手写T-SQL创建约束

---------------------------手写T-SQL创建约束------------------------------
alter table Employees add constraint FK_Employees_Department_EmpDepId foreign key(EmpDepId)
references Department(DepId) on delete cascade on update cascade
drop table Department
drop table Employees

create table Employees
(
 EmpId int identity(1,1),
 EmpName varchar(50),
 EmpGender char(2),
 EmpAge int,
 EmpEmail varchar(100),
 EmpAddress varchar(500)
)
create table Department
(
 DepId int identity(1,1),
 DepName varchar(50)
)
-----------------1.为Department创建主键约束----------------------
alter table Department
add constraint PK_Department_DepId
primary key(DepId)

------------------2.增加一个唯一约束------------------
alter table Department
add constraint UQ_Department_DepName
unique(DepName)

-------------------3.向Employees增加一个主键约束--------------
alter table Employees add
constraint PK_Employees_EmpId primary key(EmpId)

-----------------4.向姓名增加一个唯一约束-----------------
alter table Employees add
constraint UQ_Employees_EmpName unique(EmpName)

-----------------5.向姓名中增加一个非空约束--------------------
alter table Employees alter column EmpName
varchar(50) not null

-----------------6.删除姓名中的唯一约束-------------------------------
alter table Employees drop constraint UQ_Employees_EmpName

--------------7.性别增加一个检查约束，要求男或女，默认约束为男--------
alter table Employees add constraint CK_Employees_EmpGender
check(EmpGender='男' or EmpGender='女')

alter table Employees add constraint DF_Employees_EmpGender default('男')
for EmpGender

-------------8.年龄增加一个检查约束-----------------------------
alter table Employees add constraint CK_Employees_EmpAge
check(EmpAge>=18 and EmpAge<=60)

--------------9.Email增加一个唯一约束-------------------------
alter table Employees add constraint UQ_Employees_EmpEmail
unique(EmpEmail)

---------------10.增加一列-------------------------------
alter table Employees add EmpDepId int not null

----------------11.为列增加外键约束-----------------------------
alter table Employees add constraint FK_Employees_EmpDepId
foreign key(EmpDepId) references Department(DepId)

---------------12.删除约束--------------------------------------
alter table Employees drop constraint FK_Employees_EmpDepId

---------------13.增加级联------------------------------------
alter table Employees add constraint FK_Employees_EmpDepId
foreign key(EmpDepId) references Department(DepId)
on delete cascade
on update cascade

-----------------------------14.通过一句话来删除所有约束----------------
alter table Employees drop constraint FK_Employees_EmpDepId,
UQ_Employees_EmpEmail,CK_Employees_EmpAge

-----------------15.通过一句话来创建多个约束------------------------
alter table Employees add constraint FK_Employess_EmpDepId foreign
key(EmpDepId) references Department(DepId),constraint UQ_Employees_EmpEmail
unique(EmpEmail)

--------------------16.通过创建表来直接创建约束-------------------
create table Employees
(
 EmpId int identity(1,1) primary key,
 EmpName varchar(50) not null unique,
 EmpGender char(2) check(EmpGender='男' or EmpGender='女') default('男'),
 EmpAge int check(EmpAge>=18 and EmpAge<=60),
 EmpEmail varchar(100) unique,
 EmpAddress varchar(500) not null,
 EmpDepId int foreign key references Department(DepId)
)