SQL使用代码创建数据完整性，约束

--使用代码创建数据完整性：
--主键约束(primary key PK) 唯一键约束(unique UQ) 默认值约束(default DF) check约束(check CK) 主外键约束(foreign key FK)
--语法：
--alter table 表名
--add constraint 约束名称(前缀+自定义名称) 约束类型 约束说明(字段名称 表达式 值)
--为Id设置主键约束
alter table tracher
add constraint PK_Teacher_Id primary key(id)

--为Email添加唯一键约束
if exists (select*from sysobjects where name='UQ_Teacher_Email')
alter table teacher drop constraint UQ_Teacher_Email
go
alter table tracher
add constraint UQ_Teacher_Email unique(email)
--为工资添加默认值，为年龄添加check约束
alter table Teacher
add constraint Df_Teacher_Salary default(5000) for salary,
constraint CK_Tracher_Age check(age>0 and age<=100)

--添加主键约束
alter table Teacher
add constraint FK_Tracher_Classes_Classid foreign key(classid) references classes(cid)
alter table tracher
add constraint PK_Teacher_id primary key(id)

if exists(select*from sysobjects where name='UQ_Teacher_Email')
alter table teacher drop constraint UQ_teacher_Email

alter table tracher
add constraint UQ_teacher_Email unique(email)

alter table Teacher
add constraint PK_teacher_id primary key (id)

alter table Teacher
add constraint DF_teacher_salary default (5000) for salary

alter table Teacher
add constraint UQ_teacher_Email unique(email)
alter table Teacher
add constraint CK_Teacher_Age check(age>0and age<100)

alter table teacher
add constraint FK_teacher_classes_classid foreign key(classid) references classes(id)

转载于:https://www.cnblogs.com/dianshen520/p/4351839.html