Référence de Transact-SQL  
 
 
ALTER TABLE
Modifie la définition d'une table en changeant, en ajoutant ou en supprimant des colonnes ou des contraintes, ou en désactivant ou activant des contraintes et des déclencheurs.
 
Syntaxe
ALTER TABLE table  
{ [ ALTER COLUMN column_name  
    { new_data_type [ ( precision [ , scale ] ) ]  
        [ COLLATE < collation_name > ]  
        [ NULL | NOT NULL ]  
        | {ADD | DROP } ROWGUIDCOL }  
    ]  
    | ADD  
        { [ < column_definition > ]  
        |  column_name AS computed_column_expression  
        } [ ,...n ]  
    | [ WITH CHECK | WITH NOCHECK ] ADD  
        { < table_constraint > } [ ,...n ]  
    | DROP  
        { [ CONSTRAINT ] constraint_name  
            | COLUMN column } [ ,...n ]  
    | { CHECK | NOCHECK } CONSTRAINT  
        { ALL | constraint_name [ ,...n ] }  
    | { ENABLE | DISABLE } TRIGGER  
        { ALL | trigger_name [ ,...n ] }  
}
 
< column_definition > ::=  
    { column_name data_type }  
    [ [ DEFAULT constant_expression ] [ WITH VALUES ]  
    | [ IDENTITY [ (seed , increment ) [ NOT FOR REPLICATION ] ] ]  
        ]  
    [ ROWGUIDCOL ]  
    [ COLLATE < collation_name > ]  
    [ < column_constraint > ] [ ...n ]  
 
< column_constraint > ::=  
    [ CONSTRAINT constraint_name ]  
    { [ NULL | NOT NULL ]  
        | [ { PRIMARY KEY | UNIQUE }  
            [ CLUSTERED | NONCLUSTERED ]  
            [ WITH FILLFACTOR = fillfactor ]  
            [ ON { filegroup | DEFAULT } ]  
            ]  
        | [ [ FOREIGN KEY ]  
            REFERENCES ref_table [ ( ref_column ) ]  
            [ ON DELETE { CASCADE | NO ACTION } ]  
            [ ON UPDATE { CASCADE | NO ACTION } ]  
            [ NOT FOR REPLICATION ]  
            ]  
        | CHECK [ NOT FOR REPLICATION ]  
            ( logical_expression )  
    }
 
< table_constraint > ::=  
    [ CONSTRAINT constraint_name ]  
    { [ { PRIMARY KEY | UNIQUE }  
        [ CLUSTERED | NONCLUSTERED ]  
        { ( column [ ,...n ] ) }  
        [ WITH FILLFACTOR = fillfactor ]  
        [ ON {filegroup | DEFAULT } ]  
        ]  
        |    FOREIGN KEY  
            [ ( column [ ,...n ] ) ]  
            REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]  
            [ ON DELETE { CASCADE | NO ACTION } ]  
            [ ON UPDATE { CASCADE | NO ACTION } ]  
            [ NOT FOR REPLICATION ]  
        | DEFAULT constant_expression  
            [ FOR column ] [ WITH VALUES ]  
        |    CHECK [ NOT FOR REPLICATION ]  
            ( search_conditions )  
    }
 
Arguments
table
 
Nom de la table à modifier. Si la table ne figure pas dans la base de données en cours ou si elle n'appartient pas au propriétaire actuel, la base de données et le propriétaire peuvent être explicitement spécifiés.
 
ALTER COLUMN
 
Spécifie que la colonne donnée doit être modifiée. L'option ALTER COLUMN n'est pas autorisée si le niveau de compatibilité est 65 ou antérieur. Pour plus d'informations, voir sp_dbcmptlevel.  
 
La colonne modifiée ne peut pas être :  
 
Une colonne contenant des données de type text, image, ntext ou timestamp ;
 
 
la colonne ROWGUIDCOL de la table ;
 
 
une colonne calculée ou une colonne utilisée dans une colonne traitée ;
 
 
Une colonne dupliquée ;
 
 
Utilisée dans un index, sauf lorsque les données de la colonne sont du type varchar, nvarchar ou varbinary, que le type de données n'est pas modifié et que la nouvelle taille est égale ou supérieure à l'ancienne taille ;
 
 
utilisée dans des statistiques générées par l'instruction CREATE STATISTICS. Il faut tout d'abord supprimer les statistiques à l'aide de l'instruction DROP STATISTICS ; Les statistiques générées automatiquement par l'optimiseur de requêtes sont automatiquement supprimées par l'option ALTER COLUMN.
 
 
Utilisée dans une contrainte PRIMARY KEY ou [FOREIGN KEY] REFERENCES ;
 
 
utilisée dans une contrainte CHECK ou UNIQUE. Toutefois la modification d'une colonne à longueur variable utilisée dans une contrainte CHECK ou UNIQUE est autorisée ;
 
 
associée à une valeur par défaut, à moins que la modification de la longueur, de la précision ou de l'échelle d'une colonne soit autorisée si le type de données n'est pas modifié.  
La modification de certains types de données peut entraîner la modification des données. Ainsi, la conversion d'une colonne de type nchar ou nvarchar au type char ou varchar peut entraîner la conversion de caractères étendus. Pour plus d'informations, voir CAST et CONVERT. La réduction de la précision et de l'échelle d'une colonne peut tronquer les données.
 
column_name
 
Nom de la colonne à modifier, à ajouter ou à supprimer. L'argument nom_colonne peut être omis dans le cas de nouvelles colonnes de type de données timestamp. Le nom timestamp est utilisé si aucun nom_colonne n'est spécifié pour une colonne de type de données timestamp.
 
new_data_type
 
Nouveau type de données pour la colonne à modifier. Les critères à respecter pour le paramètre new_data_type d'une colonne modifiée sont :  
 
le type de données précédent doit pouvoir être implicitement converti au nouveau type de données ;
 
 
new_data_type ne peut pas être timestamp.
 
 
Les valeurs par défaut ANSI NULL sont toujours activées pour ALTER COLUMN ; si l'option n'est pas spécifiée, la colonne accepte les valeurs NULL.
 
 
Le remplissage ANSI est toujours activé pour ALTER COLUMN.
 
 
si la colonne modifiée est une colonne d'identité, new_data_type doit prendre en charge la propriété d'identité ;
 
 
la configuration actuelle de SET ARITHABORT est ignorée. ALTER TABLE fonctionne comme si l'option ARITHABORT était activée (ON).  
precision
 
Précision du type de données spécifié. Pour plus d'informations sur les valeurs de précision valides, voir Précision, échelle et longueur.  
 
scale
 
Echelle du type de données spécifié. Pour plus d'informations sur les valeurs d'échelle valides, voir Precision, Scale, and Length.
 
COLLATE < collation_name >
 
Nouveau classement pour la colonne modifiée. Le nom du classement peut être un nom de classement Windows ou SQL. Pour obtenir la liste des noms de classement et pour plus d'informations, voir Nom de classement Windows et Nom de classement SQL.
 
La clause COLLATE peut être utilisée pour modifier uniquement les classements des colonnes comportant les types de données char, varchar, text, nchar, nvarchar et ntext. Sans autre indication, le classement par défaut de la base de données est attribué à la colonne.
 
ALTER COLUMN ne peut pas utiliser un autre classement si l'une des conditions suivantes est présente :  
 
une contrainte de vérification, une contrainte de clé étrangère ou des colonnes calculées font référence à la colonne modifiée ;  
 
 
un index, des statistiques ou un index de texte intégral sont générés sur la colonne ; Les statistiques créées automatiquement sur la colonne modifiée seront supprimées si le classement de la colonne est modifié.
 
 
une vue ou une fonction SCHEMABOUND fait référence à la colonne.  
Pour plus d'informations sur la clause COLLATE, voir COLLATE.
 
NULL | NOT NULL
 
Précise si la colonne accepte les valeurs NULL. Les colonnes n'autorisant pas les valeurs NULL peuvent uniquement être ajoutées à l'aide de l'instruction ALTER TABLE si une valeur par défaut a été définie pour celles-ci. Une nouvelle colonne ajoutée à une table doit accepter les valeurs NULL ou une valeur par défaut doit être spécifiée pour celle-ci.
 
Si la nouvelle colonne accepte les valeurs NULL et qu'aucune valeur n'est spécifiée, la colonne contient une valeur NULL pour chaque ligne de la table. Si la nouvelle colonne accepte les valeurs NULL et qu'une valeur par défaut est ajoutée avec la nouvelle colonne, l'option WITH VALUES peut être utilisée pour stocker la valeur par défaut dans la nouvelle colonne pour chaque ligne existante de la table.
 
Si la nouvelle colonne n'accepte pas les valeurs NULL, une définition de valeur par défaut DEFAULT doit être ajoutée avec la nouvelle colonne. La nouvelle colonne est alors automatiquement chargée avec la valeur par défaut dans les nouvelles colonnes dans chaque ligne existante.
 
L'option NULL peut être spécifiée dans l'instruction ALTER COLUMN pour qu'une colonne NOT NULL accepte les valeurs NULL, excepté pour les colonnes de contraintes PRIMARY KEY. L'option NOT NULL peut être spécifiée dans l'instruction ALTER COLUMN uniquement si la colonne ne contient aucune valeur NULL. Les valeurs nulles doivent être mises à jour pour prendre une valeur avant que l'instruction ALTER COLUMN NOT NULL puisse être autorisée, par exemple :
 
UPDATE MyTable SET NullCol = N'some_value' WHERE NullCol IS NULL
 
ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR(20) NOT NULL
 
Si les options NULL ou NOT NULL sont spécifiées avec ALTER COLUMN, vous devez également spécifier l'option new_data_type [(precision [, scale ])]. Si vous ne modifiez pas le type de données, la précision ou l'échelle, spécifiez les valeurs actuelles de la colonne.
 
[ {ADD | DROP} ROWGUIDCOL ]
 
Précise que la propriété ROWGUIDCOL est supprimée de la colonne spécifiée ou qu'elle lui est ajoutée. ROWGUIDCOL est un mot-clé indiquant que la colonne fait office d'identificateur unique global de ligne. Une seule colonne uniqueidentifier par table peut servir de colonne ROWGUIDCOL. La propriété ROWGUIDCOL ne peut être affectée qu'à une colonne uniqueidentifier.
 
La propriété ROWGUIDCOL n'assure pas l'unicité des valeurs stockées dans la colonne. Elle ne peut de plus générer automatiquement de valeurs pour les nouvelles lignes insérées dans la table. Pour générer des valeurs uniques pour chaque colonne, utilisez la fonction NEWID sur des instructions INSERT ou faites de la fonction NEWID la valeur par défaut de la colonne.
 
ADD
 
Spécifie qu'une ou plusieurs définitions de colonnes, définitions de colonnes calculées ou contraintes de tables sont ajoutées.
 
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 physiquement stockée dans la table, mais traitée à partir d'une expression utilisant d'autres colonnes figurant 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.
 
Les colonnes calculées peuvent être utilisées dans les listes de sélections, dans les clauses WHERE ou ORDER BY ou chaque fois qu'une expression régulière peut être utilisée, à l'exception des cas suivants :  
 
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 que le type de données du résultat est autorisé dans les colonnes d'index.  
Par exemple, si la table comporte les colonnes d'entiers a et b, la colonne calculée a+b peut être indexée, mais la colonne calculée a+DATEPART(dd, GETDATE()) ne peut pas être indexée car sa valeur peut changer dans une invocation subséquente.
 
Une colonne calculée ne peut pas être la cible d'une instruction INSERT ou UPDATE.  
 
 
Remarque  Chaque ligne d'une table peut avoir une valeur différente dans les colonnes utilisées pour une colonne calculée et, par conséquent, la colonne traitée peut ne pas avoir le même résultat pour toutes les lignes.
 
n
 
Emplacement réservé qui indique que l'élément précédent peut se répéter n fois.
 
WITH CHECK | WITH NOCHECK
 
Précise si les données de la table doivent être vérifiées par rapport à une contrainte FOREIGN KEY ou CHECK nouvellement ajoutée ou réactivée. Si l'option n'est pas spécifiée, l'option WITH CHECK est utilisée pour les nouvelles contraintes et l'option WITH NOCHECK pour les contraintes réactivées.
 
Les clauses WITH CHECK et WITH NOCHECK ne peuvent pas être utilisées pour les contraintes PRIMARY KEY et UNIQUE.
 
Utilisez WITH NOCHECK si vous ne voulez pas vérifier les nouvelles contraintes CHECK ou FOREIGN KEY dans les données existantes. Ceci n'est pas recommandé, sauf dans quelques cas assez rares. La nouvelle contrainte sera évaluée dans toutes les mises à jour ultérieures. Toute violation de contrainte supprimée par l'option WITH NOCHECK lors de l'ajout de la contrainte peut faire échouer les mises à jour ultérieures, si celles-ci mettent des lignes à jour à l'aide de données non conformes à la contrainte.
 
Les contraintes définies avec l'option WITH NOCHECK sont ignorées par l'optimiseur de requêtes. Elles seront ignorées tant qu'elles n'auront pas été réactivées à l'aide de l'instruction ALTER TABLE table CHECK CONSTRAINT ALL.
 
DROP { [CONSTRAINT] constraint_name | COLUMN column_name }
 
Spécifie que constraint_name ou column_name doit être supprimé de la table. L'option DROP COLUMN n'est pas autorisée si le niveau de compatibilité est 65 ou antérieur. Plusieurs colonnes et contraintes peuvent être listées. Une colonne ne peut pas être supprimée si elle est :  
 
Une colonne dupliquée ;
 
 
Utilisée dans un index ;
 
 
Utilisée dans une contrainte CHECK, FOREIGN KEY, UNIQUE ou PRIMARY KEY ;
 
 
Associée à une valeur par défaut définie à l'aide du mot-clé DEFAULT ou liée à un objet par défaut ;
 
 
Liée à une règle.  
{ CHECK | NOCHECK} CONSTRAINT
 
Spécifie si constraint_name est activé ou désactivé. Si la contrainte est désactivée, les insertions et mises à jour ultérieures ne sont pas validées par rapport aux conditions de la contrainte. Cette option peut uniquement être utilisée avec les contraintes FOREIGN KEY et CHECK.  
 
ALL  
Spécifie que toutes les contraintes sont désactivées à l'aide de l'option NOCHECK ou activées à l'aide de l'option CHECK.  
{ENABLE | DISABLE} TRIGGER
 
Spécifie si trigger_name est activé ou désactivé. Lorsqu'un déclencheur est désactivé, il est néanmoins défini pour la table. Toutefois lorsque des instructions INSERT, UPDATE et DELETE sont exécutées sur la table, les actions sur le déclencheur ne sont pas effectuées tant que celui-ci n'a pas été réactivé.  
 
ALL  
Spécifie que tous les déclencheurs de la table sont activés ou désactivés.
 
trigger_name  
Spécifie le nom du déclencheur à activer ou désactiver.  
column_name data_type
 
Type de données de la nouvelle colonne. L'argument data_type peut être tout type de données Microsoft® SQL Server™ ou défini par l'utilisateur.
 
DEFAULT
 
Mot-clé spécifiant 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. Pour garantir une compatibilité avec les versions antérieures de SQL Server, vous pouvez affecter un nom de contrainte à une valeur par défaut.
 
IDENTITY
 
Spécifie que la nouvelle colonne est une colonne d'identité. Lorsqu'une ligne est ajoutée à la table, SQL Server affecte une valeur incrémentée unique à la colonne. Les colonnes d'identité sont normalement utilisées avec les contraintes PRIMARY KEY comme identificateur unique de ligne pour la table. La propriété IDENTITY peut être affectée à une colonne de type tinyint, smallint, int, bigint, decimal(p,0) ou numeric(p,0). Une seule colonne d'identité peut être créée par table. Vous ne pouvez pas utiliser le mot-clé DEFAULT ni les valeurs par défaut liées avec une colonne d'identité. Vous devez spécifier à la fois la valeur de départ et l'incrément ou aucune de ces valeurs. Si aucune valeur n'est spécifiée, la valeur par défaut est de (1,1).  
 
Seed  
Valeur utilisée pour la 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
 
Spécifie que la propriété IDENTITY ne doit pas être appliquée lorsqu'un processus de réplication, tel sqlrepl, insère des données dans la table. NOT FOR REPLICATION peut également être spécifiée pour une contrainte. Dans ce cas, la contrainte n'est pas vérifiée lorsqu'un processus de réplication insère des données dans la table.
 
CONSTRAINT
 
Spécifie le début d'une contrainte PRIMARY KEY, UNIQUE, FOREIGN KEY, ou CHECK, ou une définition DEFAULT.
 
constraint_name
 
Nouvelle contrainte. 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.
 
PRIMARY KEY
 
Contrainte assurant l'intégrité de l'entité d'une colonne ou de plusieurs colonnes données au moyen d'un seul index. Une seule contrainte PRIMARY KEY peut être créée par table.
 
UNIQUE
 
Contrainte assurant l'intégrité de l'entité d'une colonne ou de plusieurs colonnes données au moyen d'un seul index.
 
CLUSTERED | NONCLUSTERED
 
Spécifie qu'un index, ordonné ou non en clusters, est créé pour la contrainte PRIMARY KEY ou UNIQUE. L'option par défaut de la contrainte PRIMARY KEY est CLUSTERED tandis que celle de la contrainte UNIQUE est NONCLUSTERED.
 
Si une contrainte ou un index ordonné en clusters existe déjà pour la table, vous ne pouvez pas spécifier CLUSTERED dans l'instruction ALTER TABLE. Dans ce cas, l'option par défaut d'une contrainte PRIMARY KEY devient NONCLUSTERED.
 
WITH FILLFACTOR = fillfactor
 
Spécifie le remplissage par SQL Server des pages d'index utilisées pour stocker les données d'index. Les valeurs fillfactor définies par l'utilisateur peuvent varier de 1 à 100. Si aucune valeur n'est spécifiée, la valeur par défaut est 0. Une valeur fillfactor peu élevée crée un index comportant plus d'espace pour les nouvelles entrées, ce qui évite de devoir affecter de l'espace supplémentaire. Pour plus d'informations, voir CREATE INDEX.  
 
ON {filegroup | DEFAULT}
 
Spécifie le lieu de stockage de l'index créé pour la contrainte. Si filegroup est spécifié, l'index est créé dans le groupe de fichiers désigné. Si l'option DEFAULT est utilisée, l'index est créé dans le groupe de fichiers par défaut. Si l'option ON est spécifiée, l'index est créé dans le groupe de fichiers contenant la table. Si l'option ON est spécifiée lors de la création d'un index ordonné en clusters pour une contrainte PRIMARY KEY ou UNIQUE, la table das son intégralité est placée dans le groupe de fichiers spécifié lorsque l'index ordonné en clusters est créé.
 
La valeur DEFAULT n'est pas un mot clé dans ce contexte. Elle représente l'identificateur du groupe de fichiers par défaut et doit être délimitée, par exemple : ON "DEFAULT" ou ON [DEFAULT].
 
FOREIGN KEY...REFERENCES
 
Contrainte assurant 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.
 
ref_table
 
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 {CASCADE | NO ACTION}
 
Spécifie l'action qui se produit dans une ligne de la table modifiée, si cette ligne comporte une relation référentielle et que la ligne référencée est supprimée de la table parent. La valeur par défaut est NO ACTION.  
 
Si la valeur CASCADE est spécifiée, une ligne est supprimée de la table de référence si elle est supprimée de la table parent. Si la valeur NO ACTION est spécifiée, SQL Server déclenche une erreur et la suppression de la ligne dans la table parent est annulée.
 
L'action ON DELETE de l'option CASCADE ne peut pas être définie si le déclencheur ON DELETE de l'option INSTEAD OF existe déjà dans la table en question.
 
Par exemple, dans la base de données Northwind, la table Orders possède une relation référentielle avec la table Customers. La clé étrangère Orders.CustomerID fait référence à la clé primaire Customers.CustomerID.
 
Si une instruction DELETE est exécutée sur une ligne dans la table Customers, et qu'une action ON DELETE CASCADE est spécifiée pour Orders.CustomerID, SQL Server vérifie la présence d'une ou plusieurs lignes dépendantes dans la table Orders. Si elle existe, la ligne dépendante dans la table Orders sera supprimée, ainsi que la ligne référencée dans la table Customers.
 
Par contre, si la valeur NO ACTION est spécifiée, SQL Server déclenche une erreur et annule la suppression de la ligne dans la table Customers si au moins une ligne y fait référence dans la table Orders.
 
ON UPDATE {CASCADE | NO ACTION}
 
Spécifie l'action qui se produit sur une ligne de la table modifiée, si cette ligne a une relation référentielle et que la ligne référencée est mise à jour dans la table parent. La valeur par défaut est NO ACTION.  
 
Si la valeur CASCADE est spécifiée, la ligne est mise à jour dans la table de référence si elle est mise à jour dans table parent. Si la valeur NO ACTION est spécifiée, SQL Server déclenche une erreur et la mise à jour de la ligne dans la table parent est annulée.
 
L'action CASCADE ON UPDATE ne peut pas être définie si un déclencheur INSTEAD OF ON UPDATE existe déjà dans la table en question.
 
Par exemple, dans la base de données Northwind, la table Orders possède une relation référentielle avec la table Customers. La clé étrangère Orders.CustomerID fait référence à la clé primaire Customers.CustomerID.
 
Si une instruction UPDATE est exécutée sur une ligne dans la table Customers, et qu'une action ON UPDATE CASCADE est spécifiée pour Orders.CustomerID, SQL Server vérifie la présence d'une ou plusieurs lignes dépendantes dans la table Orders. Si elle existe, la ligne dépendante dans la table Orders sera mise à jour, ainsi que la ligne référencée dans la table Customers.
 
D'autre part, si l'option NO ACTION est spécifiée, SQL Server génère une erreur et annule l'action de mise à jour de la ligne Customers s'il y a au moins une ligne dans la table Orders qui y fait référence.
 
[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 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ée 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 dans les 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.
 
column[,...n]
 
Colonne, ou liste de colonnes, entre parenthèses, utilisée dans une nouvelle contrainte.
 
constant_expression
 
Valeur littérale, valeur NULL ou fonction système utilisée comme valeur par défaut de la colonne.
 
FOR column
 
Spécifie la colonne associée à une définition DEFAULT au niveau de la table.
 
CHECK
 
Contrainte qui assure l'intégrité du domaine en limitant les valeurs possibles pouvant être entrées dans une ou plusieurs colonnes.
 
logical_expression
 
Expression logique utilisée dans une contrainte CHECK et qui renvoie la valeur TRUE ou FALSE. L'argument logical_expression, utilisé avec une contrainte CHECK ne peut pas référencer une autre table, mais peut référencer d'autres colonnes dans la même table, pour la même ligne.
 
Notes
Pour ajouter de nouvelles lignes de données, utilisez l'instruction INSERT. Pour supprimer des lignes de données, utilisez DELETE ou TRUNCATE TABLE. Pour modifier des valeurs dans les lignes existantes, utilisez UPDATE.
 
Les modifications spécifiées dans l'instruction ALTER TABLE sont mises en œuvre immédiatement. Si elles entraînent une modification des lignes de la table, ALTER TABLE met les lignes à jour. ALTER TABLE applique un verrou de modification du schéma afin qu'aucune autre connexion ne référence les métadonnées de la table durant la modification. Les modifications effectuées sur la table sont consignées dans un journal et peuvent être récupérées ultérieurement. Si les modifications, telles que la suppression d'une colonne ou l'ajout d'une colonne NOT NULL avec une valeur par défaut, affectent toutes les lignes d'une table de dimension importante, celles-ci peuvent nécessiter beaucoup de temps, tant pour s'exécuter que pour générer un grand nombre d'enregistrements dans le journal de transactions. Ces instructions ALTER TABLE doivent être exécutées avec le même soin que toute instruction INSERT, UPDATE ou DELETE affectant un grand nombre de lignes.
 
Si le cache de procédure contient des plans d'exécution référençant la table, ALTER TABLE les marque afin qu'ils soient recompilés lors de leur prochaine exécution.
 
Si l'instruction ALTER TABLE spécifie des changements au niveau des valeurs des colonnes référencées par d'autres tables, l'un ou l'autre des deux événements suivants se produit selon l'action spécifiée par ON UPDATE ou ON DELETE dans les tables de référence.  
 
Si aucune valeur n'est spécifiée (ou l'option NO ACTION par défaut) dans les tables de référence, une instruction ALTER TABLE sur la table parent qui génère une modification de la valeur de la colonne référencée par les autres tables sera annulée, et SQL Server génère une erreur.
 
 
Si l'option CASCADE est spécifiée dans les tables de référence, les modifications consécutives à une instruction ALTER TABLE dans la table parent sont appliquées à la table parent et à ses dépendants.  
Les instructions ALTER TABLE qui ajoutent une colonne sql_variant peuvent générer l'avertissement suivant :
 
The total row size (xx) for table 'yy' exceeds the maximum number of bytes per row (8060). Rows that exceed the maximum number of bytes will not be added.
Cet avertissement se produit car sql_variant peut compter jusqu'à 8016 octets. Lorsqu'une colonne sql_variant contient des valeurs proches de la longueur maximale, elle peut passer outre la taille maximale de la ligne.
 
Les restrictions applicables aux instructions ALTER TABLE dans les tables comportant des vues liées au schéma sont identiques à celles exercées lors de la modification de tables comportant un seul index. L'ajout d'une colonne est autorisé. Toutefois, la suppression ou la modification d'une colonne intervenant dans une vue associée à un schéma n'est pas autorisée. Si l'instruction ALTER TABLE requiert la modification d'une colonne utilisée dans une vue liée au schéma, l'action de modification échoue et SQL Server génère un message d'erreur. Pour plus d'informations sur la fonction SCHEMABINDING et les vues indexées, voir CREATE VIEW.
 
L'ajout ou la suppression de déclencheurs sur les tables de base n'est pas affectée par la création d'une vue liée au schéma comportant des références aux tables.
 
Tout index créé dans le cadre d'une contrainte est supprimé lorsque cette dernière est supprimée. Un index créé au moyen de l'instruction CREATE INDEX doit être supprimé à l'aide de l'instruction DROP INDEX. L'instruction DBCC DBREINDEX peut être utilisée pour reconstruire un index faisant partie de la définition d'une contrainte ; il n'est pas nécessaire de supprimer la contrainte et de l'ajouter de nouveau à l'aide de ALTER TABLE.
 
Tous les index et contraintes basés sur une colonne doivent être supprimés avant que la colonne ne puisse être supprimée.
 
Lorsque vous ajoutez des contraintes, toutes les données existantes sont vérifiées pour établir s'il y a violation des contraintes. Si tel est le cas, l'instruction ALTER TABLE échoue et une erreur est renvoyée.
 
Lorsqu'une nouvelle contrainte PRIMARY KEY ou UNIQUE est ajoutée à une colonne existante, les données de la ou des colonne(s) doivent être uniques. L'instruction ALTER TABLE échoue si des valeurs sont dupliqué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 249 index non ordonnés en clusters et 1 index ordonné en clusters.
 
Si une nouvelle colonne de type uniqueidentifier est ajoutée, sa valeur par défaut peut utiliser la fonction NEWID() pour générer des valeurs d'identificateur uniques dans la nouvelle colonne, pour chaque ligne existante de la table.
 
SQL Server 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.
 
La clause ALTER COLUMN de l'instruction ALTER TABLE ne lie ou ne dissocie aucune règle dans une colonne. Les règles doivent être liées ou dissociées séparément, à l'aide de sp_bindrule ou de sp_unbindrule.
 
Les règles peuvent être liées à un type de données défini par l'utilisateur. CREATE TABLE lie alors automatiquement la règle à toute colonne définie avec le type de données défini par l'utilisateur. ALTER COLUMN ne dissocie par la règle lorsque vous modifiez le type de données de la colonne. La règle du type de données défini par l'utilisateur d'origine reste liée à la colonne. Une fois que l'instruction ALTER COLUMN a modifié le type de données de la colonne, toute exécution ultérieure de sp_unbindrule dissociant la règle du type de données défini par l'utilisateur ne dissocie pas celle-ci de la colonne dont le type de données a été modifié. Si l'instruction ALTER COLUMN modifie le type de données d'une colonne en un type de données défini par l'utilisateur et lié à une règle, la règle liée au nouveau type de données n'est pas liée à la colonne.
 
Autorisations
Les autorisations ALTER TABLE reviennent par défaut au propriétaire de la table, aux membres du rôle de serveur fixe sysadmin et des rôles de base de données fixes db_owner et db_ddladmin, et ne sont pas transférables.
 
Exemple
A. Modification de table pour ajouter une colonne
L'exemple suivant ajoute une colonne qui accepte les valeurs NULL et pour laquelle aucune valeur n'est spécifiée via une définition DEFAULT. Chaque ligne aura une valeur NULL dans la nouvelle colonne.
 
CREATE TABLE doc_exa ( column_a INT)  
GO
ALTER TABLE doc_exa ADD column_b VARCHAR(20) NULL
GO
EXEC sp_help doc_exa
GO
DROP TABLE doc_exa
GO
 
B. Modification de table pour supprimer une colonne
L'exemple suivant supprime une colonne dans une table.
 
CREATE TABLE doc_exb ( column_a INT, column_b VARCHAR(20) NULL)  
GO
ALTER TABLE doc_exb DROP COLUMN column_b
GO
EXEC sp_help doc_exb
GO
DROP TABLE doc_exb
GO
 
C. Modification de table pour ajouter une colonne avec une contrainte
L'exemple suivant ajoute une nouvelle colonne avec une contrainte UNIQUE.  
 
CREATE TABLE doc_exc ( column_a INT)  
GO
ALTER TABLE doc_exc ADD column_b VARCHAR(20) NULL  
   CONSTRAINT exb_unique UNIQUE
GO
EXEC sp_help doc_exc
GO
DROP TABLE doc_exc
GO
 
A. Modification d'une table pour ajouter une contrainte non vérifiée
L'exemple suivant ajoute une contrainte à une colonne existante de la table. Une valeur de la colonne ne respecte pas la contrainte et l'option WITH NOCHECK est donc utilisée pour que la contrainte ne soit pas vérifiée dans les lignes existantes, et permettre d'ajouter cette contrainte.
 
CREATE TABLE doc_exd ( column_a INT)  
GO
INSERT INTO doc_exd VALUES (-1)
GO
ALTER TABLE doc_exd WITH NOCHECK  
ADD CONSTRAINT exd_check CHECK (column_a > 1)
GO
EXEC sp_help doc_exd
GO
DROP TABLE doc_exd
GO
 
E. Modification de table pour ajouter plusieurs colonnes avec contraintes
L'exemple suivant ajoute plusieurs colonnes avec des contraintes définies. La première colonne a une propriété IDENTITY ; chaque ligne de la table a de nouvelles valeurs incrémentielles dans la colonne d'identité.
 
CREATE TABLE doc_exe ( column_a INT CONSTRAINT column_a_un UNIQUE)  
GO
ALTER TABLE doc_exe ADD  
 
/* Add a PRIMARY KEY identity column. */  
column_b INT IDENTITY
CONSTRAINT column_b_pk PRIMARY KEY,  
 
/* Add a column referencing another column in the same table. */  
column_c INT NULL  
CONSTRAINT column_c_fk  
REFERENCES doc_exe(column_a),
 
/* Add a column with a constraint to enforce that   */  
/* nonnull data is in a valid phone number format.  */
column_d VARCHAR(16) NULL  
CONSTRAINT column_d_chk
CHECK  
(column_d IS NULL OR  
column_d LIKE "[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]" OR
column_d LIKE
"([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]" ),
 
/* Add a nonnull column with a default.  */  
column_e DECIMAL(3,3)
CONSTRAINT column_e_default
DEFAULT .081
GO
EXEC sp_help doc_exe
GO
DROP TABLE doc_exe
GO
 
F. Ajout d'une colonne acceptant les valeurs NULL et dotée de valeurs par défaut
L'exemple suivant ajoute une colonne acceptant les valeurs NULL et comportant une définition DEFAULT. Il utilise l'option WITH VALUES pour spécifier des valeurs pour chaque ligne existante de la table. Si l'option WITH VALUES n'est pas utilisée, chaque ligne de la nouvelle colonne a la valeur NULL.
 
ALTER TABLE MyTable  
ADD AddDate smalldatetime NULL
CONSTRAINT AddDateDflt
DEFAULT getdate() WITH VALUES
 
G. Désactivation et réactivation d'une contrainte
L'exemple suivant désactive la contrainte définissant les salaires pouvant être inclus dans les données. L'option WITH NOCHECK CONSTRAINT est utilisée avec ALTER TABLE pour désactiver la contrainte et permettre une insertion qui devrait normalement entraîner une violation de la contrainte. WITH CHECK CONSTRAINT réactive la contrainte.
 
CREATE TABLE cnst_example  
(id INT NOT NULL,
 name VARCHAR(10) NOT NULL,
 salary MONEY NOT NULL
    CONSTRAINT salary_cap CHECK (salary < 100000)
)
 
-- Valid inserts
INSERT INTO cnst_example VALUES (1,"Joe Brown",65000)
INSERT INTO cnst_example VALUES (2,"Mary Smith",75000)
 
-- This insert violates the constraint.
INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)
 
-- Disable the constraint and try again.
ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_cap
INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)
 
-- Reenable the constraint and try another insert, will fail.
ALTER TABLE cnst_example CHECK CONSTRAINT salary_cap
INSERT INTO cnst_example VALUES (4,"Eric James",110000)
 
H. Désactivation et réactivation d'un déclencheur
L'exemple suivant utilise l'option DISABLE TRIGGER de l'instruction ALTER TABLE pour désactiver le déclencheur et permettre une insertion qui devrait normalement entraîner une violation du déclencheur. Le déclencheur est ensuite réactivé à l'aide de l'option ENABLE TRIGGER.
 
CREATE TABLE trig_example  
(id INT,  
name VARCHAR(10),
salary MONEY)
go
-- Create the trigger.
CREATE TRIGGER trig1 ON trig_example FOR INSERT
as  
IF (SELECT COUNT(*) FROM INSERTED
WHERE salary > 100000) > 0
BEGIN
print "TRIG1 Error: you attempted to insert a salary > $100,000"
ROLLBACK TRANSACTION
END
GO
-- Attempt an insert that violates the trigger.
INSERT INTO trig_example VALUES (1,"Pat Smith",100001)
GO
-- Disable the trigger.
ALTER TABLE trig_example DISABLE TRIGGER trig1
GO
-- Attempt an insert that would normally violate the trigger
INSERT INTO trig_example VALUES (2,"Chuck Jones",100001)
GO
-- Re-enable the trigger.
ALTER TABLE trig_example ENABLE TRIGGER trig1
GO
-- Attempt an insert that violates the trigger.
INSERT INTO trig_example VALUES (3,"Mary Booth",100001)
GO
 
 
Voir aussi
 
DROP TABLE
 
sp_help
 
©1988-2000 Microsoft Corporation. Tous droits réservés.

select T.name tablename, P.name pkname
from sysobjects T, sysobjects P
where P.xtype = 'PK'
and T.xtype = 'U'
and T.id = P.parent_obj