Как добавить столбец со значением по умолчанию в существующую таблицу в SQL Server 2000 / SQL Server 2005 ?
Синтаксис:
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES
Пример:
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.
Заметки:
Имя необязательного ограничения:
если вы не укажете, CONSTRAINT D_SomeTable_SomeCol
SQL Server автоматически сгенерирует
ограничение по умолчанию с забавным именем, например:DF__SomeTa__SomeC__4FB7FEF6
Дополнительное С-Values Заявление: только необходимо , когда ваша колонка обнуляемая
и вы хотите по умолчанию значения , используемым для существующих записей.
Если ваш столбец есть , он будет автоматически использовать значение
по умолчанию для всех существующих записей, независимо от того, указали вы это или нет.WITH VALUES
NOT NULL
WITH VALUES
Как вставки работают с ограничением по умолчанию:
если вы вставляете запись SomeTable
и не указываете SomeCol
значение, то по умолчанию оно будет 0
.
Если вы вставите значение «Запись и указать SomeCol
» как NULL
(а ваш столбец допускает пустые значения),
то ограничение по умолчанию не будет использоваться и NULL
будет вставлено как значение.
Примечания были основаны на всех замечательных отзывах ниже.
Особая благодарность:
@Yatrix, @WalterStabosz, @YahooSerious и @StackMan за их комментарии.
NOT NULL
. Пожалуйста, попробуйте это: create table blah(a int not null primary key clustered); insert blah values (1), (2); alter table blah add b int null constraint df_blah_b default (0); select * from blah;
вы увидите 2 пустых значения для столбца b
. WITH VALUES
для обновления существующих обнуляемых строк. См. MSDN : «Если добавленный столбец допускает нулевые значения и WITH VALUES
указан, значение по умолчанию сохраняется в новом столбце, добавляется в существующие строки». ALTER TABLE ADD ColumnName {Column_Type} Constraint
В статье MSDN ALTER TABLE (Transact-SQL) представлен весь синтаксис таблицы изменений.
ALTER TABLE Protocols
ADD ProtocolTypeID int NOT NULL DEFAULT(1)
GO
Включение DEFAULT заполняет столбец в существующих строках значением по умолчанию, поэтому ограничение NOT NULL не нарушается.
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
Остерегайтесь, когда столбец, который вы добавляете, имеет NOT NULL
ограничение, но не имеет DEFAULT
ограничения (значения). Оператор ALTER TABLE
потерпит неудачу в том случае, если в таблице есть какие-либо строки. Решение состоит в том, чтобы либо удалить NOT NULL
ограничение из нового столбца, либо предоставить DEFAULT
для него ограничение.
Использование:
-- Add a column with a default DateTime
-- to capture when each record is added.
ALTER TABLE myTableName
ADD RecordAddedDate SMALLDATETIME NULL DEFAULT (GETDATE())
GO
При добавлении обнуляемого столбца , WITH VALUES
будет гарантировать , что значение конкретных умолчанию применяются к существующим строкам:
ALTER TABLE table
ADD column BIT -- Demonstration with NULL-able column added
CONSTRAINT Constraint_name DEFAULT 0 WITH VALUES
DEFAULT
ограничением всегда будет иметь значение, то есть не будет NULL, даже если NOT NULL
он не указан. BIT
данных, я говорил об этой конкретной BIT
колонке . Посмотрите на ответ, столбец объявлен как NOT NULL
. Вы можете сделать это с T-SQL следующим образом.
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
Кроме того, вы можете использовать SQL Server Management Studio также, щелкнув правой кнопкой мыши по таблице в меню «Дизайн», установив значение по умолчанию для таблицы.
И более того, если вы хотите добавить один и тот же столбец (если он не существует) ко всем таблицам в базе данных, используйте:
USE AdventureWorks;
EXEC sp_msforeachtable
'PRINT ''ALTER TABLE ? ADD Date_Created DATETIME DEFAULT GETDATE();''' ;
В SQL Server 2008-R2 я перехожу в режим разработки - в тестовую базу данных - и добавляю свои два столбца с помощью конструктора и выполняю настройки с помощью графического интерфейса, а затем печально известный Right-Clickдает опцию « Создать сценарий изменения »!
Появится всплывающее окно с небольшим, как вы уже догадались, надлежащим образом отформатированным скриптом с гарантированными изменениями. Нажмите легкую кнопку.
Использование:
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
Ссылка: ALTER TABLE (Transact-SQL) (MSDN)
Самая простая версия с двумя строками
ALTER TABLE MyTable
ADD MyNewColumn INT NOT NULL DEFAULT 0
Кроме того, вы можете добавить значение по умолчанию, не называя ограничение явно:
ALTER TABLE [schema].[tablename] ADD DEFAULT ((0)) FOR [columnname]
Если у вас есть проблема с существующими ограничениями по умолчанию при создании этого ограничения, то их можно удалить:
alter table [schema].[tablename] drop constraint [constraintname]
ALTER TABLE MYTABLE ADD MYNEWCOLUMN VARCHAR(200) DEFAULT 'SNUGGLES'
Пример:
ALTER TABLE [Employees] ADD Seniority int not null default 0 GO
Чтобы добавить столбец в существующую таблицу базы данных со значением по умолчанию, мы можем использовать:
ALTER TABLE [dbo.table_name]
ADD [Column_Name] BIT NOT NULL
Default ( 0 )
Вот еще один способ добавить столбец в существующую таблицу базы данных со значением по умолчанию.
Ниже приведен гораздо более подробный сценарий SQL для добавления столбца со значением по умолчанию, включая проверку существования столбца перед его добавлением, а также проверку ограничения и удаление его, если оно есть. Этот сценарий также называет ограничение, чтобы у нас было хорошее соглашение об именах (мне нравится DF_), и если нет, SQL даст нам ограничение с именем, которое имеет случайно сгенерированный номер; так что приятно иметь возможность назвать ограничение тоже.
-------------------------------------------------------------------------
-- 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
Это два способа добавить столбец в существующую таблицу базы данных со значением по умолчанию.
Попробуй это
ALTER TABLE Product
ADD ProductID INT NOT NULL DEFAULT(1)
GO
Если вы хотите добавить несколько столбцов, вы можете сделать это следующим образом:
ALTER TABLE YourTable
ADD Column1 INT NOT NULL DEFAULT 0,
Column2 INT NOT NULL DEFAULT 1,
Column3 VARCHAR(50) DEFAULT 'Hello'
GO
Пример:
ALTER TABLE tes
ADD ssd NUMBER DEFAULT '0';
SQL Server + Изменить таблицу + Добавить столбец + Значение по умолчанию uniqueidentifier
ALTER TABLE Product
ADD ReferenceID uniqueidentifier not null
default (cast(cast(0 as binary) as uniqueidentifier))
SQL Server + Изменить таблицу + Добавить столбец + Значение по умолчанию uniqueidentifier ...
ALTER TABLE [TABLENAME] ADD MyNewColumn INT not null default 0 GO
Это можно сделать и в графическом интерфейсе SSMS. Я показываю дату по умолчанию ниже, но значение по умолчанию может быть любым, конечно.
- Переведите вашу таблицу в режим конструктора (щелкните правой кнопкой мыши по таблице в проводнике объектов-> Дизайн)
- Добавьте столбец в таблицу (или щелкните столбец, который вы хотите обновить, если он уже существует)
- В свойствах столбца ниже введите
(getdate())
илиabc
или0
любое другое значение в поле « Значение по умолчанию» или «Связывание», как показано ниже:
Добавьте новый столбец в таблицу:
ALTER TABLE [table]
ADD Column1 Datatype
Например,
ALTER TABLE [test]
ADD ID Int
Если пользователь хочет увеличить его автоматически, тогда:
ALTER TABLE [test]
ADD ID Int IDENTITY(1,1) NOT NULL
IF NOT EXISTS (
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME ='TABLENAME' AND COLUMN_NAME = 'COLUMNNAME'
)
BEGIN
ALTER TABLE TABLENAME ADD COLUMNNAME Nvarchar(MAX) Not Null default
END
NOT EXISTS
чек, прежде чем пытаться изменить таблицу. Очень хорошее решение Некоторые дополнительные комментарии о том, как это работает, сделают его еще более полезным. Если по умолчанию установлено значение Null, то:
- В SQL Server откройте дерево целевой таблицы
- Щелкните правой кнопкой мыши «Колонны» ==>
New Column
- Введите имя столбца
Select Type
и установите флажок Разрешить пустые - В строке меню нажмите
Save
Выполнено!
ALTER TABLE tbl_table ADD int_column int NOT NULL DEFAULT(0)
Из этого запроса вы можете добавить столбец целого типа данных со значением по умолчанию 0.
Это можно сделать с помощью приведенного ниже кода.
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
Ну, теперь у меня есть некоторые изменения в моем предыдущем ответе. Я заметил, что ни один из ответов не упоминается IF NOT EXISTS
. Поэтому я собираюсь предложить новое решение, поскольку столкнулся с некоторыми проблемами при изменении таблицы.
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
Вот TaskSheet
конкретное имя таблицы и IsBilledToClient
новый столбец, который вы собираетесь вставить, и 1
значение по умолчанию. Это означает, что в новом столбце будет значение существующих строк, поэтому один из них будет установлен там автоматически. Тем не менее, вы можете изменить, как пожелаете, с учетом типа столбца, который я использовал BIT
, поэтому я установил значение по умолчанию 1.
Я предлагаю вышеупомянутую систему, потому что я столкнулся с проблемой. Так в чем проблема? Проблема в том, что если IsBilledToClient
столбец существует в таблице, то, если вы выполните только часть кода, приведенную ниже, вы увидите ошибку в построителе запросов SQL-сервера. Но если он не существует, то в первый раз не будет ошибки при выполнении.
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
[WITH VALUES]
Вы можете использовать этот запрос:
ALTER TABLE tableName ADD ColumnName datatype DEFAULT DefaultValue;
На это есть много ответов, но я чувствую необходимость добавить этот расширенный метод. Это кажется намного дольше, но это чрезвычайно полезно, если вы добавляете поле NOT NULL в таблицу с миллионами строк в активной базе данных.
ALTER TABLE {schemaName}.{tableName}
ADD {columnName} {datatype} NULL
CONSTRAINT {constraintName} DEFAULT {DefaultValue}
UPDATE {schemaName}.{tableName}
SET {columnName} = {DefaultValue}
WHERE {columName} IS NULL
ALTER TABLE {schemaName}.{tableName}
ALTER COLUMN {columnName} {datatype} NOT NULL
Для этого нужно добавить столбец как пустое поле со значением по умолчанию, обновить все поля до значения по умолчанию (или вы можете назначить более значимые значения), и, наконец, столбец будет иметь значение NOT NULL.
Причина этого заключается в том, что если вы обновляете крупномасштабную таблицу и добавляете новое ненулевое поле, оно должно записываться в каждую строку и тем самым блокирует всю таблицу по мере добавления столбца, а затем записывает все значения.
Этот метод добавляет столбец NULL, который работает намного быстрее сам по себе, а затем заполняет данные перед установкой ненулевого статуса.
Я обнаружил, что выполнение всего этого за одно утверждение блокирует одну из наших более активных таблиц на 4-8 минут, и довольно часто я убиваю процесс. Этот метод каждой части обычно занимает всего несколько секунд и вызывает минимальную блокировку.
Кроме того, если у вас есть таблица размером в миллиарды строк, возможно, стоит обновить пакет следующим образом:
WHILE 1=1
BEGIN
UPDATE TOP (1000000) {schemaName}.{tableName}
SET {columnName} = {DefaultValue}
WHERE {columName} IS NULL
IF @@ROWCOUNT < 1000000
BREAK;
END