Comment peut-on ajouter des contraintes de tous types à une table qui existe déjà , mais dont on a modifié la structure ou tout simplement oublié d’ajouter la contrainte en même temps que la colonne ?
Considérons le DDL des tables suivantes :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | --------------------------------- -- Nicolas SOUQUET - 25/07/2009 - --------------------------------- CREATE TABLE TbPays ( IDPays INT IDENTITY, nomPays VARCHAR(30), prefixeTelephonique VARCHAR(4), longueurNumeroTelephone TINYINT ) CREATE TABLE TbClient ( IDClient INT IDENTITY, nomClient VARCHAR(20), IDPays INT, numeroTelephoneClient VARCHAR(36) ) CREATE TABLE TbAbonnement ( IDAbonnement INT IDENTITY, referenceAbonnement CHAR(10), nomAbonnement VARCHAR(30), descriptionAbonnemement VARCHAR(255) ) CREATE TABLE TbAbonnementClient ( IDAbonnementClient INT IDENTITY, IDClient INT, IDAbonnement INT, dateAbonnementClient DATETIME ) |
Manifestement il manque toutes les contraintes :
– de valuation obligatoire (NOT NULL),
Рde cl̩ primaire,
Рde cl̩ ̩trang̬re,
– d’unicité,
Рde valeur par d̩faut
– de domaine (CHECK)
Le squelette d’ajout de contraintes est le suivant :
1 2 3 | --------------------------------- -- Nicolas SOUQUET - 25/07/2009 - --------------------------------- |
=> contraintes de valuation obligatoire
1 2 | ALTER TABLE maTable ALTER COLUMN maColonne memeType NOT NULL |
=> contraintes de clé primaire
1 2 3 | ALTER TABLE maTable ADD CONSTRAINT PK_maTable PRIMARY KEY (colonnesConstituantLaCléPrimaire) |
=> contraintes de clé étrangère
1 2 3 4 | ALTER TABLE maTable ADD CONSTRAINT FK_maTable_colonneDeCleEtrangere FOREIGN KEY(colonneDeCleEtrangere) REFERENCES tableContenantLaClePrimaireAReferencer(colonneDeClePrimaire) |
=> contraintes d’unicité
1 2 3 | ALTER TABLE maTable ADD CONSTRAINT UQ_maTable_colonnesConstituantLeTupleQuiDoitEtreUnique UNIQUE (colonnesConstituantLeTupleQuiDoitEtreUnique) |
=> contraintes de valeur par défaut :
1 2 3 | ALTER TABLE maTable ADD CONSTRAINT DF_maTable_maColonne DEFAULT (uneValeur) FOR (maColonne) |
=> contraintes de domaine (CHECK)
ALTER TABLE maTable
ADD CONSTRAINT CHK_maTable_maColonne
CHECK(maColonne [formuleDeVerification]
Définissons les dans cet ordre :
=> Contraintes de valuation obligatoire
Une clé primaire ne pouvant logiquement pas être NULL, nous devons définir toute les colonnes qui vont servir de clé primaire aux tables comme ne pouvant pas être NULL.
Nous devons également obliger la valuation des colonnes qui sont des clés naturelles, et celles qui seront clé étrangère :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 | --------------------------------- -- Nicolas SOUQUET - 25/07/2009 - --------------------------------- -- TbPays ALTER TABLE dbo.TbPays ALTER COLUMN IDPays INT NOT NULL ALTER TABLE dbo.TbPays ALTER COLUMN nomPays VARCHAR(30) NOT NULL ALTER TABLE dbo.TbPays ALTER COLUMN prefixeTelephonique VARCHAR(4) NOT NULL ALTER TABLE dbo.TbPays ALTER COLUMN longueurNumeroTelephone TINYINT NOT NULL -- TbClient ALTER TABLE dbo.TbClient ALTER COLUMN IDClient INT NOT NULL ALTER TABLE dbo.TbClient ALTER COLUMN nomClient VARCHAR(20) NOT NULL ALTER TABLE dbo.TbClient ALTER COLUMN IDPays INT NOT NULL ALTER TABLE dbo.TbClient ALTER COLUMN numeroTelephoneClient VARCHAR(36) NOT NULL -- TbAbonnement ALTER TABLE dbo.TbAbonnement ALTER COLUMN IDAbonnement INT NOT NULL ALTER TABLE dbo.TbAbonnement ALTER COLUMN referenceAbonnement NOT NULL ALTER TABLE dbo.TbAbonnement ALTER COLUMN nomAbonnement VARCHAR(30) NOT NULL -- TbAbonnementClient ALTER TABLE dbo.TbAbonnementClient ALTER COLUMN IDAbonnementClient INT NOT NULL ALTER TABLE dbo.TbAbonnementClient ALTER COLUMN IDClient INT NOT NULL ALTER TABLE dbo.TbAbonnementClient ALTER COLUMN IDAbonnement INT NOT NULL ALTER TABLE dbo.TbAbonnementClient ALTER COLUMN dateAbonnementClient DATETIME NOT NULL |
=> Contraintes de clé primaire
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | --------------------------------- -- Nicolas SOUQUET - 25/07/2009 - --------------------------------- -- TbPays ALTER TABLE dbo.TbPays ADD CONSTRAINT PK_TbPays_IDPays PRIMARY KEY (IDPays) -- TbClient ALTER TABLE dbo.TbClient ADD CONSTRAINT PK_TbClient_IDClient PRIMARY KEY(IDClient) -- TbAbonnement ALTER TABLE dbo.TbAbonnement ADD CONSTRAINT PK_TbAbonnement_IDAbonnement PRIMARY KEY (IDAbonnement) -- TbAbonnementClient ALTER TABLE dbo.TbAbonnementClient ADD CONSTRAINT PK_TbAbonnementClient_IDAbonnementClient PRIMARY KEY (IDAbonnementClient) |
=> Contraintes de clé étrangère
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | --------------------------------- -- Nicolas SOUQUET - 25/07/2009 - --------------------------------- -- TbClient ALTER TABLE dbo.TbClient ADD CONSTRAINT FK_TbClient_IDPays FOREIGN KEY (IDPays) REFERENCES dbo.TbPays(IDPays) -- TbAbonnementClient ALTER TABLE dbo.TbAbonnementClient ADD CONSTRAINT FK_TbAbonnementClient_IDClient FOREIGN KEY (IDClient) REFERENCES dbo.TbClient(IDClient), ---- CONSTRAINT FK_TbAbonnementClient_IDAbonnement FOREIGN KEY (IDAbonnement) REFERENCES dbo.TbAbonnement(IDAbonnement) |
=> Contraintes d’unicité
Elles permettent de s’assurer qu’aucune valeur ne sera pas insérée en double dans des colonnes qui ne constituent pas la clé primaire.
Bien qu’une clé primaire permette également d’assurer l’unicité, on ne peut bien sûr en définir qu’une seule par table, pour qu’il soit possible de la référencer par des clés étrangères dans d’autres tables.
Au contraire, on peut définir plusieurs contraintes d’unicité par table.
Similairement, des indexes sont créés implicitement lors de la création d’une clé primaire ou d’une contrainte d’unicité.
Mais seul l’index de clé primaire est toujours cluster : l’index sous-jacent à une contrainte d’unicité est cluster si la table n’a pas de clé primaire définie, et non-cluster à l’inverse.
Comme pour une clé primaire, il est nécessaire de mettre la colonne qui a la sélectivité la plus élevée en premier dans la définition de la contrainte d’unicité, car cet ordre sert dans la définition de l’index.
Comme les recherches à travers les indexes se font suivant la valeur de la première clé, l’index sera d’autant plus efficace que la valeur de sa première clé sera sélective.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | --------------------------------- -- Nicolas SOUQUET - 25/07/2009 - --------------------------------- -- TbClient ALTER TABLE dbo.TbClient ADD CONSTRAINT UQ_TbClient_nomClient UNIQUE (nomClient), CONSTRAINT UQ_TbClient_numeroTelephoneClient UNIQUE (numeroTelephoneClient) -- TbAbonnement ALTER TABLE dbo.TbAbonnement ADD CONSTRAINT UQ_TbAbonnement_nomAbonnement UNIQUE (nomAbonnement) ALTER TABLE dbo.TbAbonnement ADD CONSTRAINT UQ_TbAbonnement_referenceAbonnement UNIQUE (referenceAbonnement) -- TbAbonnementClient ALTER TABLE dbo.TbAbonnementClient ADD CONSTRAINT UQ_TbAbonnementClient_IDClient_IDAbonnement UNIQUE(IDClient, IDAbonnement) |
=> Contraintes de valeur par défaut :
Il peut être intéressant de définir une valeur par défaut pour l’insertion (mais pas à l’UPDATE) d’une nouvelle ligne à une table si la colonne est omise dans la spécification de l’instruction INSERT :
1 2 3 4 5 6 | --------------------------------- -- Nicolas SOUQUET - 25/07/2009 - --------------------------------- ALTER TABLE dbo.TbAbonnementClient ADD CONSTRAINT DF_TbAbonnementClient_dateAbonnementClient DEFAULT (GETDATE()) FOR dateAbonnementClient |
Ainsi si nous écrivons :
1 2 3 4 5 6 7 8 9 10 11 12 13 | --------------------------------- -- Nicolas SOUQUET - 25/07/2009 - --------------------------------- INSERT INTO dbo.TbAbonnementClient ( IDClient, IDAbonnement ) VALUES ( @IDClient, @IDAbonnement ) |
la colonne dateAbonnementClient sera valuée à la date actuelle du système.
=> Contraintes de domaine
Elles permettent de limiter ou de vérifier les valeurs que peuvent contenir une colonne.
Elles nécessitent une formule de vérification, qui peut être spécifiée directement lors de la définition de la contrainte, ou bien à travers une fonction définie par l’utilisateur.
L’utilisation d’une fonction pour la définition d’une contrainte de domaine permet bien souvent de ne pas avoir recours à un déclencheur pour vérifier la validité des données.
Ici, nous souhaitons vérifier que les numéros de téléphone que nous stockons dans la base de données contiennent le préfixe du pays du client, et que la longueur des numéros de téléphone est correcte.
Pour cela nous créons la fonction suivante :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | --------------------------------- -- Nicolas SOUQUET - 25/07/2009 - --------------------------------- CREATE FUNCTION Fn_IsNumTelClient_Correct ( @IDPays INT, @numeroTelephoneClient VARCHAR(36) ) RETURNS BIT WITH SCHEMABINDING AS BEGIN RETURN ( SELECT CASE WHEN @numeroTelephoneClient LIKE prefixeTelephonique + '%' AND LEN(@numeroTelephoneClient) = longueurNumeroTelephone THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END FROM dbo.TbPays WHERE IDPays = @IDPays ) END |
La contrainte de domaine sera alors définie comme suit :
1 2 3 4 5 6 | --------------------------------- -- Nicolas SOUQUET - 25/07/2009 - --------------------------------- ALTER TABLE dbo.TbAbonnementClient ADD CONSTRAINT CHK_TbAbonnementClient_numeroTelephoneClient CHECK(dbo.Fn_IsNumTelClient_Correct(IDPays, numeroTelephoneClient) = 1) |
Nous souhaitons également vérifier que les valeurs de la colonne referenceAbonnement de la table TbAbonnement aient le format XXX-nnn-nn.
Là nous pouvons définir celui-ci directement dans la spécification de la contrainte :
1 2 3 4 5 6 | --------------------------------- -- Nicolas SOUQUET - 25/07/2009 - --------------------------------- ALTER TABLE dbo.TbAbonnement ADD CONSTRAINT CHK_TbAbonnement_referenceAbonnement CHECK(referenceAbonnement LIKE '[A-Z][A-Z][A-Z]-[0-9][0-9][0-9]-[0-9][0-9]') |
ElSuket