首页 > 代码库 > 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实现约束
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。