数据完整性约束

---恢复内容开始---

完成数据库创建步骤：

建库->建表->主键约束->域完整性约束->外键约束->验证约束->插入数据

添加数据时，首先要添加主键表，再添加外键表

删除数据时，首先删除外键表数据，再删除逐渐表数据

--添加相关约束
--创建主键约束
if exists(select*from sysobjects where name='pk_StudentId')
alter table Students drop constraint pk_StudentId

alter table Students add constraint pk_StudentId primary key(StudentId)

--创建唯一约束
if exists(select*from sysobjects where name='uq_StudentIdNo')
alter table Students drop constraint uq_StudentIdNo

alter table Students add constraint uq_StudentIdNo unique(StudentIdNo)

--创建检查约束
if exists(select*from sysobjects where name='ck_Age')
alter table Students drop constraint uck_Age

alter table Students add constraint ck_Age check(Age between 18 and 25)

if exists(select*from sysobjects where name='ck_PhoneNumber')
alter table Students drop constraint ck_PhoneNumber

alter table Students add constraint ck_PhoneNumber check(len(PhoneNumber)=11)



--创建默认约束
if exists(select*from sysobjects where name='df_StudentAddress')
alter table Students drop constraint df_StudentAddress

alter table Students add constraint df_StudentAddress default('地址不详')for StudentAddress

--插入数据
insert into StudentClass (ClassId,ClassName)
values(1,'一班')

insert into Students (StudentName,Gender,Birthday,StudentIdNo,Age,PhoneNumber,StudentAddress,ClassId)
values('小张','男','1992-10-07',330812199210071234,25,'15958150666',default,1)

select* from Students

--创建外键约束
if exists(select*from sysobjects where name='fk_ClassId')
alter table Students drop constraint fk_ClassId

alter table Students add constraint fk_ClassId foreign key(ClassId)references StudentClass(ClassId)

 

---恢复内容结束---