ALTER TABLE test ALTER COLUMN Col1 NCHAR(10) NOT NULL add CONSTRAINT DF_test_Col1 DEFAULT (1) FOR col1Incorrect syntax near the keyword 'CONSTRAINT'.
/* A. 添加新列 以下示例将添加一个允许空值的列,而且没有通过 DEFAULT 定义提供的值。 在该新列中,每一行都将有 NULL 值。 */ 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. 删除列 以下示例将修改一个表以删除列。 */ 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. 更改列的数据类型 以下示例将表中列的数据类型由 INT 更改为 DECIMAL。 */ CREATE TABLE doc_exy ( column_a INT ) ; GO INSERT INTO doc_exy (column_a) VALUES (10) ; GO ALTER TABLE doc_exy ALTER COLUMN column_a DECIMAL (5, 2) ; GO DROP TABLE doc_exy ; GO
/* D. 添加包含约束的列 以下示例将添加一个包含 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
/* E. 在现有列中添加一个未经验证的 CHECK 约束 以下示例将在表中的现有列中添加一个约束。该列包含一个违反约束的值。 因此,将使用 WITH NOCHECK 以避免根据现有行验证该约束,从而允许添加该约束。 */ 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
/* F. 在现有列中添加一个 DEFAULT 约束 以下示例将创建一个包含两列的表,在第一列插入一个值, 另一列保持为 NULL。然后在第二列中添加一个 DEFAULT 约束。 验证是否已应用了默认值,另一个值是否已插入第一列以及是否已查询表。 */CREATE TABLE doc_exz ( column_a INT, column_b INT) ; GO INSERT INTO doc_exz (column_a) VALUES ( 7 ) ; GO ALTER TABLE doc_exz ADD CONSTRAINT col_b_def DEFAULT 50 FOR column_b ; GO INSERT INTO doc_exz (column_a) VALUES ( 10 ) ; GO SELECT * FROM doc_exz ; GO DROP TABLE doc_exz ; GO /* G. 添加多个包含约束的列 以下示例将添加多个包含随新列定义的约束的列。 第一个新列具有 IDENTITY 属性。表中的每一行在标识列中都有新的增量值。 */ 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 that references 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 telephone number format. column_d VARCHAR(16) NULL CONSTRAINT column_d_chk CHECK (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/* H. 添加包含默认值的可为空的列 以下示例将添加一个包含 DEFAULT 定义的可为空的列, 并使用 WITH VALUES 为表中的各个现有行提供值。 如果没有使用 WITH VALUES,那么每一行的新列中都将具包含 NULL 值。 */ Use AdventureWorks ; GO CREATE TABLE doc_exf ( column_a INT) ; GO INSERT INTO doc_exf VALUES (1) ; GO ALTER TABLE doc_exf ADD AddDate smalldatetime NULL CONSTRAINT AddDateDflt DEFAULT GETDATE() WITH VALUES ; GO DROP TABLE doc_exf ; GO
1、创建表时指定标识列 标识列可用 IDENTITY 属性建立,因此在SQL Server中,又称标识列为具有IDENTITY属性的列或IDENTITY列。 下面的例子创建一个包含名为ID,类型为int,种子为1,递增量为1的标识列 CREATE TABLE T_test (ID int IDENTITY(1,1), Name varchar(50) )2、在现有表中添加标识列 下面的例子向表T_test中添加一个名为ID,类型为 int,种子为1,递增量为1的标识列 --创建表 CREATE TABLE T_test (Name varchar(50) )--插入数据 INSERT T_test(Name) VALUES('张三')--增加标识列 ALTER TABLE T_test ADD ID int IDENTITY(1,1)3、判段一个表是否具有标识列可以使用 OBJECTPROPERTY 函数确定一个表是否具有 IDENTITY(标识)列,用法: Select OBJECTPROPERTY(OBJECT_ID('表名'),'TableHasIdentity') 如果有,则返回1,否则返回04、判断某列是否是标识列可使用 COLUMNPROPERTY 函数确定 某列是否具有IDENTITY 属性,用法 SELECT COLUMNPROPERTY( OBJECT_ID('表名'),'列名','IsIdentity') 如果该列为标识列,则返回1,否则返回04、查询某表标识列的列名 SQL Server中没有现成的函数实现此功能,实现的SQL语句如下 SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME='表名' AND COLUMNPROPERTY( OBJECT_ID('表名'),COLUMN_NAME,'IsIdentity')=15、标识列的引用如果在SQL语句中引用标识列,可用关键字IDENTITYCOL代替 例如,若要查询上例中ID等于1的行, 以下两条查询语句是等价的 SELECT * FROM T_test WHERE IDENTITYCOL=1 SELECT * FROM T_test WHERE ID=16、获取标识列的种子值可使用函数IDENT_SEED,用法: SELECT IDENT_SEED ('表名')7、获取标识列的递增量可使用函数 IDENT_INCR ,用法: SELECT IDENT_INCR('表名')8、获取指定表中最后生成的标识值可使用函数IDENT_CURRENT,用法: SELECT IDENT_CURRENT('表名') 注意事项:当包含标识列的表刚刚创建,为经过任何插入操作时,使用IDENT_CURRENT函数得到的值为标识列的种子值,这一点在开发数据库应用程序的时候尤其应该注意。
ALTER TABLE table_name
{ [ ALTER COLUMN column_name
{DROP DEFAULT
| SET DEFAULT constant_expression
| IDENTITY [ ( seed , increment ) ]
}
| ADD
{ < column_definition > | < table_constraint > } [ ,...n ]
| DROP
{ [ CONSTRAINT ] constraint_name
| COLUMN column }
] }只能写两条?
add CONSTRAINT DF_test_Col1 DEFAULT (1) FOR col1Incorrect syntax near the keyword 'CONSTRAINT'.
A. 添加新列
以下示例将添加一个允许空值的列,而且没有通过 DEFAULT 定义提供的值。
在该新列中,每一行都将有 NULL 值。
*/
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. 删除列
以下示例将修改一个表以删除列。
*/
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. 更改列的数据类型
以下示例将表中列的数据类型由 INT 更改为 DECIMAL。
*/
CREATE TABLE doc_exy ( column_a INT ) ;
GO
INSERT INTO doc_exy (column_a)
VALUES (10) ;
GO
ALTER TABLE doc_exy ALTER COLUMN column_a DECIMAL (5, 2) ;
GO
DROP TABLE doc_exy ;
GO
/*
D. 添加包含约束的列
以下示例将添加一个包含 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
/*
E. 在现有列中添加一个未经验证的 CHECK 约束
以下示例将在表中的现有列中添加一个约束。该列包含一个违反约束的值。
因此,将使用 WITH NOCHECK 以避免根据现有行验证该约束,从而允许添加该约束。
*/
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
/*
F. 在现有列中添加一个 DEFAULT 约束
以下示例将创建一个包含两列的表,在第一列插入一个值,
另一列保持为 NULL。然后在第二列中添加一个 DEFAULT 约束。
验证是否已应用了默认值,另一个值是否已插入第一列以及是否已查询表。
*/CREATE TABLE doc_exz ( column_a INT, column_b INT) ;
GO
INSERT INTO doc_exz (column_a)
VALUES ( 7 ) ;
GO
ALTER TABLE doc_exz
ADD CONSTRAINT col_b_def
DEFAULT 50 FOR column_b ;
GO
INSERT INTO doc_exz (column_a)
VALUES ( 10 ) ;
GO
SELECT * FROM doc_exz ;
GO
DROP TABLE doc_exz ;
GO
/*
G. 添加多个包含约束的列
以下示例将添加多个包含随新列定义的约束的列。
第一个新列具有 IDENTITY 属性。表中的每一行在标识列中都有新的增量值。
*/
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 that references 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 telephone number format.
column_d VARCHAR(16) NULL
CONSTRAINT column_d_chk
CHECK
(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/*
H. 添加包含默认值的可为空的列
以下示例将添加一个包含 DEFAULT 定义的可为空的列,
并使用 WITH VALUES 为表中的各个现有行提供值。
如果没有使用 WITH VALUES,那么每一行的新列中都将具包含 NULL 值。
*/
Use AdventureWorks ;
GO
CREATE TABLE doc_exf ( column_a INT) ;
GO
INSERT INTO doc_exf
VALUES (1) ;
GO
ALTER TABLE doc_exf
ADD AddDate smalldatetime NULL
CONSTRAINT AddDateDflt
DEFAULT GETDATE() WITH VALUES ;
GO
DROP TABLE doc_exf ;
GO
标识列可用 IDENTITY 属性建立,因此在SQL Server中,又称标识列为具有IDENTITY属性的列或IDENTITY列。
下面的例子创建一个包含名为ID,类型为int,种子为1,递增量为1的标识列
CREATE TABLE T_test
(ID int IDENTITY(1,1),
Name varchar(50)
)2、在现有表中添加标识列
下面的例子向表T_test中添加一个名为ID,类型为 int,种子为1,递增量为1的标识列
--创建表
CREATE TABLE T_test
(Name varchar(50)
)--插入数据
INSERT T_test(Name) VALUES('张三')--增加标识列
ALTER TABLE T_test
ADD ID int IDENTITY(1,1)3、判段一个表是否具有标识列可以使用 OBJECTPROPERTY 函数确定一个表是否具有 IDENTITY(标识)列,用法:
Select OBJECTPROPERTY(OBJECT_ID('表名'),'TableHasIdentity')
如果有,则返回1,否则返回04、判断某列是否是标识列可使用 COLUMNPROPERTY 函数确定 某列是否具有IDENTITY 属性,用法
SELECT COLUMNPROPERTY( OBJECT_ID('表名'),'列名','IsIdentity')
如果该列为标识列,则返回1,否则返回04、查询某表标识列的列名
SQL Server中没有现成的函数实现此功能,实现的SQL语句如下
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.columns
WHERE TABLE_NAME='表名' AND COLUMNPROPERTY(
OBJECT_ID('表名'),COLUMN_NAME,'IsIdentity')=15、标识列的引用如果在SQL语句中引用标识列,可用关键字IDENTITYCOL代替
例如,若要查询上例中ID等于1的行,
以下两条查询语句是等价的
SELECT * FROM T_test WHERE IDENTITYCOL=1
SELECT * FROM T_test WHERE ID=16、获取标识列的种子值可使用函数IDENT_SEED,用法:
SELECT IDENT_SEED ('表名')7、获取标识列的递增量可使用函数 IDENT_INCR ,用法:
SELECT IDENT_INCR('表名')8、获取指定表中最后生成的标识值可使用函数IDENT_CURRENT,用法:
SELECT IDENT_CURRENT('表名')
注意事项:当包含标识列的表刚刚创建,为经过任何插入操作时,使用IDENT_CURRENT函数得到的值为标识列的种子值,这一点在开发数据库应用程序的时候尤其应该注意。