• Article

table_constraint (Transact-SQL)

Spécifie les propriétés d'une contrainte PRIMARY KEY, UNIQUE, FOREIGN KEY ou CHECK, ou encore une définition DEFAULT ajoutée à une table par le biais de l'instruction ALTER TABLE.

Icône Lien de rubriqueConventions de syntaxe Transact-SQL

Syntaxe

[ CONSTRAINT constraint_name ] 
{ 
    { PRIMARY KEY | UNIQUE } 
        [ CLUSTERED | NONCLUSTERED ] 
                (column [ ASC | DESC ] [ ,...n ] )
        [ WITH FILLFACTOR = fillfactor 
        [ WITH ( <index_option>[ , ...n ] ) ]
        [ ON { partition_scheme_name ( partition_column_name ... )
          | filegroup | "default" } ] 
    | FOREIGN KEY 
                ( column [ ,...n ] )
        REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ] 
        [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
        [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
        [ NOT FOR REPLICATION ] 
    | DEFAULT constant_expression FOR column [ WITH VALUES ] 
    | CHECK [ NOT FOR REPLICATION ] ( logical_expression )
}

Arguments

  • CONSTRAINT
    Spécifie le début d'une définition de contrainte PRIMARY KEY, UNIQUE, FOREIGN KEY ou CHECK, ou d'une définition DEFAULT.

  • constraint_name
    Nom de la contrainte. Les noms de contraintes doivent suivre les règles des identificateurs, excepté le fait que le nom ne peut pas commencer par un signe dièse (#). Si constraint_name n'est pas spécifié, un nom généré par le système est affecté à la contrainte.

  • PRIMARY KEY
    Contrainte qui applique l'intégrité d'entité d'une colonne ou de plusieurs colonnes spécifiées à l'aide d'un index unique. Une seule contrainte PRIMARY KEY peut être créée par table.

  • UNIQUE
    Contrainte qui assure l'intégrité d'entité d'une colonne ou de plusieurs colonnes données au moyen d'un index unique.

  • CLUSTERED et NONCLUSTERED
    Spécifie qu'un index, cluster ou non cluster, est créé pour la contrainte PRIMARY KEY ou UNIQUE. La valeur par défaut des contraintes PRIMARY KEY est CLUSTERED. La valeur par défaut des contraintes UNIQUE est, elle, NONCLUSTERED.

    Si une contrainte ou un index cluster existe déjà pour la table, vous ne pouvez pas spécifier CLUSTERED. Dans ce cas, l'option par défaut d'une contrainte PRIMARY KEY devient NONCLUSTERED.

    Les colonnes de types de données ntext, text, varchar(max), nvarchar(max), varbinary(max), xml ou image ne peuvent pas être spécifiées en tant que colonnes d'un index.

  • column
    Colonne ou liste de colonnes spécifiées entre parenthèses, utilisée dans une nouvelle contrainte.

  • [ ASC | DESC ]
    Indique l'ordre de tri de la ou des colonnes impliquées dans les contraintes de table. La valeur par défaut est ASC.

  • WITH FILLFACTOR **=**fillfactor
    Spécifie le remplissage par le moteur de base de données des pages d'index utilisées pour stocker les données d'index. Les valeurs fillfactor spécifiées par l'utilisateur doivent être comprises entre 1 et 100. Si aucune valeur n'est spécifiée, la valeur par défaut est 0.

    Important

    Dans la documentation, l'indication que WITH FILLFACTOR = fillfactor constitue l'unique option d'indexation s'appliquant aux contraintes PRIMARY KEY ou UNIQUE est maintenue dans un but de compatibilité avec les versions précédentes, mais cela ne sera plus indiqué ainsi dans les versions à venir. D'autres options d'indexation peuvent être précisées dans la clause index_option de ALTER TABLE.

  • ON { partition_scheme_name**(partition_column_name)** | filegroup| "default" }
    Spécifie le lieu de stockage de l'index créé pour la contrainte. Si partition_scheme_name est spécifié, l'index est partitionné et les partitions qui en découlent sont mappées aux groupes de fichiers spécifiés par partition_scheme_name. Si filegroup est spécifié, l'index est créé dans le groupe de fichiers nommé. Si "default" est spécifié ou si l'option ON n'est pas du tout spécifiée, l'index est créé dans le même groupe de fichiers que celui de la table. Si l'option ON est spécifiée lorsqu'un index cluster est ajouté pour une contrainte PRIMARY KEY ou UNIQUE, l'intégralité de la table est déplacée dans le groupe de fichiers spécifié lorsque l'index cluster est créé.

    Dans ce contexte, default ne constitue pas un mot clé. Il correspond plutôt à un identificateur du groupe de fichiers par défaut et doit être délimité, comme dans ON "default" ou ON [default]. Si "default" est spécifié, l'option QUOTED_IDENTIFIER doit être ON pour la session active. Il s'agit du paramètre par défaut. Pour plus d'informations, consultez SET QUOTED_IDENTIFIER (Transact-SQL).

  • FOREIGN KEY REFERENCES
    Contrainte qui assure l'intégrité référentielle des données d'une colonne. Une contrainte FOREIGN KEY exige que chaque valeur de la colonne existe dans la colonne spécifiée de la table référencée.

  • referenced_table_name
    Table référencée par la contrainte FOREIGN KEY.

  • ref_column
    Colonne ou liste de colonnes, entre parenthèses, référencée par la nouvelle contrainte FOREIGN KEY.

  • ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }
    Spécifie l'action qui se produit sur les lignes de la table modifiée lorsque ces lignes comportent une relation référentielle et que la ligne référencée est supprimée de la table parente. La valeur par défaut est NO ACTION.

    • NO ACTION
      Le moteur de base de données SQL Server génère une erreur et la suppression de la ligne dans la table parente est restaurée.

    • CASCADE
      Les lignes correspondantes sont supprimées de la table de référence si cette ligne est supprimée de la table parente.

    • SET NULL
      Toutes les valeurs composant la clé étrangère sont définies sur NULL si la ligne correspondante dans la table parente est supprimée. Pour que cette contrainte soit exécutée, les colonnes de clé étrangère doivent accepter les valeurs NULL.

    • SET DEFAULT
      Toutes les valeurs composant la clé étrangère sont définies sur leur valeur par défaut si la ligne correspondante dans la table parente est supprimée. Cette contrainte ne peut être exécutée que si toutes les colonnes de clé étrangère possèdent des définitions par défaut. Si une colonne peut avoir une valeur NULL et qu'aucune valeur par défaut explicite n'est définie, NULL devient la valeur par défaut implicite de la colonne.

    Ne spécifiez pas CASCADE si la table est incluse dans une publication de fusion qui utilise des enregistrements logiques. Pour plus d'informations sur les enregistrements logiques, consultez Regroupements des modifications apportées à des lignes connexes à l'aide d'enregistrements logiques.

    L'action ON DELETE CASCADE ne peut pas être définie si un déclencheur ON DELETE de l'option INSTEAD OF existe déjà dans la table en cours de modification.

    Par exemple, dans la base de données AdventureWorks, la table ProductVendor possède une relation référentielle avec la table Vendor. La clé étrangère ProductVendor.VendorID fait référence à la clé primaire Vendor.VendorID.

    Si une instruction DELETE est exécutée sur une ligne de la table Vendor et qu'une action ON DELETE CASCADE est spécifiée pour ProductVendor.VendorID, le moteur de base de données vérifie la présence des lignes dépendantes dans la table ProductVendor. Si elle existe, les lignes dépendantes se trouvant dans la table ProductVendor sont supprimées, ainsi que chaque ligne référencée correspondante dans la table Vendor.

    Par contre, si la valeur NO ACTION est spécifiée, le moteur de base de données génère une erreur et restaure l'action de suppression de la ligne dans la table Vendor si au moins une ligne fait référence à cette ligne dans la table ProductVendor.

  • ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }
    Spécifie l'action qui se produit sur les lignes de la table modifiée lorsque ces lignes comportent une relation référentielle et que la ligne référencée correspondante est mise à jour dans la table parente. La valeur par défaut est NO ACTION.

    • NO ACTION
      Le moteur de base de données génère une erreur et la mise à jour de la ligne dans la table parente est restaurée.

    • CASCADE
      Les lignes correspondantes sont mises à jour dans la table de référence si cette ligne est mise à jour dans la table parente.

    • SET NULL
      Toutes les valeurs composant la clé étrangère sont définies sur NULL si la ligne correspondante dans la table parente est mise à jour. Pour que cette contrainte soit exécutée, les colonnes de clé étrangère doivent accepter les valeurs NULL.

    • SET DEFAULT
      Toutes les valeurs qui composent la clé étrangère sont définies sur leur valeur par défaut si la ligne correspondante dans la table parente est mise à jour. Cette contrainte ne peut être exécutée que si toutes les colonnes de clé étrangère possèdent des définitions par défaut. Si une colonne peut avoir une valeur NULL et qu'aucune valeur par défaut explicite n'est définie, NULL devient la valeur par défaut implicite de la colonne.

    Ne spécifiez pas CASCADE si la table est incluse dans une publication de fusion qui utilise des enregistrements logiques. Pour plus d'informations sur les enregistrements logiques, consultez Regroupements des modifications apportées à des lignes connexes à l'aide d'enregistrements logiques.

    L'action ON UPDATE CASCADE ne peut pas être définie si un déclencheur ON UPDATE de l'option INSTEAD OF existe déjà dans la table en cours de modification.

    Par exemple, dans la base de données AdventureWorks, la table ProductVendor possède une relation référentielle avec la table Vendor. La clé étrangère ProductVendor.VendorID fait référence à la clé primaire Vendor.VendorID.

    Si une instruction UPDATE est exécutée sur une ligne de la table Vendor et si une action ON UPDATE CASCADE est spécifiée pour ProductVendor.VendorID, le moteur de base de données vérifie la présence des lignes dépendantes dans la table ProductVendor. Si elle existe, la ligne dépendante se trouvant dans la table ProductVendor est alors mise à jour en plus de la ligne référencée correspondante dans la table Vendor.

    Par contre, si la valeur NO ACTION est spécifiée, le moteur de base de données génère une erreur et restaure l'action de mise à jour de la ligne dans la table Vendor si au moins une ligne fait référence à cette ligne dans la table ProductVendor.

  • NOT FOR REPLICATION
    Peut être spécifié aussi bien pour les contraintes FOREIGN KEY que pour les contraintes CHECK. Si cette clause est spécifiée pour une contrainte cette dernière n'est pas appliquée lorsque les agents de réplication effectuent des opérations d'insertion de mise à jour ou de suppression. Pour plus d'informations, consultez Contrôle des contraintes, des identités et des déclencheurs avec l'option NOT FOR REPLICATION.

  • DEFAULT
    Spécifie la valeur par défaut de la colonne. Une définition DEFAULT peut servir à définir les valeurs d'une nouvelle colonne dans les lignes de données existantes. Elle ne peut pas être ajoutée à une colonne de type timestamp, à une propriété IDENTITY, à une définition DEFAULT existante ou une valeur par défaut liée. Si la colonne a déjà une valeur par défaut, celle-ci doit être supprimée avant que la nouvelle valeur par défaut puisse être ajoutée. Si une valeur par défaut est spécifiée pour une colonne de type défini par l'utilisateur, le type doit prendre en charge une conversion implicite de constant_expression en type défini par l'utilisateur. Pour maintenir la compatibilité avec les versions antérieures de SQL Server, un nom de contrainte peut être affecté à une définition DEFAULT.

  • constant_expression
    Valeur littérale, valeur NULL ou fonction système utilisée comme valeur par défaut de la colonne. Si constant_expression est utilisé avec une colonne de type défini par l'utilisateur Microsoft.NET Framework, l'implémentation de ce dernier doit prendre en charge une conversion implicite de constant_expression en type défini par l'utilisateur.

  • FOR column
    Spécifie la colonne associée à une définition DEFAULT au niveau de la table.

  • WITH VALUES
    Spécifie que la valeur donnée dans DEFAULT constant_expression est stockée dans une nouvelle colonne ajoutée aux lignes existantes. WITH VALUES peut uniquement être spécifié si l'option DEFAULT est spécifiée dans une clause ADD COLUMN. Si la colonne ajoutée accepte les valeurs Null et que l'option WITH VALUES est spécifiée, la valeur par défaut est stockée dans la nouvelle colonne ajoutée aux lignes existantes. Si WITH VALUES n'est pas spécifiée pour des colonnes acceptant les valeurs NULL, la valeur NULL est stockée dans la nouvelle colonne des lignes existantes. Si la nouvelle colonne n'accepte pas les valeurs NULL, la valeur par défaut est stockée dans les nouvelles lignes, que l'option WITH VALUES soit spécifiée ou pas.

  • CHECK
    Contrainte qui assure l'intégrité de domaine en limitant les valeurs qui pouvent être entrées dans une ou plusieurs colonnes.

  • logical_expression
    Expression logique utilisée avec une contrainte CHECK et retournant les valeurs TRUE ou FALSE. L'argument logical_expression associé à ce type de contrainte ne peut pas référencer une autre table mais il peut référencer d'autres colonnes dans la même table, pour la même ligne. L'expression ne peut pas référencer un type de données d'alias..

Notes

Lorsque vous ajoutez des contraintes, toutes les données existantes sont vérifiées afin de s'assurer qu'il n'existe aucune violation des contraintes. Si tel est le cas, l'instruction ALTER TABLE échoue et une erreur est retournée.

Lorsqu'une nouvelle contrainte PRIMARY KEY ou UNIQUE est ajoutée à une colonne existante, les données de la ou des colonnes doivent être uniques. L'instruction ALTER TABLE échoue si des valeurs dupliquées sont détectées. L'option WITH NOCHECK n'a aucun effet lorsque vous ajoutez une contrainte PRIMARY KEY ou UNIQUE.

Chaque contrainte PRIMARY KEY et UNIQUE génère un index. Quel que soit le nombre de contraintes UNIQUE et PRIMARY KEY, le nombre d'index sur la table ne peut en aucun cas dépasser 999 index non cluster et 1 index cluster.

Exemples

Pour obtenir des exemples, consultez ALTER TABLE (Transact-SQL).