如何手动生成数据库中的各种约束

一.数据库约束主要包含以下几类
非空约束
主键约束(PK) primary key constraint 唯一且不为空
唯一约束 (UQ)unique constraint 唯一，允许为空，但只能出现一次
默认约束 (DF)default constraint 默认值
检查约束 (CK)check constraint 范围以及格式限制
外键约束 (FK)foreign key constraint 表关系
增加外键约束时，设置级联更新、级联删除：
[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
二.手动生约束
--手动添加主键的约束
alter table Employee add constraint PK_Employee_EmpId primary key(EmpId)
alter table Department add constraint PK_Department_DepId primary key(DepId)
--为EmpName增加非空约束
alter table Employee alter column EmpName varchar(50) not null

--为EmpName增加唯一键约束
alter table Employee add constraint UQ_Employee_EmpName unique(EmpName)
--删约束
alter table Employee drop constraint UQ_Employee_EmpName
--为性别增加默认约束,是默认值为"男"
alter table Employee add constraint DF_Employee_EmpGender default('男') for EmpGender
--为年龄增加检测约束 0—120含0和120
alter table Employee add constraint CK_Employee_EmpAge check(EmpAge>=0 and EmpAge<=120)
--为性别增加检查约束非男即女
alter table Employee add constraint CK_Employee_EmpGender check(EmpGender='男' or EmpGender='女')
--为员工表增加外键约束
--首先设置部门表中的DepId为主键，外键不能为空
alter table Department add constraint PK_Department_DepId primary key(DepId)
--然后写约束
alter table Employee add constraint FK_Employee_EmpDepartmentId foreign key(EmpDepartmentId) references Department(DepId)on delete cascade
alter table Employee drop constraint FK_Employee_EmpDepartmentId

--一条语句删除多条约束,约束名用逗号隔开
alter table Employee drop constraint FK_Employee_EmpDepartmentId,
CK_Employee_EmpAge,
UQ_Employee_EmpName

--1条语句为表增加多个约束
alter table Employee add constraint
UQ_Employee_EmpName
unique(EmpName),
constraint
CKSSS
check(Emp>=0 and EmpAge<=150)