CREATE TABLE (Transact-SQL)
Crée une nouvelle table dans SQL Server 2008 R2.
.gif "Icône Lien de rubrique")
Syntaxe
CREATE TABLE
[ database_name . [ schema_name ] . | schema_name . ] table_name
( { <column_definition> | <computed_column_definition>
| <column_set_definition> | [ <table_constraint> ] [ ,...n ] } )
[ ON { partition_scheme_name ( partition_column_name ) | filegroup
| "default" } ]
[ { TEXTIMAGE_ON { filegroup | "default" } ]
[ FILESTREAM_ON { partition_scheme_name | filegroup
| "default" } ]
[ WITH ( <table_option> [ ,...n ] ) ]
[ ; ]
<column_definition> ::=
column_name <data_type>
[ FILESTREAM ]
[ COLLATE collation_name ]
[ NULL | NOT NULL ]
[
[ CONSTRAINT constraint_name ] DEFAULT constant_expression ]
| [ IDENTITY [ ( seed ,increment ) ] [ NOT FOR REPLICATION ]
]
[ ROWGUIDCOL ] [ <column_constraint> [ ...n ] ]
[ SPARSE ]
<data type> ::=
[ type_schema_name . ] type_name
[ ( precision [ , scale ] | max |
[ { CONTENT | DOCUMENT } ] xml_schema_collection ) ]
<column_constraint> ::=
[ CONSTRAINT constraint_name ]
{ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[
WITH FILLFACTOR = fillfactor
| WITH ( < index_option > [ , ...n ] )
]
[ ON { partition_scheme_name ( partition_column_name )
| filegroup | "default" } ]
| [ FOREIGN KEY ]
REFERENCES [ schema_name . ] referenced_table_name [ ( ref_column ) ]
[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[ NOT FOR REPLICATION ]
| CHECK [ NOT FOR REPLICATION ] ( logical_expression )
}
<computed_column_definition> ::=
column_name AS computed_column_expression
[ PERSISTED [ NOT NULL ] ]
[
[ CONSTRAINT constraint_name ]
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[
WITH FILLFACTOR = fillfactor
| WITH ( <index_option> [ , ...n ] )
]
| [ FOREIGN KEY ]
REFERENCES referenced_table_name [ ( ref_column ) ]
[ ON DELETE { NO ACTION | CASCADE } ]
[ ON UPDATE { NO ACTION } ]
[ NOT FOR REPLICATION ]
| CHECK [ NOT FOR REPLICATION ] ( logical_expression )
[ ON { partition_scheme_name ( partition_column_name )
| filegroup | "default" } ]
]
<column_set_definition> ::=
column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
< table_constraint > ::=
[ 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 ]
| CHECK [ NOT FOR REPLICATION ] ( logical_expression )
}
<table_option> ::=
{
DATA_COMPRESSION = { NONE | ROW | PAGE }
[ ON PARTITIONS ( { <partition_number_expression> | <range> }
[ , ...n ] ) ]
}
<index_option> ::=
{
PAD_INDEX = { ON | OFF }
| FILLFACTOR = fillfactor
| IGNORE_DUP_KEY = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
| ALLOW_ROW_LOCKS = { ON | OFF}
| ALLOW_PAGE_LOCKS ={ ON | OFF}
| DATA_COMPRESSION = { NONE | ROW | PAGE }
[ ON PARTITIONS ( { <partition_number_expression> | <range> }
[ , ...n ] ) ]
}
<range> ::=
<partition_number_expression> TO <partition_number_expression>
Arguments
database_name
Nom de la base de données dans laquelle la table est créée. L'argument database_name doit correspondre au nom d'une base de données existante. Si aucun nom n'est spécifié, la valeur par défaut de database_name est la base de données active. Le nom d'accès de la connexion actuelle doit être associé à un ID d'utilisateur existant dans la base de données spécifiée par database_name, et cet ID d'utilisateur doit disposer des autorisations CREATE TABLE.schema_name
Nom du schéma auquel appartient la nouvelle table.table_name
Nom de la nouvelle table. Les noms de tables doivent se conformer aux règles en vigueur pour les identificateurs. table_name peut comprendre un maximum de 128 caractères, à l'exception des noms de tables temporaires locales (noms précédés du signe #) qui ne peuvent pas dépasser 116 caractères.column_name
Nom d'une colonne de la table. Les noms de colonnes doivent respecter les règles gouvernant les identificateurs et doivent être uniques dans la table. column_name peut comporter jusqu'à 128 caractères. column_name peut être omis pour les colonnes créées avec un type de données timestamp. Si column_name n'est pas spécifié, le nom par défaut d'une colonne timestamp est timestamp.computed_column_expression
Expression définissant la valeur d'une colonne calculée. Une colonne calculée est une colonne virtuelle qui n'est pas stockée physiquement dans la table, à moins que la colonne ne soit indiquée comme PERSISTED. La colonne est calculée à partir d'une expression qui utilise d'autres colonnes dans la même table. Par exemple, une colonne calculée peut avoir la définition : cost AS price * qty. L'expression peut être un nom de colonne non calculée, une constante, une fonction, une variable et toute combinaison de ces éléments reliés par un ou plusieurs opérateurs. L'expression ne peut pas être une sous-requête ou contenir des types de données d'alias.Les colonnes calculées peuvent être utilisées dans des listes de sélection, des clauses WHERE, des clauses ORDER BY ou à tout autre emplacement où il est possible d'utiliser des expressions régulières, aux exceptions suivantes près :
Une colonne calculée ne peut pas être utilisée en tant que définition de contrainte DEFAULT ou FOREIGN KEY ou avec une définition de contrainte NOT NULL. Toutefois, elle peut être utilisée en tant que colonne clé dans un index ou que composante d'une contrainte PRIMARY KEY ou UNIQUE quelconque, si sa valeur est définie par une expression déterministe et si le type de données du résultat est autorisé dans les colonnes d'index.
Par exemple, si la table possède les colonnes de type entier a et b, la colonne calculée a+b peut être indexée, contrairement à la colonne calculée a+DATEPART(dd, GETDATE()) dont la valeur est susceptible d'évoluer au fil des appels.
Une colonne calculée ne peut pas être la cible d'une instruction INSERT ou UPDATE.
Notes
Chaque ligne dans une table peut avoir des valeurs différentes pour les colonnes impliquées dans une colonne calculée ; par conséquent, il est possible que la colonne calculée n'ait pas la même valeur pour chaque ligne.
En fonction des expressions utilisées, la possibilité de valeurs NULL dans les colonnes calculées est déterminée automatiquement par le Moteur de base de données. Le résultat de la plupart des expressions est considéré comme pouvant avoir la valeur Null, même si seules des colonnes n'acceptant pas cette valeur sont présentes, car des dépassements négatifs ou positifs possibles produisent également des résultats Null. Utilisez la fonction COLUMNPROPERTY avec la propriété AllowsNull pour examiner la possibilité de valeur NULL pour chaque colonne calculée dans une table. Une expression pouvant prendre la valeur Null peut être transformée en expression ne pouvant pas prendre cette valeur, lorsque ISNULL est spécifié avec la constante check_expression, où la constante est une valeur non nulle substituée à n'importe quel résultat NULL. L'autorisation REFERENCES sur le type est nécessaire pour les colonnes calculées basées sur des expressions de type CLR (Common Language Runtime) défini par l'utilisateur.
PERSISTED
Spécifie que le moteur de Moteur de base de données SQL Server stockera physiquement les valeurs calculées dans la table et mettra à jour les valeurs lorsque n'importe quelle colonne dont dépend la colonne calculée est mise à jour. Notamment, une colonne calculée en tant que PERSISTED vous permet de créer un index sur une colonne calculée qui est déterministe, mais pas précise. Pour plus d'informations, consultez Création d'index sur des colonnes calculées. Toutes les colonnes calculées utilisées en tant que colonnes de partitionnement d'une table partitionnée doivent être marquées explicitement comme PERSISTED. computed_column_expression doit être déterministe lorsque PERSISTED est spécifié.ON { <schéma_partition> | filegroup | "default" }
Spécifie le schéma de partition ou groupe de fichiers dans lequel la table est stockée. Si <schéma_partition> est spécifié, la table sera partitionnée avec des partitions stockées dans un ensemble d'un ou de plusieurs groupes de fichiers spécifié dans <schéma_partition>. Si vous spécifiez filegroup, la table est stockée dans le groupe de fichiers nommé. Le groupe de fichiers doit exister dans la base de données. Si "default" est spécifié, ou si ON n'est pas spécifié du tout, la table est stockée dans le groupe de fichiers par défaut. Le mécanisme de stockage d'une table tel que spécifié dans CREATE TABLE ne peut plus être modifié ultérieurement.ON {<schéma_partition> | filegroup | "default"} peut également être spécifié dans une contrainte PRIMARY KEY ou UNIQUE. Ces contraintes créent des index. Si vous spécifiez filegroup, l'index est stocké dans le groupe de fichiers nommé. Si "default" est spécifié, ou si ON n'est pas spécifié du tout, l'index est stocké dans le même groupe de fichiers que la table. Si la contrainte PRIMARY KEY ou UNIQUE crée un index cluster, les pages de données de la table sont stockées dans le même groupe de fichiers que l'index. Si CLUSTERED est spécifié ou la contrainte crée un index cluster d'une autre manière et si une valeur <schéma_partition> est spécifiée qui diffère des valeurs <schéma_partition> ou filegroup de la définition de table, ou vice versa, seule la définition de la contrainte sera honorée et l'autre sera ignorée.
Notes
L'élément « default » n'est pas un mot clé dans ce contexte. C'est un identificateur du groupe de fichiers par défaut et il 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).
Notes
Après avoir créé une table partitionnée, pensez à affecter à l'option LOCK_ESCALATION de la table la valeur AUTO. Cela peut améliorer la concurrence en permettant l'escalade des verrous au niveau de la partition (HoBT) plutôt que de la table. Pour plus d'informations, consultez ALTER TABLE (Transact-SQL).
TEXTIMAGE_ON { filegroup| "default" }
Mots clés indiquant que les colonnes text, ntext, image, xml, varchar(max), nvarchar(max), varbinary(max) et de type CLR (Common Language Runtime) (y compris geometry et geography) défini par l'utilisateur sont stockées dans le groupe de fichiers spécifié.TEXTIMAGE_ON n'est pas autorisé s'il n'y a pas de colonne de valeur élevée dans la table. TEXTIMAGE_ON ne peut pas être spécifié si <schéma_partition> est spécifié. Si "default" est spécifié, ou si TEXTIMAGE_ON n'est pas spécifié du tout, les colonnes de valeur élevée sont stockées dans le groupe de fichiers par défaut. Le stockage de données de colonnes de valeur élevée tel que spécifié dans CREATE TABLE ne peut plus être modifié ultérieurement.
Notes
L'élément « default » n'est pas un mot clé dans ce contexte. Il représente l'identificateur du groupe de fichiers par défaut et doit être délimité, par exemple de la manière suivante : TEXTIMAGE_ON "default" ou TEXTIMAGE_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).
FILESTREAM_ON { partition_scheme_name | filegroup | "default" }
Spécifie le groupe de fichiers pour les données FILESTREAM.Si la table contient des données FILESTREAM et si elle est partitionnée, la clause FILESTREAM_ON doit être incluse et doit spécifier un schéma de partition de groupes de fichiers FILESTREAM. Ce schéma de partition doit utiliser la même fonction de partition et les mêmes colonnes de partition que le schéma de partition de la table, faute de quoi une erreur est générée.
Si la table n'est pas partitionnée, la colonne FILESTREAM ne peut pas être partitionnée. Les données FILESTREAM de la table doivent être stockées dans un groupe de fichiers unique. Ce groupe de fichiers est spécifié dans la clause FILESTREAM_ON.
Si la table n'est pas partitionnée et si la clause FILESTREAM_ON n'est pas spécifiée, c'est le groupe de fichiers FILESTREAM dont la propriété DEFAULT est définie qui est utilisé. S'il n'y a aucun groupe de fichiers FILESTREAM, une erreur est générée.
Comme avec ON et TEXTIMAGE_ON, la valeur définie à l'aide de CREATE TABLE pour FILESTREAM_ON ne peut pas être modifiée, sauf dans les cas suivants :
Une instruction CREATE INDEX convertit un segment de mémoire en index cluster. Dans ce cas, il est possible de spécifier un autre groupe de fichiers FILESTREAM, un autre schéma de partition ou la valeur NULL.
Une instruction DROP INDEX convertit un index cluster en segment de mémoire. Dans ce cas, il est possible de spécifier un autre groupe de fichiers FILESTREAM, un autre schéma de partition ou la valeur "default".
Le groupe de fichiers de la clause FILESTREAM_ON <groupe_fichiers> ou chaque groupe de fichiers FILESTREAM nommé dans le schéma de partition doit avoir un fichier défini pour le groupe de fichiers. Ce fichier doit être défini à l'aide d'une instruction CREATE DATABASE ou ALTER DATABASE, faute de quoi une erreur est générée.
Pour obtenir les rubriques FILESTREAM connexes, consultez Conception et implémentation du stockage FILESTREAM.
[ type_schema_name**.** ] type_name
Précise le type de données de la colonne et le schéma auquel il appartient. Le type de données peut être un des suivants :Type de données système.
Type d'alias basé sur un type de données système SQL Server. Les types de données d'alias sont créés par l'instruction CREATE TYPE avant de pouvoir être utilisés dans une définition de table. L'affectation NULL ou NOT NULL pour un type de données d'alias peut être ignorée au cours de l'exécution de l'instruction CREATE TABLE. Cependant, une spécification de longueur ne peut pas être modifiée ; la longueur d'un type de données d'alias ne peut pas être spécifiée dans une instruction CREATE TABLE.
Type CLR défini par l'utilisateur. Les types de données CLR définis par l'utilisateur sont créés avec l'instruction CREATE TYPE avant de pouvoir être utilisés dans une définition de table. Pour créer une colonne sur un type de données CLR défini par l'utilisateur, une autorisation REFERENCES est nécessaire pour le type.
Si type_schema_name n'est pas spécifié, le Moteur de base de données SQL Server pointe sur type_name dans l'ordre suivant :
Le type de données système SQL Server
Le schéma par défaut de l'utilisateur actuel dans la base de données active
Le schéma dbo dans la base de données active
precision
Précision du type de données spécifié. Pour plus d'informations sur les valeurs de précision valides, consultez Précision, échelle et longueur.scale
Échelle du type de données spécifié. Pour plus d'informations sur les valeurs d'échelle valides, consultez Précision, échelle et longueur.max
S'applique uniquement aux types de données varchar, nvarchar et varbinary pour le stockage de 2^31 octets de données caractères et binaires et 2^30 octets de données Unicode.CONTENT
Spécifie que chaque instance du type de données xml dans column_name peut contenir plusieurs éléments de niveau supérieur. CONTENT ne s'applique qu'au type de données xml et ne peut être spécifié que si xml_schema_collection est également spécifié. En l'absence de toute spécification, CONTENT est le comportement par défaut.DOCUMENT
Spécifie que chaque instance du type de données xml dans column_name ne peut contenir qu'un seul élément de niveau supérieur. DOCUMENT ne s'applique qu'au type de données xml et ne peut être spécifié que si xml_schema_collection est également spécifié.xml_schema_collection
S'applique uniquement au type de données xml pour l'association d'une collection de schémas XML au type. Avant d'inclure une colonne xml dans un schéma, vous devez d'abord créer celui-ci dans la base de données à l'aide de CREATE XML SCHEMA COLLECTION.DEFAULT
Spécifie la valeur fournie pour la colonne lorsque vous n'avez pas spécifié explicitement de valeur lors d'une insertion. Les définitions DEFAULT peuvent être appliquées à n'importe quelle colonne, excepté celles définies en tant que timestamp ou celles dotées de la propriété IDENTITY. 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 au type défini par l'utilisateur. Les définitions DEFAULT sont supprimées lorsque la table est supprimée. Seule une valeur constante, telle qu'une chaîne de caractères, une fonction scalaire (fonction système, définie par l'utilisateur ou CLR) ou la valeur NULL peut être utilisée comme valeur par défaut. 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
Constante, valeur NULL ou fonction système utilisée comme valeur par défaut pour la colonne.IDENTITY
Indique que la nouvelle colonne est une colonne d'identité. Lorsqu'une ligne est ajoutée à la table, le Moteur de base de données affecte une valeur incrémentée unique à la colonne. Les colonnes d'identité sont normalement utilisées avec les contraintes PRIMARY KEY comme identificateur de ligne unique pour la table. La propriété IDENTITY peut être affectée aux colonnes tinyint, smallint, int, bigint, decimal(p,0) ou numeric(p,0). Une seule colonne d'identité peut être créée par table. Il n'est pas possible d'utiliser des valeurs par défaut liées et des contraintes DEFAULT avec une colonne d'identité. Vous devez spécifier à la fois la valeur initiale et l'incrément ou aucune de ces valeurs. Si vous n'en spécifiez aucune, la valeur par défaut est (1,1).seed
Valeur utilisée pour la toute première ligne chargée dans la table.increment
Valeur d'incrément ajoutée à la valeur d'identité de la ligne précédemment chargée.NOT FOR REPLICATION
Dans l'instruction CREATE TABLE, la clause NOT FOR REPLICATION peut être spécifiée pour la propriété IDENTITY, les contraintes FOREIGN KEY et CHECK. Si la clause est spécifiée pour la propriété IDENTITY, les valeurs ne sont pas incrémentées dans les colonnes d'identité lorsque les agents de réplication effectuent des insertions. Si cette clause est spécifiée pour une contrainte, la contrainte 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.ROWGUIDCOL
Indique que la nouvelle colonne est une colonne d'identité ROWGUID. Une seule colonne uniqueidentifier par table peut servir de colonne ROWGUIDCOL. L'application de la propriété ROWGUIDCOL permet à la colonne d'être référencée à l'aide de $ROWGUID. La propriété ROWGUIDCOL ne peut être affectée qu'à une colonne uniqueidentifier. Le mot clé ROWGUIDCOL n'est pas valide si le niveau de compatibilité de la base de données est 65 ou inférieur. Pour plus d'informations, consultez Niveau de compatibilité ALTER DATABASE (Transact-SQL). Les colonnes avec un type de données défini par l'utilisateur ne peuvent pas être conçues avec ROWGUIDCOL.La propriété ROWGUIDCOL n'assure pas l'unicité des valeurs stockées dans la colonne. ROWGUIDCOL ne peut pas non plus générer automatiquement des valeurs pour les nouvelles lignes insérées dans la table. Pour générer des valeurs uniques pour chaque colonne, vous pouvez soit utiliser la fonction NEWID ou NEWSEQUENTIALID sur des instructions INSERT, soit utiliser ces fonctions comme fonctions par défaut pour la colonne.
SPARSE
Indique que la nouvelle colonne est une colonne fragmentée. Le stockage des colonnes fragmentées est optimisé pour les valeurs Null. Les colonnes fragmentées ne peuvent pas être désignées comme NOT NULL. Pour connaître les restrictions supplémentaires et obtenir plus d'informations sur les colonnes fragmentées, consultez Utilisation de colonnes fragmentées.FILESTREAM
Valide uniquement pour les colonnes varbinary(max). Spécifie le stockage FILESTREAM pour les données BLOB varbinary(max).La table doit également comporter une colonne du type de données uniqueidentifier ayant l'attribut ROWGUIDCOL. Cette colonne ne doit pas autoriser les valeurs Null et doit avoir une contrainte de colonne unique de type UNIQUE ou PRIMARY KEY. La valeur GUID de la colonne doit être fournie par une application lors de l'insertion de données ou par une contrainte DEFAULT qui utilise la fonction NEWID ().
La colonne ROWGUIDCOL ne peut pas être supprimée et les contraintes liées ne peuvent pas être modifiées tant qu'une colonne FILESTREAM est définie pour la table. La colonne ROWGUIDCOL peut être supprimée uniquement lorsque la dernière colonne FILESTREAM a été supprimée.
Lorsque l'attribut de stockage FILESTREAM est spécifié pour une colonne, toutes les valeurs de cette colonne sont stockées dans un conteneur de données FILESTREAM sur le système de fichiers.
COLLATE collation_name
Indique le classement de la colonne. Le nom du classement peut être un nom de classement Windows ou un nom de classement SQL. collation_name n'est applicable qu'aux colonnes avec le type de données char, varchar, text, nchar, nvarchar et ntext. Si cette valeur n'est pas spécifiée, la colonne reçoit le classement du type de données utilisateur, si son type de données est un type de données utilisateur, ou le classement par défaut de la base de données.Pour plus d'informations sur les noms de classements Windows et SQL, consultez Nom de classement Windows et Nom de classement SQL.
Pour plus d'informations sur la clause COLLATE, consultez COLLATE (Transact-SQL).
CONSTRAINT
Mot clé facultatif qui indique le début de la définition d'une contrainte PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY ou CHECK. Pour plus d'informations, consultez Contraintes.constraint_name
Nom de la contrainte. Les noms de contraintes doivent être uniques au sein du schéma auquel appartient la table.NULL et NOT NULL
Détermine si les valeurs Null sont autorisées dans la colonne. NULL n'est pas strictement une contrainte, mais peut être spécifié de la même manière que NOT NULL. Il est possible de spécifier NOT NULL pour des colonnes calculées seulement si PERSISTED est également spécifié.PRIMARY KEY
Contrainte assurant l'intégrité d'entité d'une ou de plusieurs colonnes spécifiées au moyen 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 ou de plusieurs colonnes spécifiées au moyen d'un index unique. Une table peut comprendre plusieurs contraintes UNIQUE.CLUSTERED et NONCLUSTERED
Indique la création d'un index cluster ou non cluster pour la contrainte PRIMARY KEY ou UNIQUE. Les contraintes PRIMARY KEY ont la valeur par défaut CLUSTERED et les contraintes UNIQUE la valeur par défaut NONCLUSTERED.CLUSTERED peut être spécifié pour une seule contrainte dans une instruction CREATE TABLE. Si CLUSTERED est spécifié pour une contrainte UNIQUE et une contrainte PRIMARY KEY est également spécifiée, la contrainte PRIMARY KEY a la valeur par défaut NONCLUSTERED.
FOREIGN KEY REFERENCES
Contrainte qui assure l'intégrité référentielle des données des colonnes. Avec les contraintes FOREIGN KEY, il faut que chaque valeur de la colonne existe dans la ou les colonnes référencées correspondantes de la table référencée. Les contraintes FOREIGN KEY ne peuvent référencer que des colonnes qui sont des contraintes PRIMARY KEY ou UNIQUE dans la table référencée ou des colonnes référencées dans un UNIQUE INDEX sur la table référencée. Les clés étrangères sur les colonnes calculées doivent également être marquées comme PERSISTED.[ schema_name**.**] referenced_table_name]
Nom de la table référencée par la contrainte FOREIGN KEY, et le schéma à laquelle elle appartient.**(**ref_column [ ,... n ] )
Colonne, ou liste de colonnes, provenant de la table référencée par la contrainte FOREIGN KEY.ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }
Spécifie l'action qui se produit dans les lignes de la table créée, si ces lignes comportent une relation référentielle et si 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 déclenche 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 pour celles supprimées de la table parent.SET NULL
Toutes les valeurs qui composent la clé étrangère sont NULL si la ligne correspondante dans la table parente est supprimée. Cette contrainte ne peut être exécutée que si les colonnes de la clé étrangère acceptent les valeurs NULL.SET DEFAULT
Toutes les valeurs qui composent la clé étrangère sont celles 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 la clé étrangère possèdent des définitions par défaut. Si une colonne peut être affectée de la valeur NULL et qu'aucune valeur par défaut n'est définie, NULL constitue alors la valeur par défaut de la colonne de façon implicite.
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.
ON DELETE CASCADE ne peut pas être défini si un déclencheur ON DELETE de INSTEAD OF existe déjà pour la table.
Par exemple, dans la base de données AdventureWorks2008R2, la table ProductVendor possède une relation référentielle avec la table Vendor. La clé étrangère ProductVendor.BusinessEntityID fait référence à la clé primaire Vendor.BusinessEntityID.
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.BusinessEntityID, le Moteur de base de données vérifie la présence de lignes dépendantes dans la table ProductVendor. Le cas échéant, les lignes dépendantes détectées dans la table ProductVendor sont supprimées, ainsi que la ligne référencée dans la table Vendor.
En revanche, si la valeur NO ACTION est spécifiée, le Moteur de base de données génère une erreur et restaure la suppression de la ligne dans la table Vendor si au moins une ligne y fait référence 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, si chacune de ces lignes possède une relation référentielle et que la ligne référencée correspondante est mise à jour dans la table parent. La valeur par défaut est NO ACTION.NO ACTION
Le Moteur de base de données déclenche 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 la ligne de la table parent est mise à jour.SET NULL
Toutes les valeurs composant la clé étrangère sont définies sur NULL si la ligne correspondante se trouvant à l'origine dans la table parent est mise à jour. Pour que cette contrainte s'applique, les colonnes de clé étrangère doivent pouvoir cependant être définies sur NULL.SET DEFAULT
Toutes les valeurs composant la clé étrangère sont définies sur leur valeur par défaut si la ligne correspondante se trouvant à l'origine dans la table parent est mise à jour. Pour que cette contrainte s'applique, les colonnes de clé étrangère doivent disposer cependant de valeur par défaut. Si une colonne peut être affectée de la valeur NULL et qu'aucune valeur par défaut n'est définie, NULL constitue alors la valeur par défaut de la colonne de façon implicite.
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, SET NULL ou SET DEFAULT ne peut pas être définie si le 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 AdventureWorks2008R2, la table ProductVendor possède une relation référentielle avec la table Vendor. La clé étrangère ProductVendor.BusinessEntity fait référence à la clé primaire Vendor.BusinessEntityID.
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.BusinessEntityID, le Moteur de base de données vérifie la présence de lignes dépendantes dans la table ProductVendor. Le cas échéant, les lignes dépendantes détectées dans la table ProductVendor sont mises à jour, ainsi que la ligne référencée dans la table Vendor.
En revanche, si la valeur NO ACTION est spécifiée, le Moteur de base de données génère une erreur et restaure la mise à jour de la ligne dans la table Vendor si au moins une ligne y fait référence dans la table ProductVendor.
CHECK
Contrainte qui assure l'intégrité de domaine en limitant les valeurs qui peuvent être entrées dans une ou plusieurs colonnes. Les contraintes CHECK sur les colonnes calculées doivent également être marquées comme PERSISTED.logical_expression
Expression logique qui retourne TRUE ou FALSE. Les types de données d'alias ne peuvent pas faire partie de l'expression.column
Colonne, ou liste de colonnes, entre parenthèses, utilisée dans des contraintes de table pour indiquer les colonnes utilisées dans la définition de la 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.partition_scheme_name
Nom du schéma de partition qui définit les groupes de fichiers vers lesquels les partitions d'une table partitionnée seront mappées. Le schéma de partition doit exister dans la base de données.[ partition_column_name**.** ]
Désigne la colonne selon laquelle une table partitionnée sera partitionnée. La colonne doit correspondre à celle spécifiée dans la fonction de partition que partition_scheme_name utilise en termes de type de données, de longueur et de précision. Une colonne calculée qui participe à une fonction de partition doit être explicitement marquée comme PERSISTED.Important
Nous vous conseillons de spécifier NOT NULL sur la colonne de partitionnement des tables partitionnées et également des tables non partitionnées qui sont sources ou cibles d'opérations ALTER TABLE...SWITCH. Vous êtes ainsi certain que les contraintes CHECK sur les colonnes de partitionnement ne doivent pas rechercher la présence de valeurs Null. Pour plus d'informations, consultez Transfert efficace de données à l'aide du commutateur de partitionnement.
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. Les facteurs de remplissage 0 et 100 sont identiques en tous points.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 elle ne sera plus indiquée ainsi dans les versions à venir.
column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
Représente le nom du jeu de colonnes. Un jeu de colonnes est une représentation XML non typée qui combine toutes les colonnes fragmentées d'une table dans une sortie structurée. Pour plus d'informations sur les jeux de colonnes, consultez Utilisation de jeux de colonnes.< table_option> ::=
Spécifie une ou plusieurs options de table.DATA_COMPRESSION
Spécifie l'option de compression de données pour la table, le numéro de partition ou la plage de partitions spécifiés. Les options disponibles sont les suivantes :NONE
La table ou les partitions spécifiées ne sont pas compressées.ROW
La table ou les partitions spécifiées sont compressés au moyen de la compression de ligne.PAGE
La table ou les partitions spécifiées sont compressés au moyen de la compression de page.
Pour plus d'informations sur la compression, consultez Création de tables et d'index compressés.
ON PARTITIONS ( { <expression_numéro_partition> | <plage> } [ ,...n ] )
Spécifie les partitions auxquelles le paramètre DATA_COMPRESSION s'applique. Si la table n'est pas partitionnée, l'argument ON PARTITIONS génère une erreur. Si la clause ON PARTITIONS n'est pas fournie, l'option DATA_COMPRESSION s'applique à toutes les partitions d'une table partitionnée.<expression_numéro_partition> peut être spécifié des manières suivantes :
Spécifiez le numéro de partition d'une partition, par exemple : ON PARTITIONS (2).
Spécifiez des numéros de partition pour plusieurs partitions individuelles séparées par des virgules, par exemple : ON PARTITIONS (1, 5).
Spécifiez à la fois des plages et des partitions individuelles, par exemple : ON PARTITIONS (2, 4, 6 TO 8).
La <plage> peut être spécifiée sous la forme de numéros de partitions séparés par le mot TO, par exemple : ON PARTITIONS (6 TO 8).
Pour définir différents types de compression de données pour différentes partitions, spécifiez plusieurs fois l'option DATA_COMPRESSION, par exemple :
WITH ( DATA_COMPRESSION = NONE ON PARTITIONS (1), DATA_COMPRESSION = ROW ON PARTITIONS (2, 4, 6 TO 8), DATA_COMPRESSION = PAGE ON PARTITIONS (3, 5) )<index_option> ::=
Spécifie une ou plusieurs options d'index. Pour une description complète de ces options, consultez CREATE INDEX (Transact-SQL).PAD_INDEX = { ON | OFF }
Lorsque ON est spécifié, le pourcentage d'espace disponible spécifié par FILLFACTOR est appliqué aux pages de niveau intermédiaire de l'index. Lorsque OFF ou une valeur FILLFACTOR n'est pas spécifié, les pages de niveau intermédiaire de l'index sont presque entièrement remplies, ce qui laisse un espace libre suffisant pour prendre en charge au moins une ligne de la taille maximale permise par l'index, en prenant en compte l'ensemble de clés sur les pages intermédiaires. La valeur par défaut est OFF.FILLFACTOR **=**fillfactor
Spécifie un pourcentage indiquant le taux de remplissage appliqué par le Moteur de base de données au niveau feuille de chaque page d'index lors de la création ou de la modification de l'index. fillfactor doit être une valeur entière comprise entre 1 et 100. La valeur par défaut est 0. Les facteurs de remplissage 0 et 100 sont identiques en tous points.IGNORE_DUP_KEY = { ON | OFF }
Spécifie la réponse d'erreur lorsqu'une opération d'insertion essaie d'insérer des valeurs de clé en double dans un index unique. L'option IGNORE_DUP_KEY s'applique uniquement aux opérations d'insertion après la création ou la régénération de l'index. Cette option n'a aucun effet lors de l'exécution de CREATE INDEX, ALTER INDEX ou de UPDATE. La valeur par défaut est OFF.ON
Un message d'avertissement s'affichera lorsque des valeurs de clé en double sont insérées dans un index unique. Seules les lignes qui violent la contrainte d'unicité échouent.OFF
Un message d'erreur s'affichera lorsque des valeurs de clé en double sont insérées dans un index unique. L'intégralité de l'opération INSERT sera restaurée.
IGNORE_DUP_KEY ne peut pas être activé (ON) dans le cas d'index créés sur une vue, d'index non uniques, d'index XML, d'index spatiaux et d'index filtrés.
Pour afficher IGNORE_DUP_KEY, utilisez sys.indexes.
Dans la syntaxe de compatibilité descendante, WITH IGNORE_DUP_KEY est équivalent à WITH IGNORE_DUP_KEY = ON.
STATISTICS_NORECOMPUTE = { ON | OFF }
Lorsque la valeur spécifiée est ON, les statistiques d'index périmées ne sont pas recalculées automatiquement. Lorsque la valeur spécifiée est OFF, la mise à jour automatique des statistiques est activée. La valeur par défaut est OFF.ALLOW_ROW_LOCKS = { ON | OFF }
Si la valeur est ON, les verrous de ligne sont autorisés lorsque vous accédez à l'index. Le Moteur de base de données détermine le moment où les verrous de ligne sont utilisés. Si la valeur est OFF, les verrous de ligne ne sont pas utilisés. La valeur par défaut est ON.ALLOW_PAGE_LOCKS = { ON | OFF }
Si la valeur est ON, les verrous de page sont autorisés lorsque vous accédez à l'index. Le Moteur de base de données détermine le moment où les verrous de page sont utilisés. Si la valeur est OFF, les verrous de page ne sont pas utilisés. La valeur par défaut est ON.
Notes
Pour plus d'informations sur le nombre de tables, colonnes, contraintes et index autorisés, consultez Spécifications des capacités maximales pour SQL Server.
L'espace est généralement alloué aux tables et aux index par incréments d'une valeur d'étendue à la fois. Lors de la création d'une table ou d'un index, les pages sont allouées à partir de valeurs d'extension mixtes jusqu'à ce qu'il y ait suffisamment de pages pour remplir une extension uniforme. Quand il y a assez de pages pour remplir une extension uniforme, une autre extension est allouée chaque fois que l'extension active est pleine. Pour obtenir des informations sur la quantité d'espace allouée et utilisée par une table, exécutez sp_spaceused.
Le Moteur de base de données ne garantit pas l'application de l'ordre dans lequel des contraintes DEFAULT, IDENTITY, ROWGUIDCOL, ou des contraintes de colonne, sont spécifiées dans une définition de colonne.
Lors de la création d'une table, l'option QUOTED IDENTIFIER est toujours stockée avec la valeur ON dans les métadonnées de la table, même si elle a la valeur OFF au moment de sa création.
Tables temporaires
Vous pouvez créer des tables temporaires locales et globales. Les tables temporaires locales ne peuvent être vues que dans la session active ; les tables temporaires globales sont accessibles dans toutes les sessions. Les tables temporaires ne peuvent pas être partitionnées.
Faites précéder les noms de tables temporaires locales d'un signe dièse (#table_name), et les noms de tables temporaires globales de deux signes dièse (##table_name).
Les instructions SQL font référence à une table temporaire à l'aide de la valeur spécifiée pour table_name dans l'instruction CREATE TABLE, par exemple :
CREATE TABLE #MyTempTable (cola INT PRIMARY KEY);
INSERT INTO #MyTempTable VALUES (1);
Si plusieurs tables temporaires sont créées dans un lot ou une seule procédure stockée, elles doivent porter des noms différents.
Si vous créez une table temporaire locale dans une procédure stockée ou dans une application qui peut être exécutée en même temps par plusieurs utilisateurs, le Moteur de base de données doit être capable de distinguer les tables créées par les différents utilisateurs. Cela est effectué en interne par le Moteur de base de données en ajoutant de manière interne un suffixe numérique à chaque nom de table temporaire locale. Le nom complet d'une table temporaire, tel qu'il est stocké dans la table sysobjects de tempdb, est constitué du nom de table spécifié dans l'instruction CREATE TABLE plus le suffixe numérique généré par le système. Pour laisser assez de place au suffixe, le table_name spécifié pour un nom de table temporaire locale ne doit pas dépasser 116 caractères.
Les tables temporaires sont automatiquement supprimées lorsqu'elles passent hors de portée, sauf si elles sont supprimées explicitement à l'aide de DROP TABLE :
Une table temporaire locale créée dans une procédure stockée est supprimée automatiquement lorsque la procédure stockée est terminée. La table peut être référencée par des procédures stockées imbriquées exécutées par la procédure stockée qui a créé la table. La table ne peut pas être référencée par le processus qui a appelé la procédure stockée ayant créé la table.
Toutes les autres tables temporaires locales sont supprimées automatiquement à la fin de la session active.
Les tables temporaires globales sont supprimées automatiquement lorsque la session qui a créé la table se termine, et que toutes les autres tâches n'y font plus référence. L'association entre une tâche et une table n'est assurée que pendant la durée d'une seule instruction Transact-SQL. Cela signifie qu'une table temporaire globale est supprimée à la fin de la dernière instruction Transact-SQL qui faisait activement référence à la table lorsque la session de création s'est terminée.
Une table temporaire locale créée au sein d'une procédure stockée ou d'un déclencheur peut avoir le même nom qu'une table temporaire créée avant l'appel de la procédure stockée ou du déclencheur. Cependant, si une requête fait référence à une table temporaire et si deux tables temporaires portent ce nom, la table par rapport à laquelle la requête est résolue n'est pas définie. Les procédures stockées imbriquées peuvent également créer des tables temporaires portant le même nom qu'une table temporaire créée par la procédure stockée qui l'a appelée. Cependant, pour que les modifications résolvent la table créée par la procédure imbriquée, la table doit avoir la même structure, avec les mêmes noms de colonnes, que la table créée dans la procédure d'appel. C'est ce qu'illustre l'exemple suivant.
CREATE PROCEDURE dbo.Test2
AS
CREATE TABLE #t(x INT PRIMARY KEY);
INSERT INTO #t VALUES (2);
SELECT Test2Col = x FROM #t;
GO
CREATE PROCEDURE dbo.Test1
AS
CREATE TABLE #t(x INT PRIMARY KEY);
INSERT INTO #t VALUES (1);
SELECT Test1Col = x FROM #t;
EXEC Test2;
GO
CREATE TABLE #t(x INT PRIMARY KEY);
INSERT INTO #t VALUES (99);
GO
EXEC Test1;
GO
Voici l'ensemble des résultats.
(1 row(s) affected)
Test1Col
-----------
1
(1 row(s) affected)
Test2Col
-----------
2
Lorsque vous créez des tables temporaires locales ou globales, la syntaxe CREATE TABLE prend en charge les définitions de contraintes à l'exception des contraintes FOREIGN KEY. Si vous spécifiez une contrainte FOREIGN KEY dans une table temporaire, l'instruction retourne un message d'avertissement précisant que la contrainte a été ignorée. La table est toujours créée mais sans les contraintes FOREIGN KEY. Les tables temporaires ne peuvent pas être référencées dans des contraintes FOREIGN KEY.
Si une table temporaire est créée avec une contrainte nommée et la table temporaire est créée dans l'étendue d'une transaction définie par l'utilisateur, un seul utilisateur à la fois peut exécuter l'instruction qui crée la table temporaire. Par exemple, si une procédure stockée crée une table temporaire avec une contrainte de clé primaire nommée, la procédure stockée ne peut pas être exécutée simultanément par plusieurs utilisateurs.
Tables partitionnées
Avant de créer une table partitionnée à l'aide de CREATE TABLE, vous devez d'abord créer une fonction de partition pour spécifier la manière dont la table est partitionnée. Une fonction de partition est créée à l'aide de CREATE PARTITION FUNCTION. Ensuite, vous devez créer un schéma de partition pour spécifier les groupes de fichiers qui contiendront les partitions indiquées par la fonction de partition. Un schéma de partition est créé à l'aide de CREATE PARTITION SCHEME. Le placement des contraintes PRIMARY KEY ou UNIQUE pour séparer les groupes de fichiers ne peut pas être spécifié pour les tables partitionnées. Pour plus d'informations, consultez Tables et index partitionnés.
Contraintes PRIMARY KEY
Une table ne peut contenir qu'une seule contrainte PRIMARY KEY.
L'index généré par une contrainte PRIMARY KEY ne peut avoir pour conséquence une augmentation du nombre d'index dans la table à plus de 999 index non cluster et un index cluster.
Si vous ne spécifiez pas CLUSTERED ou NONCLUSTERED pour une contrainte PRIMARY KEY, CLUSTERED est utilisé s'il n'y a pas d'index cluster spécifiés pour les contraintes UNIQUE.
Toutes les colonnes définies dans une contrainte PRIMARY KEY doivent avoir la valeur NOT NULL. Si vous ne spécifiez pas la possibilité ou non de valeurs NULL, toutes les colonnes participant à une contrainte PRIMARY KEY sont définies à NOT NULL.
Si une clé primaire est définie sur une colonne avec le type de données CLR défini par l'utilisateur, l'implémentation du type doit prendre en charge le tri binaire. Pour plus d'informations, consultez Types CLR définis par l'utilisateur.
Contraintes UNIQUE
Si vous ne spécifiez pas CLUSTERED ou NONCLUSTERED pour une contrainte UNIQUE, NONCLUSTERED est utilisé par défaut.
Chaque contrainte UNIQUE génère un index. Le nombre de contraintes UNIQUE ne peut avoir pour conséquence une augmentation du nombre d'index dans la table à plus de 999 index non cluster et 1 index cluster.
Si une contrainte unique est définie sur une colonne avec le type de données CLR défini par l'utilisateur, l'implémentation du type doit prendre en charge le tri binaire ou basé sur l'opérateur. Pour plus d'informations, consultez Types CLR définis par l'utilisateur.
Contraintes FOREIGN KEY
Lorsqu'une valeur différente de NULL est entrée dans la colonne d'une contrainte FOREIGN KEY, la valeur doit exister dans la colonne référencée. Dans le cas contraire, le système retourne un message d'erreur signalant une violation de clé étrangère.
Les contraintes FOREIGN KEY sont appliquées à la colonne précédente, à moins que des colonnes sources ne soient spécifiées.
Les contraintes FOREIGN KEY ne peuvent faire référence qu'à des tables au sein de la même base de données sur le même serveur. L'intégrité référentielle inter-base de données doit être implémentée via les déclencheurs. Pour plus d'informations, consultez CREATE TRIGGER (Transact-SQL).
Les contraintes FOREIGN KEY peuvent faire référence à une autre colonne dans la même table. On appelle habituellement ce mécanisme « auto-référence ».
La clause REFERENCES d'une contrainte FOREIGN KEY au niveau des colonnes, ne peut lister qu'une colonne de référence. Cette colonne doit avoir le même type de données que la colonne pour laquelle la contrainte est définie.
La clause REFERENCES d'une contrainte FOREIGN KEY de niveau table doit avoir le même nombre de colonnes de référence que le nombre de colonnes de la liste des colonnes de la contrainte. Le type de données de chaque colonne de référence doit également être identique à la colonne de référence correspondante dans la liste des colonnes.
La valeur CASCADE, SET NULL ou SET DEFAULT ne peut pas être spécifiée si une colonne de type timestamp fait partie de la clé étrangère ou de la clé référencée.
Il est possible de combiner CASCADE, SET NULL, SET DEFAULT et NO ACTION pour des tables liées par des relations référentielles. Si le Moteur de base de données rencontre NO ACTION, il s'interrompt et restaure les actions CASCADE, SET NULL et SET DEFAULT. Lorsqu'une instruction DELETE génère une combinaison d'actions CASCADE, SET NULL, SET DEFAULT et NO ACTION, les actions CASCADE, SET NULL et SET DEFAULT sont appliquées par le Moteur de base de données avant toute recherche de NO ACTION.
Le Moteur de base de données n'a pas de limite prédéfinie du nombre de contraintes FOREIGN KEY qu'une table peut contenir et qui référencent d'autres tables ou du nombre de contraintes FOREIGN KEY possédées par d'autres tables qui font référence à une table spécifique.
Cependant, le nombre réel de contraintes FOREIGN KEY qui peuvent être utilisées est limité par la configuration matérielle et par la conception de la base de données et de l'application. Nous vous recommandons de ne pas insérer plus de 253 contraintes FOREIGN KEY dans une table et qu'une même table ne soit pas référencée par plus de 253 contraintes FOREIGN KEY. La limite effective pour vous peut varier en fonction de l'application et du matériel. Prenez en compte le coût d'application des contraintes FOREIGN KEY avant de concevoir vos bases de données et applications.
Les contraintes FOREIGN KEY ne sont pas appliquées dans les tables temporaires.
Les contraintes FOREIGN KEY ne peuvent référencer que les colonnes dans des contraintes PRIMARY KEY ou UNIQUE dans la table référencée ou dans un index UNIQUE INDEX de la table référencée.
Si une clé étrangère est définie sur une colonne avec le type de données CLR défini par l'utilisateur, l'implémentation du type doit prendre en charge le tri binaire. Pour plus d'informations, consultez Types CLR définis par l'utilisateur.
Une colonne de type varchar(max) ne peut participer à une contrainte FOREIGN KEY que si la clé primaire qu'elle référence est également définie comme étant de type varchar(max).
Définitions DEFAULT
Une colonne ne peut avoir qu'une seule définition DEFAULT (valeur par défaut).
Une définition DEFAULT peut contenir des valeurs constantes, des fonctions, des fonctions niladiques SQL-92 ou des valeurs NULL. Le tableau suivant montre les fonctions niladiques et les valeurs qu'elles retournent pour la valeur par défaut, lors d'une instruction INSERT.
Fonction niladique SQL-92
Valeur retournée
CURRENT_TIMESTAMP
Date et heure actuelles.
CURRENT_USER
Nom de l'utilisateur effectuant une insertion.
SESSION_USER
Nom de l'utilisateur effectuant une insertion.
SYSTEM_USER
Nom de l'utilisateur effectuant une insertion.
USER
Nom de l'utilisateur effectuant une insertion.
constant_expression dans une définition DEFAULT ne peut faire référence à une autre colonne de la table, ou à d'autres tables, vues ou procédures stockées.
Les définitions DEFAULT ne peuvent pas être créées dans des colonnes ayant un type de données timestamp ou une propriété IDENTITY.
Les définitions DEFAULT ne peuvent pas être créées pour des colonnes qui possèdent des types de données d'alias, si ces types de données sont liés à un objet par défaut.
Contraintes CHECK
Une colonne peut posséder un nombre illimité de contraintes CHECK et la condition peut inclure plusieurs expressions logiques combinées par AND et OR. S'il existe plusieurs contraintes CHECK pour une même colonne, elles sont validées dans l'ordre de leur création.
La condition de recherche doit correspondre à une expression booléenne et ne peut pas faire référence à une autre table.
Une contrainte CHECK de niveau colonne ne peut faire référence qu'à la colonne contenant la contrainte, et une contrainte CHECK de niveau table ne peut faire référence qu'aux colonnes d'une même table.
Les contraintes CHECK et les règles servent toutes les deux à valider les données lors des instructions INSERT et UPDATE.
Quand il existe une règle et une ou plusieurs contraintes CHECK pour une colonne, toutes les restrictions sont évaluées.
Les contraintes CHECK ne peuvent pas être définies sur les colonnes text, ntext ou image.
Informations supplémentaires sur les contraintes
Un index créé pour une contrainte ne peut pas être supprimé en utilisant DROP INDEX ; la contrainte doit être supprimée à l'aide de ALTER TABLE. Un index créé pour une contrainte et utilisé par elle peut être recréé en utilisant ALTER INDEX.
Les noms de contrainte doivent suivre les règles des identificateurs, excepté le fait que le nom ne peut pas commencer par un signe dièse (#). En l'absence de constraint_name, un nom généré par le système est affecté à la contrainte. Le nom de la contrainte apparaît dans tous les messages d'erreur relatifs aux violations de contraintes.
Lorsqu'une contrainte est violée dans une instruction INSERT, UPDATE ou DELETE, l'instruction est terminée. Cependant, lorsque SET XACT_ABORT a la valeur OFF, la transaction, si l'instruction fait partie d'une transaction explicite, continue à être traitée. Lorsque SET XACT_ABORT a pour valeur ON, toute la transaction est restaurée. Vous pouvez également utiliser l'instruction ROLLBACK TRANSACTION avec la définition de la transaction en vérifiant la fonction système **@@**ERROR.
Si ALLOW_ROW_LOCKS = ON et ALLOW_PAGE_LOCK = ON, les verrous de ligne, de page et de table sont autorisés lorsque vous accédez à l'index. Le Moteur de base de données choisit le verrou approprié et peut convertir un verrou de ligne ou de page en verrou de table. Pour plus d'informations, consultez Escalade de verrous (moteur de base de données). Si ALLOW_ROW_LOCKS = OFF et ALLOW_PAGE_LOCK = OFF, seuls les verrous de table sont autorisés lorsque vous accédez à l'index. Pour plus d'informations sur la définition de la granularité des verrous d'un index, consultez Personnalisation du verrouillage pour un index.
Si une table contient des contraintes FOREIGN KEY ou CHECK, et des déclencheurs, les conditions de la contrainte sont évaluées avant l'exécution du déclencheur.
Pour obtenir des informations sur une table et ses colonnes, utilisez sp_help ou sp_helpconstraint. Pour renommer une table, utilisez sp_rename. Pour obtenir un rapport sur les vues et procédures stockées qui dépendent d'une table, utilisez sys.dm_sql_referenced_entities et sys.dm_sql_referencing_entities.
Règles des possibilités de valeurs Null dans une définition de table
La possibilité de valeurs Null pour une colonne détermine si cette colonne peut accepter une valeur Null (NULL) comme données dans la colonne. La valeur NULL n'est pas équivalente à la valeur zéro ou à un blanc : cela signifie qu'il n'y a pas eu d'entrée dans la colonne ou que la valeur NULL explicite a été spécifiée. Cela implique généralement que la valeur est inconnue ou non applicable.
Lorsque vous créez ou modifiez une table à l'aide des instructions CREATE TABLE ou ALTER TABLE, les paramètres de la base de données et de la session influencent et éventuellement modifient la possibilité de valeur Null pour le type de données utilisé dans une définition de colonne. Il est recommandé de toujours définir explicitement une colonne comme NULL ou NOT NULL ou, si vous utilisez un type de données défini par l'utilisateur, d'autoriser la colonne à utiliser la possibilité de valeur NULL par défaut pour ce type de données. Les colonnes fragmentées doivent toujours autoriser les valeurs NULL.
Lorsque vous ne l'avez pas spécifiée explicitement, la possibilité de valeurs Null pour les colonnes respecte les règles récapitulées dans le tableau suivant :
Type de données de la colonne |
Règle |
|---|---|
Type de données d'alias |
Le Moteur de base de données utilise la possibilité de valeurs Null spécifiée lors de la création du type de données. Utilisez sp_help pour obtenir la possibilité de valeurs Null par défaut du type de données. |
Type CLR défini par l'utilisateur |
La possibilité de valeur NULL est déterminée en fonction de la définition de la colonne. |
Type de données fourni par le système |
Si le type de données fourni par le système ne possède qu'une option, il a priorité. Les types de données timestamp doivent être NOT NULL. Lorsque les paramètres de session ont pour valeur ON en utilisant SET :
|
Si aucune des options ANSI_NULL_DFLT n'est définie pour la session et si la base de données est définie avec les valeurs par défaut (ANSI_NULL_DEFAULT étant OFF), la valeur par défaut, NOT NULL, est affectée.
La possibilité de valeurs NULL dans les colonnes calculées est déterminée automatiquement par le Moteur de base de données. Pour rechercher si les valeurs NULL sont acceptées ou non par ce type de colonne, utilisez la fonction COLUMNPROPERTY avec la propriété AllowsNull.
Notes
Que ce soit pour le pilote ODBC de SQL Server ou pour le fournisseur Microsoft OLE DB de SQL Server, ANSI_NULL_DFLT_ON a par défaut la valeur ON. Les utilisateurs ODBC et OLE DB peuvent réaliser cette configuration dans les sources de données ODBC ou à l'aide d'attributs ou de propriétés de connexion définies par l'application.
Compression de données
Les tables système ne peuvent pas être activées pour la compression. Lorsque vous créez une table ou un index, la compression de données est définie sur NONE, sauf indication contraire. Si vous spécifiez une liste de partitions ou une partition hors limites, une erreur est générée. Pour plus d'informations sur la compression de données, consultez Création de tables et d'index compressés.
Pour évaluer la façon dont la modification de l'état de compression affectera une table, un index ou une partition, utilisez la procédure stockée sp_estimate_data_compression_savings.
Autorisations
Nécessite une autorisation CREATE TABLE dans la base de données et une autorisation ALTER pour le schéma dans lequel la table a été créée.
Si des colonnes dans l'instruction CREATE TABLE sont définies avec le type de données CLR défini par l'utilisateur, la propriété du type ou une autorisation REFERENCES est nécessaire.
Si des colonnes dans l'instruction CREATE TABLE sont associées à une collection de schémas XML, la propriété de la collection de schémas XML ou une autorisation REFERENCES pour le type est nécessaire.
Exemples
A. Utilisation des contraintes PRIMARY KEY
Cet exemple montre la définition de colonne pour une contrainte PRIMARY KEY avec un index cluster sur la colonne BusinessEntityID de la table Employee (autorisant le système à fournir le nom de contrainte) dans la base de données exemple AdventureWorks2008R2.
BusinessEntityID int
PRIMARY KEY CLUSTERED
B. Utilisation des contraintes FOREIGN KEY
Une contrainte FOREIGN KEY sert à référencer une autre table. Les clés étrangères peuvent être des clés à colonne unique ou sur plusieurs colonnes. Cet exemple montre une contrainte FOREIGN KEY à colonne unique dans la table SalesOrderHeader qui fait référence à la table SalesPerson. Seule la clause REFERENCES est obligatoire pour une contrainte FOREIGN KEY à colonne unique.
SalesPersonID int NULL
REFERENCES SalesPerson(BusinessEntityID)
Vous pouvez également utiliser de manière explicite la clause FOREIGN KEY et redéterminer l'attribut de la colonne. Notez que le nom de la colonne ne doit pas nécessairement être le même dans les deux tables.
FOREIGN KEY (SalesPersonID) REFERENCES SalesPerson(BusinessEntityID)
Les contraintes de clés sur plusieurs colonnes sont créées comme des contraintes de table. Dans la base de données AdventureWorks2008R2, la table SpecialOfferProduct inclut une clause PRIMARY KEY sur plusieurs colonnes. L'exemple suivant montre comment faire référence à cette clé à partir d'une autre table ; un nom de contrainte explicite n'est pas obligatoire.
CONSTRAINT FK_SpecialOfferProduct_SalesOrderDetail FOREIGN KEY
(ProductID, SpecialOfferID)
REFERENCES SpecialOfferProduct (ProductID, SpecialOfferID)
C. Utilisation des contraintes UNIQUE
Les contraintes UNIQUE servent à garantir l'unicité dans les colonnes qui n'ont pas de clés primaires. L'exemple suivant applique la restriction suivant laquelle la colonne Name de la table Product doit être unique.
Name nvarchar(100) NOT NULL
UNIQUE NONCLUSTERED
D. Utilisation des définitions DEFAULT
Les valeurs par défaut fournissent une valeur (avec les instructions INSERT et UPDATE) lorsqu'aucune valeur n'est fournie. Par exemple, la base de données AdventureWorks2008R2 peut inclure une table de correspondance répertoriant les différents emplois que les employés peuvent occuper dans la société. Sous une colonne décrivant chaque emploi, une chaîne de caractères par défaut peut fournir une description lorsqu'aucune description réelle n'est entrée explicitement.
DEFAULT 'New Position - title not formalized yet'
Outre des constantes, les définitions DEFAULT peuvent inclure des fonctions. Utilisez l'exemple suivant pour obtenir la date actuelle d'une entrée.
DEFAULT (getdate())
Une fonction niladique peut également améliorer l'intégrité des données. Afin de garder une trace de l'utilisateur qui a inséré une ligne, utilisez la fonction niladique pour USER. N'entourez pas les fonctions niladiques de parenthèses.
DEFAULT USER
E. Utilisation des contraintes CHECK
L'exemple suivant affiche une restriction appliquée aux valeurs entrées dans la colonne CreditRating de la table Vendor. La contrainte n'a pas de nom.
CHECK (CreditRating >= 1 and CreditRating <= 5)
Cet exemple montre une contrainte nommée avec un modèle de restriction pour les données caractères entrées dans une colonne d'une table.
CONSTRAINT CK_emp_id CHECK (emp_id LIKE
'[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]'
OR emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]')
Cet exemple spécifie que les valeurs doivent figurer dans une liste spécifique ou suivre un modèle donné.
CHECK (emp_id IN ('1389', '0736', '0877', '1622', '1756')
OR emp_id LIKE '99[0-9][0-9]')
F. Affichage des définitions de tables complètes
Cet exemple montre des définitions complètes de tables avec toutes les définitions de contraintes pour la table PurchaseOrderDetail créée dans la base de données AdventureWorks2008R2. Notez que pour exécuter l'exemple, le schéma de table est modifié en dbo.
CREATE TABLE dbo.PurchaseOrderDetail
(
PurchaseOrderID int NOT NULL
REFERENCES Purchasing.PurchaseOrderHeader(PurchaseOrderID),
LineNumber smallint NOT NULL,
ProductID int NULL
REFERENCES Production.Product(ProductID),
UnitPrice money NULL,
OrderQty smallint NULL,
ReceivedQty float NULL,
RejectedQty float NULL,
DueDate datetime NULL,
rowguid uniqueidentifier ROWGUIDCOL NOT NULL
CONSTRAINT DF_PurchaseOrderDetail_rowguid DEFAULT (newid()),
ModifiedDate datetime NOT NULL
CONSTRAINT DF_PurchaseOrderDetail_ModifiedDate DEFAULT (getdate()),
LineTotal AS ((UnitPrice*OrderQty)),
StockedQty AS ((ReceivedQty-RejectedQty)),
CONSTRAINT PK_PurchaseOrderDetail_PurchaseOrderID_LineNumber
PRIMARY KEY CLUSTERED (PurchaseOrderID, LineNumber)
WITH (IGNORE_DUP_KEY = OFF)
)
ON PRIMARY;
G. Création d'une table avec une colonne XML de type collection de schémas XML
L'exemple suivant crée une table avec une colonne xml de type collection de schémas XML HRResumeSchemaCollection. Le mot clé DOCUMENT spécifie que chaque instance du type de données xml dans column_name ne peut contenir qu'un seul élément de niveau supérieur.
USE AdventureWorks2008R2;
GO
CREATE TABLE HumanResources.EmployeeResumes
(LName nvarchar(25), FName nvarchar(25),
Resume xml( DOCUMENT HumanResources.HRResumeSchemaCollection) );
H. Création d'une table partitionnée
L'exemple suivant crée une fonction de partition pour partitionner une table ou un index en quatre partitions. L'exemple crée ensuite un schéma de partition pour spécifier les groupes de fichiers qui contiendront chacune des quatre partitions. Enfin, l'exemple crée une table qui utilise le schéma de partition. Cet exemple suppose que les groupes de fichiers existent déjà dans la base de données.
CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000) ;
GO
CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO (test1fg, test2fg, test3fg, test4fg) ;
GO
CREATE TABLE PartitionTable (col1 int, col2 char(10))
ON myRangePS1 (col1) ;
GO
En fonction des valeurs de la colonne col1 de PartitionTable, les partitions sont affectées en suivant les méthodes ci-dessous.
Groupe de fichiers |
test1fg |
test2fg |
test3fg |
test4fg |
|---|---|---|---|---|
Partition |
1 |
2 |
3 |
4 |
Valeurs |
col 1 <= 1 |
col1 > 1 AND col1 <= 100 |
col1 > 100 AND col1 <= 1 000 |
col1 > 1 000 |
I. Utilisation du type de données uniqueidentifier dans une colonne
L'exemple suivant crée une table avec une colonne uniqueidentifier. Il utilise une contrainte PRIMARY KEY pour empêcher les utilisateurs de la table d'insérer des doublons, et la fonction NEWSEQUENTIALID() dans la contrainte DEFAULT pour fournir des valeurs aux nouvelles lignes. La propriété ROWGUIDCOL est appliquée à la colonne uniqueidentifier de sorte qu'elle peut être référencée en utilisant le mot clé $ROWGUID.
CREATE TABLE dbo.Globally_Unique_Data
(guid uniqueidentifier CONSTRAINT Guid_Default DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
Employee_Name varchar(60)
CONSTRAINT Guid_PK PRIMARY KEY (guid) );
J. Utilisation d'une expression pour une colonne calculée
L'exemple suivant illustre l'utilisation d'une expression ((low + high)/2) pour le calcul de la colonne calculée myavg.
CREATE TABLE dbo.mytable
( low int, high int, myavg AS (low + high)/2 ) ;
K. Création d'une colonne calculée basée sur une colonne de type défini par l'utilisateur
L'exemple suivant crée une table, avec une colonne définie comme utf8string dont le type de données est défini par l'utilisateur, en supposant que l'assembly du type et le type lui-même ont déjà été créés dans la base de données active. Une seconde colonne est définie d'après utf8string et utilise la méthode ToString() de type(class)utf8string pour calculer une valeur pour la colonne.
CREATE TABLE UDTypeTable
( u utf8string, ustr AS u.ToString() PERSISTED ) ;
L. Utilisation de la fonction USER_NAME pour une colonne calculée
L'exemple suivant utilise la fonction USER_NAME() dans la colonne myuser_name.
CREATE TABLE dbo.mylogintable
( date_in datetime, user_id int, myuser_name AS USER_NAME() ) ;
M. Création d'une table qui comporte une colonne FILESTREAM
L'exemple suivant crée une table qui comporte une colonne FILESTREAMPhoto. Si une table comporte une un ou plusieurs colonnes FILESTREAM, elle doit aussi comporter une colonne ROWGUIDCOL.
CREATE TABLE dbo.EmployeePhoto
(
EmployeeId int NOT NULL PRIMARY KEY
,Photo varbinary(max) FILESTREAM NULL
,MyRowGuidColumn uniqueidentifier NOT NULL ROWGUIDCOL
UNIQUE DEFAULT NEWID()
);
N. Création d'une table qui utilise la compression de ligne
L'exemple suivant crée une table qui utilise la compression de ligne.
CREATE TABLE dbo.T1
(c1 int, c2 nvarchar(200) )
WITH (DATA_COMPRESSION = ROW);
Pour obtenir d'autres exemples de compression de données, consultez Création de tables et d'index compressés.
O. Création d'une table qui comporte des colonnes fragmentées et un jeu de colonnes
Les exemples suivants montrent comment créer une table qui comporte une colonne fragmentée et une table qui comporte deux colonnes fragmentées et un jeu de colonnes. Ces exemples utilisent la syntaxe de base. Pour obtenir des exemples plus complexes, consultez Utilisation de colonnes fragmentées et Utilisation de jeux de colonnes.
Cet exemple crée une table qui comporte une colonne fragmentée.
CREATE TABLE dbo.T1
(c1 int PRIMARY KEY,
c2 varchar(50) SPARSE NULL ) ;
Cet exemple crée une table qui comporte deux colonnes fragmentées et un jeu de colonnes nommé CSet.
CREATE TABLE T1
(c1 int PRIMARY KEY,
c2 varchar(50) SPARSE NULL,
c3 int SPARSE NULL,
CSet XML COLUMN_SET FOR ALL_SPARSE_COLUMNS ) ;
Voir aussi