use MyTestsDB go /*创建表 TB_stuInfo */ if exists(select name from sysobjects where name = 'TB_stuInfo') drop table TB_stuInfo create table TB_stuInfo ( stuNo char(12) not null, stuName varchar(20) not null, stuAge int not null, --身份证号,整数18位,小数0位; 可以换成varchar(18) stuID numeric(18,0), --标识列(起始值, 地增量) stuSeat smallint identity(1, 1), stuAddress Text ) go /*创建表 TB_person, 保存所有非黑户的身份证号和姓名 */ if exists(select name from sysobjects where name = 'TB_person') drop table TB_person create table TB_person ( pID numeric(18, 0) not null, pName varchar(20) not null ) go /*向TB_person表 添加约束 */ alter table TB_person add constraint PK_TB_person_pID primary key(pID) go /*向TB_stuInfo表 添加约束 */ --主键约束 alter table TB_stuInfo add constraint PK_TB_stuInfo_stuNO primary key(stuNO) --检查约束 alter table TB_stuInfo add constraint CK_TB_stuInfo_stuAge check(stuAge between 15 and 35) --默认约束 alter table TB_stuInfo add constraint DF_TB_stuInfo_stuAddress default('地址不详') for stuAddress --唯一约束 alter table TB_stuInfo add constraint UQ_TB_stuInfo_stuID unique(stuID) go /*添加TB_stuInfo表到TB_person的外键引用 外键约束 */ alter table TB_stuInfo add constraint FK_TB_stuInfo_TB_person_stuID foreign key(stuID) references TB_person(pID) go /*删除约束 */ alter table TB_stuInfo drop constraint DF_TB_stuInfo_stuAddress