使用代码创建SQL约束
-
约束的种类
主键约束(primary key PK) 唯一约束(unique UQ) 检查约束(check CK) 默认值约束(default DF) 外键约束(foreign key FK) -
语法
alter table 表名称
add constraint 约束的名称(以简写作为前缀) 约束的类型 约束的说明(字段or表达式or值) -
使用代码创建约束
-- 1、将Id设置为主键
if exists(select * from sysobjects where name = 'PK_Teacher_Id')
alter table Teacher drop constraint PK_Teacher_Id --如果存在已经存在约束PK_Teacher_Id ,则删除
alter table Teacher
add constraint PK_Teacher_Id primary key(Id)
-- 2、将Name设置为唯一键
if exists(select * from sysobjects where name = 'UQ_Teacher_Name')
alter table Teacher drop constraint UQ_Teacher_Name --如果存在已经存在约束UQ_Teacher_Name ,则删除
alter table Teacher
add constraint UQ_Teacher_Name unique(Name)
-- 3、将Age添加Check约束
if exists(select * from sysobjects where name = 'CK_Teacher_Age')
alter table Teacher drop constraint CK_Teacher_Age --如果存在已经存在约束CK_Teacher_Age ,则删除
alter table Teacher
add constraint CK_Teacher_Age check(Age > 0 and Age <= 100)
-- 4、将Birthday添加默认值
if exists(select * from sysobjects where name = 'DF_Teacher_Birthday')
alter table Teacher drop constraint DF_Teacher_Birthday --如果存在已经存在约束DF_Teacher_Birthday ,则删除
alter table Teacher
add constraint DF_Teacher_Birthday default('2001-10-10') for Birthday --for说明为那个字段添加默认值
-- 5、将CId添加外键约束
if exists(select * from sysobjects where name = 'FK_Teacher_CId')
alter table Teacher drop constraint FK_Teacher_CId --如果存在已经存在约束FK_Teacher_CId ,则删除
alter table Teacher
with nocheck --当增加约束时,已经存在数据,可以设置不检查现有数据
add constraint FK_Teacher_CId foreign key(CId) references Classes(CId)
- 备注
增加外键约束时,设置级联更新、级联删除
[on update {no action | cascade | set null | set default }]
[on delete {no action | cascade | set null | set default }]