Home >Database >Mysql Tutorial >How to Add a Column with a Default Value in SQL Server?

How to Add a Column with a Default Value in SQL Server?

DDD
DDDOriginal
2025-01-18 09:31:09954browse

How to Add a Column with a Default Value in SQL Server?

Add column with default value in SQL Server

Adding columns to an existing table is a simple task in SQL Server. However, when you need to add a column with a default value, the syntax can get a little more complicated.

To add a column with a default value, you can use the following syntax:

<code class="language-sql">ALTER TABLE {表名}
ADD {列名} {数据类型} {NULL|NOT NULL}
CONSTRAINT {约束名} DEFAULT {默认值}
WITH VALUES</code>

Example:

<code class="language-sql">ALTER TABLE SomeTable
ADD SomeCol Bit NULL -- 或 NOT NULL
CONSTRAINT D_SomeTable_SomeCol -- 省略时,将自动生成默认约束名
DEFAULT (0) -- 可选的默认约束
WITH VALUES -- 如果列可为空并且您希望为现有记录使用默认值,则添加此语句</code>

Instructions:

  • Optional constraint name: If the CONSTRAINT name is omitted, SQL Server will automatically generate a default constraint name.
  • Optional WITH VALUES statement: This statement is only required if your column is nullable and you want to use default values ​​for existing records. If your column is NOT NULL, it will automatically use the default value for all existing records, regardless of whether you specify WITH VALUES.
  • Insert operation with default constraints: If you insert a record into SomeTable and do not specify a value for SomeCol, it will default to 0. However, if you insert a record and specify the value of SomeCol as NULL (and your column allows null values), the default constraint will not be used and NULL will be inserted as the value.

The above is the detailed content of How to Add a Column with a Default Value in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn