APSQL

Définir les contraintes d’intégrités

Maintenant que les tables sont créées, il est possible de définir les contraintes d’intégrités. Pour réaliser cette opération dans de bonne condition, il est nécessaire de définir l’ensemble des contraintes de clé primaire (PK) dans un premier temps, puis les contraintes de clé étagère(FK). Les contraintes d’unicité (UN) et de validation (CK) sont créées en dernier.

Contraintes de clé primaire

Chaque table possède une clé primaire. Cette clé peut être composée d’une ou plusieurs colonnes.

SQL Server utilise la structure d’index unique pour mettre en place cette contrainte. L’accès aux données à partir de la clé primaire est ainsi plus rapide. Par défaut c’est un index organisé (CLUSTERED) qui est défini. Les données sont donc physiquement organisées par rapport à la clé primaire.

Le tableau ci-dessous permet d’identifier pour chaque table les colonnes qui participent à la clé primaire ainsi que le nom de cette contrainte.

TableColonneNom de la contrainte
clients id pk_clients
fiches numero pk_fiches
details numero
fiche
pk_details
exemplaires numeropk_exemplaires
films numeropk_films
acteurs artiste
film
pk_acteurs
artistes idpk_artistes
categories codepk_categories
producteurs idpk_producteurs
genres codepk_genres

Les clés primaires sont mises en place avec le script suivant :

USE Video;
go
ALTER TABLE Magasin.Clients
  ADD CONSTRAINT pk_clients PRIMARY KEY(numero);
ALTER TABLE Magasin.Fiches
  ADD CONSTRAINT pk_fiches PRIMARY KEY (numero);
ALTER TABLE Magasin.Details
  ADD CONSTRAINT pk_details PRIMARY KEY (numero, fiche);
ALTER TABLE Magasin.Exemplaires
  ADD CONSTRAINT pk_exemplaires PRIMARY KEY (numero);
ALTER TABLE Films 
  ADD CONSTRAINT pk_films PRIMARY KEY (numero);
ALTER TABLE Acteurs
  ADD CONSTRAINT pk_acteurs PRIMARY KEY (artiste, film);
ALTER TABLE Artistes
  ADD CONSTRAINT pk_artistes PRIMARY KEY (numero);
ALTER TABLE Categories 
  ADD CONSTRAINT pk_categories PRIMARY KEY (code);
ALTER TABLE Producteurs
  ADD CONSTRAINT pk_producteurs PRIMARY KEY (numero);
ALTER TABLE Spectateurs 
  ADD CONSTRAINT pk_spectateurs PRIMARY KEY(numero);
			

Contraintes de clés étrangères

Le tableau suivant présente les différentes contraintes de références qui existent dans le schéma étudié.

Colonnes de la clé étrangèreColonnes référencéesNom de la contrainte
films.categories categories.code fk_films_categories
films.realisateur artistes.numero fk_films_realisateurs
films.producteur producteurs.numero fk_films_producteurs
films.spectateur spectateurs.numero fk_films_spectateurs
acteurs.artiste artistes.numero fk_ateurs_artistes
acteurs.film films.numero fk_ateurs_films
magasin.exemplaire.film films.numero fk_exemplaires_films
magasin.details.exemplaire magasin.exemplaires.numero fk_details_exemplaires
magasin.details.fiche magasin.fiches.numero fk_details_fiches
magasin.fiches.client magasin.clients.numero fk_fiches_clients

Le script suivant permet de définir les différentes contraintes de références dont les caractéristiques sont exposées ci-dessus.

use Video;
go
ALTER TABLE	Films 
  ADD CONSTRAINT fk_films_categories
  FOREIGN KEY(categorie) REFERENCES Categories(code);
ALTER TABLE	Films 
  ADD CONSTRAINT fk_films_realisateur
  FOREIGN KEY(realisateur) REFERENCES Artistes(numero);  
ALTER TABLE	Films 
  ADD CONSTRAINT fk_films_producteurs
  FOREIGN KEY(producteur) REFERENCES Producteurs(numero);
ALTER TABLE	Films 
  ADD CONSTRAINT fk_films_spectateurs
  FOREIGN KEY(spectateur) REFERENCES Spectateurs(numero);
ALTER TABLE	Acteurs
  ADD CONSTRAINT fk_acteurs_artistes
  FOREIGN KEY(artiste) REFERENCES Artistes(numero);
ALTER TABLE	Acteurs
  ADD CONSTRAINT fk_acteurs_films
  FOREIGN KEY(film) REFERENCES Films(numero);
ALTER TABLE	Magasin.Exemplaires
  ADD CONSTRAINT fk_exemplaires_films
  FOREIGN KEY(film) REFERENCES Films(numero);
ALTER TABLE	Magasin.Details
  ADD CONSTRAINT fk_details_exemplaires
  FOREIGN KEY(exemplaire) REFERENCES Magasin.Exemplaires(numero);
ALTER TABLE	Magasin.Details
  ADD CONSTRAINT fk_details_fiches
  FOREIGN KEY(fiche) REFERENCES Magasin.Fiches(numero);
ALTER TABLE	Magasin.Fiches
  ADD CONSTRAINT fk_fiches_clients
  FOREIGN KEY(client) REFERENCES Magasin.Clients(numero);
			

Contraintes de validation

Les contraintes de validations (CHECK) vont permettre de définir un certain nombre de règles simples de gestion des données. Il est important de définir ces règles au plus proche des données. En travaillant ainsi, il n’est pas possible d’outrepasser ces règles et donc les données sont toujours cohérentes. Le tableau ci-dessous présente les différentes contraintes de validation à définir.

TableDescriptionNom de la contrainte
films disponibleLocation={0,1} ck_films_disponibleLocation
films prixLocationJour>=0 ck_films_prixLocationJour
films anneeProduction>=1900 ck_films_anneeProduction
fiches montantPaye contient null ou bien une valeur >=0 ck_fiches_montantPaye

Remarque: Certaines règles ne peuvent pas être définies sous forme de contrainte de validation. Un déclencheur de base de données sera donc défini ultérieurement.

Le script suivant permet d’ajouter ces différentes contraintes :

USE Video;
go
ALTER TABLE Films
  ADD CONSTRAINT ck_films_disponibleLocation 
      CHECK (disponibleLocation IN (0,1));
ALTER TABLE Films
  ADD CONSTRAINT ck_films_prixLocationJour 
      CHECK (prixLocationJour>=0);
ALTER TABLE Films
  ADD CONSTRAINT ck_films_anneeProduction 
      CHECK (anneeProduction>=1900);
ALTER TABLE Magasin.Fiches
  ADD CONSTRAINT ck_fiches_montantPaye 
      CHECK ((montantPaye is null) OR (montantPaye>=0));
			

La manipulations depuis SQL Server Management Studio:


Video: Définir les contraintes d'intégrités