remplir - Ajouter une colonne avec une valeur par défaut à une table existante dans SQL Server
valeur par défaut sql server (20)
Comment une colonne avec une valeur par défaut peut-elle être ajoutée à une table existante dans SQL Server 2000 / SQL Server 2005 ?
Syntaxe:
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES
Exemple:
ALTER TABLE SomeTable
ADD SomeCol Bit NULL --Or NOT NULL.
CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.
Remarques:
Nom de contrainte facultatif:
Si vous laissez CONSTRAINT D_SomeTable_SomeCol
alors SQL Server se créera automatiquement
une DF__SomeTa__SomeC__4FB7FEF6
par défaut avec un nom amusant comme: DF__SomeTa__SomeC__4FB7FEF6
Déclaration facultative avec les valeurs:
WITH VALUES
n’est nécessaire que lorsque votre colonne est Nullable
et vous voulez que la valeur par défaut soit utilisée pour les enregistrements existants.
Si votre colonne n'est NOT NULL
, elle utilisera automatiquement la valeur par défaut
pour tous les enregistrements existants, que vous spécifiiez WITH VALUES
ou non.
Comment fonctionnent les insertions avec une contrainte par défaut:
Si vous insérez un enregistrement dans SomeTable
sans spécifier la valeur de SomeCol
la valeur par défaut sera 0
.
Si vous insérez un enregistrement et spécifiez la valeur de SomeCol
tant que NULL
(et que votre colonne autorise les valeurs nulles),
alors la contrainte par défaut ne sera pas utilisée et NULL
sera inséré en tant que valeur.
Les notes étaient basées sur les excellents commentaires de tous ci-dessous.
Un merci spécial à:
@Yatrix, @WalterStabosz, @YahooSerious et @StackMan pour leurs commentaires.
Ajouter une nouvelle colonne à une table:
ALTER TABLE [table]
ADD Column1 Datatype
Par exemple,
ALTER TABLE [test]
ADD ID Int
Si l'utilisateur veut l'incrémenter automatiquement, alors:
ALTER TABLE [test]
ADD ID Int IDENTITY(1,1) NOT NULL
Cela peut également être effectué dans l'interface graphique SSMS. Je montre une date par défaut ci-dessous, mais la valeur par défaut peut être quoi que, bien sûr.
- Mettez votre table en mode Création (clic droit sur la table dans l'explorateur d'objets-> Conception)
- Ajouter une colonne à la table (ou cliquer sur la colonne que vous souhaitez mettre à jour si elle existe déjà)
- Dans les propriétés de colonne ci-dessous, entrez
(getdate())
abc
ou0
ou la valeur de votre(getdate())
dans le champ Valeur par défaut ou Liaison , comme illustré ci-dessous:
Cela peut être fait par le code ci-dessous.
CREATE TABLE TestTable
(FirstCol INT NOT NULL)
GO
------------------------------
-- Option 1
------------------------------
-- Adding New Column
ALTER TABLE TestTable
ADD SecondCol INT
GO
-- Updating it with Default
UPDATE TestTable
SET SecondCol = 0
GO
-- Alter
ALTER TABLE TestTable
ALTER COLUMN SecondCol INT NOT NULL
GO
Eh bien, j'ai maintenant quelques modifications à ma réponse précédente. J'ai remarqué qu'aucune des réponses mentionnées IF NOT EXISTS
. Je vais donc proposer une nouvelle solution car j'ai eu du mal à modifier le tableau.
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.columns WHERE table_name = 'TaskSheet' AND column_name = 'IsBilledToClient')
BEGIN
ALTER TABLE dbo.TaskSheet ADD
IsBilledToClient bit NOT NULL DEFAULT ((1))
END
GO
Ici TaskSheet
est le nom de la table particulière et IsBilledToClient
est la nouvelle colonne que vous allez insérer et 1
la valeur par défaut. Cela signifie dans la nouvelle colonne quelle sera la valeur des lignes existantes, donc une sera définie automatiquement ici. Cependant, vous pouvez changer à votre guise avec le respect du type de colonne comme j'ai utilisé BIT
, donc je mets en valeur par défaut 1.
Je suggère le système ci-dessus, car j'ai rencontré un problème. Alors quel est le problème? Le problème est que si la colonne IsBilledToClient
existe dans la table, si vous n'exécutez que la partie du code indiquée ci-dessous, vous verrez une erreur dans le générateur Requête du serveur SQL. Mais s'il n'existe pas, pour la première fois, il n'y aura pas d'erreur lors de l'exécution.
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
[WITH VALUES]
Essaye ça
ALTER TABLE Product
ADD ProductID INT NOT NULL DEFAULT(1)
GO
Exemple:
ALTER TABLE tes
ADD ssd NUMBER DEFAULT '0';
Faites attention lorsque la colonne que vous ajoutez a une contrainte NOT NULL
sans pour DEFAULT
avoir une contrainte DEFAULT
(valeur). L'instruction ALTER TABLE
échouera dans ce cas si la table contient des lignes. La solution consiste à supprimer la contrainte NOT NULL
de la nouvelle colonne ou à lui fournir une contrainte DEFAULT
.
Lors de l'ajout d'une colonne Nullable , WITH VALUES
s'assurera que la valeur DEFAULT spécifique est appliquée aux lignes existantes:
ALTER TABLE table
ADD column BIT -- Demonstration with NULL-able column added
CONSTRAINT Constraint_name DEFAULT 0 WITH VALUES
Pour ajouter une colonne à une table de base de données existante avec une valeur par défaut, nous pouvons utiliser:
ALTER TABLE [dbo.table_name]
ADD [Column_Name] BIT NOT NULL
Default ( 0 )
Voici un autre moyen d'ajouter une colonne à une table de base de données existante avec une valeur par défaut.
Vous trouverez ci-dessous un script SQL beaucoup plus complet pour ajouter une colonne avec une valeur par défaut. Il vérifie notamment si la colonne existe avant de l'ajouter, vérifie également la contrainte et la supprime s'il en existe une. Ce script nomme également la contrainte afin que nous puissions avoir une belle convention de nommage (j'aime bien DF_) et si SQL ne nous donne pas une contrainte avec un nom qui a un numéro généré aléatoirement; il est donc agréable de pouvoir nommer la contrainte aussi.
-------------------------------------------------------------------------
-- Drop COLUMN
-- Name of Column: Column_EmployeeName
-- Name of Table: table_Emplyee
--------------------------------------------------------------------------
IF EXISTS (
SELECT 1
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table_Emplyee'
AND COLUMN_NAME = 'Column_EmployeeName'
)
BEGIN
IF EXISTS ( SELECT 1
FROM sys.default_constraints
WHERE object_id = OBJECT_ID('[dbo].[DF_table_Emplyee_Column_EmployeeName]')
AND parent_object_id = OBJECT_ID('[dbo].[table_Emplyee]')
)
BEGIN
------ DROP Contraint
ALTER TABLE [dbo].[table_Emplyee] DROP CONSTRAINT [DF_table_Emplyee_Column_EmployeeName]
PRINT '[DF_table_Emplyee_Column_EmployeeName] was dropped'
END
-- ----- DROP Column -----------------------------------------------------------------
ALTER TABLE [dbo].table_Emplyee
DROP COLUMN Column_EmployeeName
PRINT 'Column Column_EmployeeName in images table was dropped'
END
--------------------------------------------------------------------------
-- ADD COLUMN Column_EmployeeName IN table_Emplyee table
--------------------------------------------------------------------------
IF NOT EXISTS (
SELECT 1
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table_Emplyee'
AND COLUMN_NAME = 'Column_EmployeeName'
)
BEGIN
----- ADD Column & Contraint
ALTER TABLE dbo.table_Emplyee
ADD Column_EmployeeName BIT NOT NULL
CONSTRAINT [DF_table_Emplyee_Column_EmployeeName] DEFAULT (0)
PRINT 'Column [DF_table_Emplyee_Column_EmployeeName] in table_Emplyee table was Added'
PRINT 'Contraint [DF_table_Emplyee_Column_EmployeeName] was Added'
END
GO
Ce sont deux façons d'ajouter une colonne à une table de base de données existante avec une valeur par défaut.
SQL Server + Table de modification + Ajouter une colonne + Valeur par défaut uniqueidentifier
ALTER TABLE Product
ADD ReferenceID uniqueidentifier not null
default (cast(cast(0 as binary) as uniqueidentifier))
Si la valeur par défaut est Null, alors:
- Dans SQL Server, ouvrez l'arborescence de la table ciblée.
- Clic droit sur "Colonnes" ==>
New Column
- Tapez la colonne Nom,
Select Type
et cochez la case Autoriser les valeurs nulles. - Dans la barre de menus, cliquez sur
Save
Terminé!
Utilisation:
-- Add a column with a default DateTime
-- to capture when each record is added.
ALTER TABLE myTableName
ADD RecordAddedDate smalldatetime NULL DEFAULT(GetDate())
GO
Utilisation:
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
Référence: ALTER TABLE (Transact-SQL) (MSDN)
Vous pouvez faire la chose avec T-SQL de la manière suivante.
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
Vous pouvez également utiliser SQL Server Management Studio en cliquant avec le bouton droit de la souris sur table dans le menu Conception, en définissant la valeur par défaut sur table.
De plus, si vous voulez ajouter la même colonne (si elle n’existe pas) à toutes les tables de la base de données, utilisez:
USE AdventureWorks;
EXEC sp_msforeachtable
'PRINT ''ALTER TABLE ? ADD Date_Created DATETIME DEFAULT GETDATE();''' ;
Commencez par créer une table avec le nom étudiant:
CREATE TABLE STUDENT (STUDENT_ID INT NOT NULL)
Ajoutez-y une colonne:
ALTER TABLE STUDENT
ADD STUDENT_NAME INT NOT NULL DEFAULT(0)
SELECT *
FROM STUDENT
La table est créée et une colonne est ajoutée à une table existante avec une valeur par défaut.
ALTER TABLE <table name>
ADD <new column name> <data type> NOT NULL
GO
ALTER TABLE <table name>
ADD CONSTRAINT <constraint name> DEFAULT <default value> FOR <new column name>
GO
ALTER TABLE ADD ColumnName {Column_Type} Constraint
L'article MSDN ALTER TABLE (Transact-SQL) présente la syntaxe alter table.
ALTER TABLE Protocols
ADD ProtocolTypeID int NOT NULL DEFAULT(1)
GO
L'inclusion de DEFAULT remplit la colonne dans les lignes existantes avec la valeur par défaut, de sorte que la contrainte NOT NULL n'est pas violée.
ALTER TABLE tbl_table ADD int_column int NOT NULL DEFAULT(0)
À partir de cette requête, vous pouvez ajouter une colonne de type type entier avec la valeur par défaut 0.