sqlserver数据库设计完整性与约束

 use StudentManageDB
go
--创建主键约束 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 ck_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) update Students set PhoneNumber='' where StudentId=10000
select * from Students --创建默认值
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 Students (StudentName,Gender,Birthday,Age,StudentIdNo ,PhoneNumber,
StudentAddress,ClassId)
values('李小璐','女','1989-01-12',24,120229198901121315, '',default,1) insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo ,PhoneNumber,
ClassId)
values('李小璐','女','1989-01-12',24,120229198901121316, '',1) --外键约束
insert into StudentClass (ClassId,ClassName) values(1,'软件1班')
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) select * from studentClass
上一篇:eclipse插件安装验证及问题处理


下一篇:前端开发的使用服务器环境开源项目 D2Server 可替代Apache