首页 > 代码库 > 19-6 通过t-sql实现约束

19-6 通过t-sql实现约束

------------------------------------------------------------------------
--通过t-sql语句来创建约束
------------------------------------------------------------------------
--新建一张表:员工信息表
create table Employees
(
    EmpId int identity(1,1),
    EmpName nvarchar(50),
    EmpGender char(2),
    EmpAge int,
    EmpEmail nvarchar(100),
    EmpAddress nvarchar(500)
)

create table Department
(
  DepId int identity(1,1),
  DepName nvarchar(50),
)
--========================手动增加约束===========================
--手动删除一列(删除EmpAddress列)
alter table Employees drop column EmpAddress

--手动增加一列(增加一列EmpAddress nvarchar(1000))
alter table Employees add EmpAddress nvarchar(1000)

--手动修改一下EmpEmail的数据类型(nvarchar(200))
alter table Employees alter column EmpEmail nvarchar(200)

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

--非空约束,为EmpName增加一个非空约束(修改列)
alter table Employees alter column EmpName nvarchar(50) not null

--为EmpName增加一个唯一约束
alter table Employees add constraint UQ_Employees_EmpName unique (EmpName) 

--为性别增加一个默认约束,默认为"男"
alter table Employees add constraint DF_Employees_EmpGender default() for EmpGender

--为性别增加一个检查约束,要求性别只能是"男"or"女"
alter table Employees add constraint CK_Employees_EmpGender check(EmpGender= or EmpGender=)

--为年龄增加一个检查约束,年龄必须在0-120岁,含岁与岁
alter table Employees add constraint CK_Employees_EmpAge check(EmpAge>=0 and EmpAge<=120)

--创建一个部门表,然后为Employees表增加一个DepId列
alter table Employees add DepId int not null

--为Department表设置主键,主键列是DepId
alter table Department add constraint PK_Department_DepId primary key (DepId)

--增加外键约束
alter table Employees add constraint FK_Employees_Department foreign key(DepId) references Department(DepId)
------------------------------------------------------------------
--删除约束---------------------------------------------
alter table Employees drop constraint UQ_Employees_EmpName,DF_Employees_EmpGender,CK_Employees_EmpGender,CK_Employees_EmpAge,FK_Employees_Department

--通过一条代码来增加多个约束
alter table Employees add
constraint UQ_Employees_EmpName unique (EmpName) ,
constraint DF_Employees_EmpGender default() for EmpGender,
constraint CK_Employees_EmpGender check(EmpGender= or EmpGender=),
constraint CK_Employees_EmpAge check(EmpAge>=0 and EmpAge<=120),
constraint FK_Employees_Department foreign key(DepId) references Department(DepId)
--========================================================================
----------------------创建表的同时就为表增加约束--------------------------
create table Employees
(
    EmpId int identity(1,1) primary key,
    EmpName nvarchar(50) not null unique check(len(EmpName)>2),
    EmpGender char(2) default(),
    EmpAge int check(EmpAge>0 and EmpAge<120),
    EmpEmail nvarchar(100) unique,
    EmpAddress nvarchar(500) not null,
    EmpDepId int foreign key references Department(DepId) on delete cascade
)

create table Department
(
  DepId int identity(1,1) primary key,
  DepName nvarchar(50) not null unique
)

 

19-6 通过t-sql实现约束