SQL Server 的约束

SQL Server 的约束

SQL Server的约束有五种，分别如下：

• check

• default

• foreign key

• primary key

• unique

创建方式

1. 创建表时创建

如下脚本给出创建表是创建约束的样例

CREATE TABLE constraintTest(
       id INT PRIMARY KEY
       ,name VARCHAR(50) UNIQUE
       ,age INT CHECK(age>0 AND age<150)
       ,sex BIT DEFAULT(0)
);
CREATE TABLE foreignTest(id INT FOREIGN KEY REFERENCES constraintTest(id)
       ON UPDATE CASCADE
       ON DELETE CASCADE);

2. 后期增加

创建表后，如果需要增加约束，可以通过ALTER TABLE tableName ADD CONSTRAINT constraintName constraint创建，创建样例如下：

CREATE TABLE constraintTest1(
       id INT not null
       ,name VARCHAR(50)
       ,age INT
       ,sex BIT
);
--增加主键
ALTER TABLE constraintTest1 ADD CONSTRAINT pk_test PRIMARY KEY(id);
--增加唯一约束
ALTER TABLE constraintTest1 ADD CONSTRAINT uq_test UNIQUE(name);
--增加check约束
ALTER TABLE constraintTest1 ADD CONSTRAINT ck_test CHECK(age>0 AND age<200);
--增加默认值
ALTER TABLE constraintTest1 ADD CONSTRAINT df_test DEFAULT(0) FOR sex;
CREATE TABLE foreignTest1(id INT);
--增加外键
ALTER TABLE foreignTest1 ADD CONSTRAINT fk_test FOREIGN KEY(id)
REFERENCES constraintTest1(id)
ON DELETE CASCADE
ON UPDATE CASCADE;

查看约束信息

sp_helpconstraint 可以帮助查看表上的约束信息

exec sp_helpconstraint constraintTest
exec sp_helpconstraint foreignTest

也可以通过动态视图来查看约束信息

--查看主键和unique约束信息
SELECT * FROM sys.key_constraints
WHERE parent_object_id IN (OBJECT_ID('constraintTest',N'U'));
--查看check 约束信息
SELECT * FROM sys.check_constraints
WHERE parent_object_id IN (OBJECT_ID('constraintTest',N'U'));
--查看默认值信息
SELECT * FROM sys.default_constraints
WHERE parent_object_id IN (OBJECT_ID('constraintTest',N'U'));
--查看外键信息
SELECT * FROM sys.foreign_keys
WHERE parent_object_id IN (OBJECT_ID('foreignTest',N'U'));
--查看外键列
SELECT fkc.*,c.name columnName FROM sys.foreign_key_columns fkc
LEFT JOIN sys.columns c ON fkc.constraint_column_id=c.column_id
       AND fkc.parent_object_id=c.[object_id]
WHERE parent_object_id IN (OBJECT_ID('foreignTest',N'U'));
SELECT * FROM sys.sysconstraints;

禁用/启用约束

从sp_helpconstraint查看的结果可以看到

status_enabled 状态只有外键约束和check约束才有值，nocheck也仅仅只能改变这两个约束的状态，对其他约束没有影响。上面显示，check和外键约束都是开启的，下面使用nocheck关闭这两个约束

ALTER TABLE constraintTest NOCHECK CONSTRAINT ALL;
ALTER TABLE foreignTest NOCHECK CONSTRAINT ALL;

此时，我们再查看约束状态，可以发现状态变为disabled，其他约束的状态没有改变

为测试约束禁用情况，我们向表中插入一些值

INSERT INTO constraintTest(id,age,sex) VALUES (1,300,NULL);

因为表中id列值已经有1存在，再插入1，违反唯一性约束，报错。把第一列值改为2，再执行插入

可以看到，由于name值没有指定值，默认为Null，而表中Name列NULL值已经存在，违反unique约束报错

删除约束。

最后，我们将插入值再改写如下，发现插入成功

INSERT INTO constraintTest(id,name,age) VALUES (2,'test',300);

查看表中数据

可以看到，age=300不在原约束大于0，小于200 的范围内。同时sex没有指定值，给了默认值0

我们在向foreignTest表的id中插入值3（该值在主键表不存在，如上）

INSERT INTO foreignTest(id) VALUES(3);

可以成功插入。

至此，我们确定nocheck 只影响check约束和外键约束，而对主键、unique、默认值不起作用

启用约束

ALTER TABLE constraintTest CHECK CONSTRAINT ALL;
ALTER TABLE foreignTest CHECK CONSTRAINT ALL;

开启约束时，并没有报错，显然，无论是check约束，还是外键约束，对已有的数据不起作用，只对启用后的数据变更起作用。

如果喜欢，可以搜索关注 MSSQLServer 公众号，将有更多精彩内容分享：