Добавить столбец со значением по умолчанию в существующую таблицу в SQL Server

2987

Как я могу добавить столбец со значением по умолчанию в существующую таблицу в SQL Server 2000 / SQL Server 2005 ?

2
  • Как на этот вопрос 41 ответ? Просматривая его, большинство из них говорят то же самое. Почему они не удаляются? На этот вопрос нужно максимум 3 ответа. Michael Z. 15 мая и 22:00
  • ALTER TABLE SomeTable ADD SomeCol Bit NULL --ИЛИ НЕ NULL. CONSTRAINT D_SomeTable_SomeCol - при пропуске автоматически создается имя ограничения по умолчанию. DEFAULT (0) - необязательное ограничение по умолчанию. СО ЗНАЧЕНИЯМИ --Добавьте, если столбец имеет значение NULL и вам нужно значение по умолчанию для существующих записей. Ali NajafZadeh 3 августа в 17:06
3776
+50

Синтаксис:

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_SomeColSQL Server автоматически сгенерирует
    ограничение по умолчанию с забавным именем, например:DF__SomeTa__SomeC__4FB7FEF6

Дополнительное С-Values Заявление: только необходимо , когда ваша колонка обнуляемая     и вы хотите по умолчанию значения , используемым для существующих записей. Если ваш столбец равен , то он автоматически будет использовать значение     по умолчанию для всех существующих записей, независимо от того, укажете вы или нет.
WITH VALUES

NOT NULL
WITH VALUES

Как вставки работают с ограничением по умолчанию:
Если вы вставляете запись в значение SomeTableи не указываете SomeColзначение, то оно будет по умолчанию 0.
Если вы вставляете значение Record и Specify SomeColкак NULL(а ваш столбец допускает пустые значения),
    то ограничение по умолчанию не будет использоваться и NULLбудет вставлено как значение.

Примечания были основаны на замечательных отзывах всех, представленных ниже.
Особая благодарность:
    @Yatrix, @WalterStabosz, @YahooSerious и @StackMan за их комментарии.

12
  • 346
    Имейте в виду, что если столбец допускает значение NULL, то значение NULL будет использоваться для существующих строк. Richard Collette 31 янв.
  • 19
    @Thecrocodilehunter Столбец, допускающий значение NULL, означает, что вы можете вставить значение Null в качестве значения столбца. Если это не столбец, допускающий значение NULL, вам необходимо вставить какое-то значение этого типа данных. Таким образом, для существующих записей в них будет вставлен Null, а в новые записи будет вставлено ваше значение по умолчанию, если не указано иное. Есть смысл? Yatrix 29 фев '12 в 16:42
  • 43 год
    Мне этот ответ немного больше нравится, чем у dbugger, потому что он явно называет ограничение по умолчанию. Ограничение по умолчанию по-прежнему создается с использованием синтаксиса dbugger, за исключением того, что его имя генерируется автоматически. Знать точное имя удобно при написании скриптов DROP-CREATE. Walter Stabosz 23 марта '12 в 12:43
  • 20
    @Vertigo Это верно ТОЛЬКО, если столбец есть 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 значения NULL для столбца b. ErikE 14 июня '13 в 22: 192013-06-14 22:19
  • 51
    Используется WITH VALUESдля обновления существующих строк, допускающих значение NULL. См. MSDN : «Если добавленный столбец допускает нулевые значения и WITH VALUESуказан, значение по умолчанию сохраняется в новом столбце и добавляется к существующим строкам». Yahoo Serious 28 июля '14 в 12: 452014-07-28 12:45
1081
ALTER TABLE Protocols
ADD ProtocolTypeID int NOT NULL DEFAULT(1)
GO

Включение DEFAULT заполняет столбец в существующих строках значением по умолчанию, поэтому ограничение NOT NULL не нарушается.

8
  • 14
    Проблема с этим ответом в том, что значение по умолчанию действительно только для новых записей. Существующие записи по-прежнему будут иметь значение NULL. Roee Gavirel 9 нояб.
  • 135
    Вы обнаружите, что это не так. В противном случае ограничение будет нарушено. dbugger 9 ноя '11 в 16:50
  • 38
    Столбцы в существующих строках заполняются значением по умолчанию. Это докажет небольшой эмпирический тест. dbugger 9 нояб.
  • 89
    Просто чтобы уточнить - если в команде опущено «NOT NULL», значение для существующих строк НЕ будет обновлено и останется NULL. Если в команду включено "NOT NULL", значение существующих строк БУДЕТ обновлено, чтобы соответствовать значениям по умолчанию. Stack Man 14 авг.
  • 16
    Для нескольких столбцов ALTER TABLE table_1 ADD col_1 int NOT NULL DEFAULT (1), col_2 int NULLaads 4 сен '13 в 5:25
258

При добавлении столбца , допускающего значение NULL , WITH VALUESгарантирует, что конкретное значение DEFAULT применяется к существующим строкам:

ALTER TABLE table
ADD column BIT     -- Demonstration with NULL-able column added
CONSTRAINT Constraint_name DEFAULT 0 WITH VALUES
2
  • 14
    Это ключевой момент. Легко предположить, что столбец с DEFAULTограничением всегда будет иметь значение, то есть не иметь значение NULL, даже если NOT NULLоно не указано. Bill Brinkley 27 ноя '12 в 22: 292012-11-27 19:29
  • 7
    @tkocmathla, я не говорил о типе BITданных, я говорил об этом конкретном BIT столбце . Посмотрите ответ, столбец объявлен как NOT NULL. rsenna 20 авг.
147
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
1
  • 10
    Это не сработает, если в таблице уже есть содержимое, потому что новый столбец, не допускающий значения NULL, создается до ограничения значения по умолчаниюWDuffy 16 мая '16 в 14:16
136
ALTER TABLE MYTABLE ADD MYNEWCOLUMN VARCHAR(200) DEFAULT 'SNUGGLES'
2
  • 14
    это добавление null! должно быть не нулевым раньшеbaaroz 17 сен.
  • 6
    @baaroz, это работает с NOT NULL: ALTER TABLE MYTABLE ADD MYNEWCOLUMN VARCHAR (200) NOT NULL DEFAULT 'SNUGGLES'Shaiju T 22 фев '15 в 9:59
107

Самая простая версия только с двумя строками

ALTER TABLE MyTable
ADD MyNewColumn INT NOT NULL DEFAULT 0
102

Будьте осторожны, если столбец, который вы добавляете, имеет NOT NULLограничение, но не имеет DEFAULTограничения (значения). ALTER TABLEВыражение не будет в том случае , если в таблице есть строки в нем. Решение состоит в том, чтобы либо удалить NOT NULLограничение из нового столбца, либо предоставить DEFAULTдля него ограничение.

1
  • 4
    какой-нибудь образец SQL об этом? Kiquenet 23 авг.
90

Использовать:

-- Add a column with a default DateTime  
-- to capture when each record is added.

ALTER TABLE myTableName  
ADD RecordAddedDate SMALLDATETIME NULL DEFAULT (GETDATE())  
GO 
86

Если вы хотите добавить несколько столбцов, вы можете сделать это, например:

ALTER TABLE YourTable
    ADD Column1 INT NOT NULL DEFAULT 0,
        Column2 INT NOT NULL DEFAULT 1,
        Column3 VARCHAR(50) DEFAULT 'Hello'
GO
59

Использовать:

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}

Ссылка: ALTER TABLE (Transact-SQL) (MSDN)

58

Чтобы добавить столбец в существующую таблицу базы данных со значением по умолчанию, мы можем использовать:

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

Это два способа добавить столбец в существующую таблицу базы данных со значением по умолчанию.

55

Вы можете сделать это с помощью 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();''' ;
53

В SQL Server 2008-R2 я перехожу в режим проектирования - в тестовую базу данных - и добавляю два столбца с помощью конструктора и выполняю настройки с помощью графического интерфейса, а затем печально известный Right-Clickдает возможность « Создать сценарий изменения »!

На экране появится небольшое окошко с, как вы уже догадались, правильно отформатированным сценарием гарантированно работающего изменения. Нажмите простую кнопку.

52

В качестве альтернативы вы можете добавить значение по умолчанию без явного указания ограничения:

ALTER TABLE [schema].[tablename] ADD  DEFAULT ((0)) FOR [columnname]

Если при создании этого ограничения у вас возникла проблема с существующими ограничениями по умолчанию, их можно удалить:

alter table [schema].[tablename] drop constraint [constraintname]
1
  • 2
    К вашему сведению, явное указание ограничения с использованием стандартной схемы именования (например, «DF_Table_Column») для простоты обслуживания. В противном случае для поиска ограничения потребуется дополнительная работа. Mike Christian 22 ноя '17 в 0:15
49

Это также можно сделать в графическом интерфейсе SSMS. Я показываю дату по умолчанию ниже, но значение по умолчанию, конечно, может быть любым.

  1. Поместите свою таблицу в представление дизайна (щелкните правой кнопкой мыши таблицу в проводнике объектов-> Дизайн)
  2. Добавьте столбец в таблицу (или щелкните столбец, который вы хотите обновить, если он уже существует)
  3. В свойствах столбца ниже введите (getdate())или 'abc'или 0любое другое значение, которое вы хотите, в поле Значение по умолчанию или Привязка, как показано ниже:

введите описание изображения здесь

1
  • SSMS будет намного медленнее для больших таблиц, поскольку он будет генерировать скрипт для копирования всего во временную таблицу. ALTER TABLE будет мгновенным, даже если установить значение по умолчанию. Simon_Weaver 26 апр в 4:17
43 год
ALTER TABLE ADD ColumnName {Column_Type} Constraint

Статья MSDN ALTER TABLE (Transact-SQL) содержит весь синтаксис изменения таблицы.

0
35

Пример:

ALTER TABLE [Employees] ADD Seniority int not null default 0 GO
25

Пример:

ALTER TABLE tes 
ADD ssd  NUMBER   DEFAULT '0';
24

Сначала создайте таблицу с именем ученик:

CREATE TABLE STUDENT (STUDENT_ID INT NOT NULL)

Добавьте к нему один столбец:

ALTER TABLE STUDENT 
ADD STUDENT_NAME INT NOT NULL DEFAULT(0)

SELECT * 
FROM STUDENT

Таблица создается, и в существующую таблицу добавляется столбец со значением по умолчанию.

Изображение 1

22

Здесь много ответов, но я чувствую необходимость добавить этот расширенный метод. Кажется, что это намного больше, но это чрезвычайно полезно, если вы добавляете поле 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

Что это будет делать, так это добавить столбец как поле, допускающее значение NULL, и со значением по умолчанию, обновить все поля до значения по умолчанию (или вы можете назначить более значимые значения), и, наконец, он изменит столбец на NOT NULL.

Причина этого в том, что если вы обновляете крупномасштабную таблицу и добавляете новое ненулевое поле, которое оно должно записывать в каждую отдельную строку, и тем самым блокирует всю таблицу, поскольку она добавляет столбец, а затем записывает все значения.

Этот метод добавит столбец, допускающий значение NULL, который работает намного быстрее сам по себе, а затем заполнит данные перед установкой состояния not NULL.

Я обнаружил, что выполнение всего этого в одном операторе блокирует одну из наших наиболее активных таблиц на 4-8 минут, и довольно часто я убивал процесс. Этот метод для каждой части обычно занимает всего несколько секунд и вызывает минимальную блокировку.

Кроме того, если у вас есть таблица, состоящая из миллиардов строк, возможно, стоит выполнить пакетное обновление следующим образом:

WHILE 1=1
BEGIN
    UPDATE TOP (1000000) {schemaName}.{tableName}
        SET {columnName} = {DefaultValue}
        WHERE {columName} IS NULL

    IF @@ROWCOUNT < 1000000
        BREAK;
END
21

Попробуй это

ALTER TABLE Product
ADD ProductID INT NOT NULL DEFAULT(1)
GO
20

SQL Server + Изменить таблицу + Добавить столбец + Уникальный идентификатор значения по умолчанию

ALTER TABLE Product 
ADD ReferenceID uniqueidentifier not null 
default (cast(cast(0 as binary) as uniqueidentifier))
20
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
1
  • 4
    Мне нравится NOT EXISTSчек перед попыткой изменить таблицу. Очень хорошее решение. Некоторые дополнительные комментарии о том, как это работает, сделали бы его еще более полезным. Michael Gaskill 21 июня '16 в 20:14
20
--Adding Value with Default Value
ALTER TABLE TestTable
ADD ThirdCol INT NOT NULL DEFAULT(0)
GO
2
  • Может быть, заменить изображение реальным кодом? Трудно скопировать и вставить как есть. David Faber 20 марта '18 в 16: 062018-03-20 16:06
  • 8
    Это не добавляет ценности по сравнению с уже существующими ответами много лет назад. nvoigt 15 апр '18 в 8: 012018-04-15 8:01
19

Это для SQL Server:

ALTER TABLE TableName
ADD ColumnName (type) -- NULL OR NOT NULL
DEFAULT (default value)
WITH VALUES

Пример:

ALTER TABLE Activities
ADD status int NOT NULL DEFAULT (0)
WITH VALUES

Если вы хотите добавить ограничения, то:

ALTER TABLE Table_1
ADD row3 int NOT NULL
CONSTRAINT CONSTRAINT_NAME DEFAULT (0)
WITH VALUES
1
  • 9
    Это не добавляет ценности по сравнению с уже существующими ответами много лет назад. nvoigt 15 апр '18 в 8: 012018-04-15 8:01
16

Добавьте в таблицу новый столбец:

ALTER TABLE [table]
ADD Column1 Datatype

Например,

ALTER TABLE [test]
ADD ID Int

Если пользователь хочет, чтобы он увеличивался автоматически, тогда:

ALTER TABLE [test]
ADD ID Int IDENTITY(1,1) NOT NULL
12

Это можно сделать с помощью приведенного ниже кода.

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
12

Попробуйте выполнить следующий запрос:

ALTER TABLE MyTable
ADD MyNewColumn DataType DEFAULT DefaultValue

Это добавит новый столбец в таблицу.

1
  • 7
    этот ответ, данный другим пользователем на этот вопросjanith1024 28 авг.
10
ALTER TABLE tbl_table ADD int_column int NOT NULL DEFAULT(0)

Из этого запроса вы можете добавить столбец целочисленного типа данных со значением по умолчанию 0.

10

Что ж, теперь у меня есть некоторые изменения в моем предыдущем ответе. Я заметил, что ни один из ответов не упомянут 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]