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.
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.
| Table | Colonne | Nom de la contrainte |
|---|---|---|
| clients | id | pk_clients |
| fiches | numero | pk_fiches |
| details | numero fiche | pk_details |
| exemplaires | numero | pk_exemplaires |
| films | numero | pk_films |
| acteurs | artiste film | pk_acteurs |
| artistes | id | pk_artistes |
| categories | code | pk_categories |
| producteurs | id | pk_producteurs |
| genres | code | pk_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);
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ère | Colonnes référencées | Nom 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);
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.
| Table | Description | Nom 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: