sql语句修改表结构和添加约束

sql语句修改表结构和添加约束

create table Tbclass
(
	clsId int primary key identity(1,1),
	clsName nvarchar(8) unique,
	clsTeacher nvarchar(4),
	clsNumber smallint check(clsNumber >= 20 and clsNumber <= 80)
)
create table TbStudent
(
	stuId int identity(1,1) primary key,
	stuName nvarchar(4) not null,
	stuNumber char(9) unique,
	stuGender bit default(1),
	stuAddress nvarchar(32),
	stuAge smallint,
	stuPhone char(11),
	stuClassId int foreign key references  Tbclass(clsId)

)

---

以下操作是在上面建的表的前提下完成的

1.删除一列

alter table TbStudent drop column stuPhone

2.添加一列

alter table TbStudent add  stuPhone char(11)

3.修改字段的数据类型

alter table TbStudent alter column stuGender nchar(1)

4.添加主键约束

alter table TbStudent add constraint Pk_TbStudent_StuId primary key(StuId)

5.添加唯一性约束

alter table TbStudent add constraint UK_TbStdent_StuName unique(stuName)

6.添加check约束

alter table TbStudent add constraint CK_TbStudent_StuAge
check(stuAge>=20 and stuAge <=30)

7.非空约束,实际上就是对列的数据类型修改

alter table TbStudent alter column stuPhone char(11) not null

8.添加外键约束

alter table TbStudent add constraint FK_TbStudent_stuClassId 
foreign key(stuClassId) references Tbclass(clsId)

9.外键的级联 删除或更新

--级联删除
alter table TbStudent add constraint FK_TbStudent_stuClassId 
foreign key(stuClassId) references Tbclass(clsId) on delete cascade 
--级联更新
alter table TbStudent add constraint FK_TbStudent_stuClassId 
foreign key(stuClassId) references Tbclass(clsId) on update cascade 

10.删除约束

alter table TbStudent drop constraint FK_TbStudent_stuClassId