SQLServer之FOREIGN KEY约束

2019-03-23 11:04栏目:程序人生

FOREIGN KEY约束添加规则

壹 、外键约束并不仅能够与另一表的主键约束相链接,它还是能定义为引用另多个表中 UNIQUE 约束的列。

② 、倘若在 FOREIGN KEY 约束的列中输入非 NULL 值,则此值必须在被引用列中设有;否则,将回来违反外键约束的错误消息。 若要确定保障验证了组合外键约束的持有值,请对负有参与列指定NOT NULL。

叁 、FOREIGN KEY 约束仅能引用位于同一服务器上的相同数据库中的表。 跨数据库的引用完整性必须经过触发器完成。

④ 、FOREIGN KEY 约束可援引同一表中的其余列。 此行为称作自引用。

⑤ 、在列级钦命的 FOREIGN KEY 约束只好列出1个引用列。 此列的数据类型必须与概念约束的列的数据类型相同。

⑥ 、在表级钦赐的 FOREIGN KEY 约束所持有的引用列数目必须与约束列列表中的列数相同。 每一个引用列的数据类型也非得与列表中相应列的数据类型相同。

⑦ 、对于表可包涵的引用别的表的 FOREIGN KEY 约束的多寡或别的表所拥有的引用特定表的 FOREIGN KEY 约束的数据, 数据库引擎 都尚未预订义的范围。 就算如此,可采取的 FOREIGN KEY 约束的实际上数目依旧受硬件配备以及数据库和应用程序设计的限制。 表最多能够将 2五十四个其余表和列作为外键引用(传出引用)。 SQL Server 二〇一五 (13.x) 将可在单独的表中引用的其余表和列(传入引用)的多寡限制从 253 升高至 10,000。 (兼容性级别至少必须为 130。)数量限制的抓牢带来了下列约束:

DELETE 和 UPDATE DML 操作帮助胜出 253 个外键引用。 不帮衬ME卡宴GE 操作。

对自个儿实行外键引用的表仍只可以进展 25五个外键引用。

列存款和储蓄索引、内部存储器优化表和 Stretch Database 暂不帮助开始展览超过 253 个外键引用。

八 、对于权且表不强制 FOREIGN KEY 约束。

九 、若是在 CL瑞虎用户定义类型的列上定义外键,则该品种的兑现必须协理二进制排序。

十 、仅当 FOREIGN KEY 约束引用的主键也定义为项目 varchar(max) 时,才能在此约束中接纳项目为varchar(max) 的列。

行使SSMS数据库管理工具添加DEFAULT约束

一 、连接数据库,接纳数据表-》右键点击-》选取设计。

图片 1

② 、在表设计窗口中-》选用数据列-》在列属性窗口中找到暗中同意值或绑定-》输入暗许值(注意暗许值的数据类型和输入格式)。

图片 2

叁 、点击保存按钮(大概ctrl s)-》刷新表-》再度打开表查看结果。

图片 3

CHECK约束优缺点

优点:

① 、保证列数据正式和范围,能够约束数据完整性

二 、有Check约束的列能够提必要查询优化器音信之所以升级品质

缺点:

壹 、插入只怕修改时数据不符合约束规则,不能修改成功。

11:重命名列名

使用SSMS数据库管理工科具添加外键约束

本示例演示当表结构已存在时添加外键约束,创设表时充裕外键约束步骤和表结构存在时添加外键步骤一样。示例演示如下:

一 、连接数据库,打开要添加外键的数目表-》右键点击-》选择设计。

图片 4

二 、在表设计窗口-》选取要添加外键的多寡行-》右键点击-》选取涉及。

图片 5

三 、在外键关系窗口中-》点击添加。

图片 6

④ 、添加实现后-》首先修改表和列规范。

图片 7

五 、在表和列窗口中-》输入外键名-》在左边采取主表和涉嫌的列-》在左侧选用从表和当作外键的列-》点击显明。

图片 8

⑥ 、在外键关系窗口中-》可选拔丰裕只怕不添加外键描述-》可挑选丰盛可能不添加修改或然去除数据时级联操作-》可采用丰盛大概不添狠抓制外键约束-》可挑选丰富恐怕不添加强制用于复制-》点击关闭。

图片 9

七 、点击保存按钮(ctrl s)-》此时表会弹出警告窗口,点击是-》刷新查看外键是或不是丰盛成功。

图片 10

图片 11

使用T-SQL脚本添加DEFAULT约束

当表结构不存在时

要是表结构不设有,能够从来抬高约束,能够而且添加一个要么七个约束。

语法:

--即使表结构不设有时添加check约束
use 数据库名;
go
--假如已存在表则删除
if exists(select * from sysobjects where name=表名 and xtype='U')
drop table 表名;
go
--创建表
create table 表名
(
--字段声明
列名 int identity(1,1) not null,
列名 nvarchar(50) null,
列名 nvarchar(50) null constraint 约束名 check(约束规则),
列名 nvarchar(50) null,
列名 int,
列名 int constraint 约束名 check(约束规则)
primary key clustered(列名 asc) with(ignore_dup_key=off) on [primary], --主键索引注解
)on [primary]

--字段注释注明
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'列描述' , @level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'表名', @level2type=N'COLUMN',@level2name=N'列名';

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'列描述' , @level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'表名', @level2type=N'COLUMN',@level2name=N'列名';

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'列描述' , @level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'表名', @level2type=N'COLUMN',@level2name=N'列名';

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'列描述' , @level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'表名', @level2type=N'COLUMN',@level2name=N'列名';

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'列描述' , @level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'表名', @level2type=N'COLUMN',@level2name=N'列名';

go

示例:

--要是表结构不设有时添加check约束
use testss;
go
--如若已存在表则删除
if exists(select * from sysobjects where name='test1' and xtype='U')
drop table test1;
go
--创建表
create table test1
(
--字段注脚
id int identity(1,1) not null,
name nvarchar(50) null,
sex nvarchar(50) null constraint check1 check(sex='男' or sex='女'),
age nvarchar(50) null,
classid int,
height int constraint check2 check(height>=110 and height<=120)
primary key clustered(id asc) with(ignore_dup_key=off) on [primary], --主键索引注解
)on [primary]

--字段注释注解
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'id主键' , @level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'test1', @level2type=N'COLUMN',@level2name=N'id';

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'姓名' , @level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'test1', @level2type=N'COLUMN',@level2name=N'name';

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'性别' , @level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'test1', @level2type=N'COLUMN',@level2name=N'sex';

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'年龄' , @level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'test1', @level2type=N'COLUMN',@level2name=N'age';

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'班级id' , @level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'test1', @level2type=N'COLUMN',@level2name=N'classid';

go

 图片 12

图片 13

图片 14

Alter table [表名] add constraint [ 约束名] primary key( [列名])

FOREIGN KEY约束优缺点

优点:

一 、保障数据的一致性,完整性,更保证。

② 、关联合检查询时,可以用到FK 的计算新闻。

叁 、有主外键的数据库设计能够追加E奥迪Q5图的可读性。

缺点:

壹 、删队或更新关联数据时供给做检讨,成效会十分低。

二 、手工业调数据时,会设有主从表校验,会相比麻烦。

三 、批量导入数据时,会设有外键校验,必要先关闭外键约束,导入完毕再打开外键约束,操作比较费心。

 

DEFAULT约束添加规则

一 、若在表中定义了默许值约束,用户在插入新的多寡行时,如若该行没有点名数量,那么系统将暗中认可值赋给该列,假诺我们不设置默许值,系统暗许为NULL。

贰 、倘诺“暗中同意值”字段中的项替换绑定的默许值(以不带圆括号的样式呈现),则将唤起您清除对暗中认可值的绑定,并将其替换为新的暗中认可值。

三 、若要输入文本字符串,请用单引号 (') 将值括起来;不要选取双引号 ("),因为双引号已保存用于带引号的标识符。

四 、若要输入数值暗许值,请输入数值并且毫不用引号将值括起来。

五 、若要输入对象/函数,请输入对象/函数的名目并且永不用引号将名称括起来。

动用SSMS数据库管理工科具添加CHECK约束

一 、连接数据库,选拔数据库,选择数据表-》右键点击(大概直接点击约束,右键点击,选择丰裕约束,前边步骤一样)-》接纳设计。

图片 15

二 、选择要丰裕约束的数额列-》右键点击-》选拔CHECK约束。

图片 16

三 、在CHECK约束弹出框中式点心击添加。

图片 17

④ 、在CHECK约束弹出框中-》输入CHECK约束表明式-》输入CHECK约束名-》输入CHECK约束描述-》其他能够选取暗中同意。

图片 18

五 、点击关闭-》点击保存按钮(或然ctrl s)-》刷新表查看结果。

图片 19

 

在新表中开创外键

 语法:

if exists( select * from sysobjects where name=表名 and type ='U')
drop table 表名;
go

--当表结构不存在时
--建表语法注解
create table 表名
(
--字段证明
列名 int identity(1,1) not null,
列名 int,
primary key clustered(id asc) with(ignore_dup_key=off) on [primary], --主键索引表明
constraint 外键名 foreign key(列名)

references 主表名(列名)
on update cascade--是或不是级联操作
on delete cascade
)on [primary]

--字段注释注解
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'列说明' , @level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'表名', @level2type=N'COLUMN',@level2name=N'列名';

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'列说明' , @level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'表名', @level2type=N'COLUMN',@level2name=N'列名';

go

示例:

if exists( select * from sysobjects where name='test1'and type ='U')
drop table test1;
go

--当表结构不设有时
--建表语法注明
create table test1
(
--字段声明
id int identity(1,1) not null,
name nvarchar(50) null,
sex nvarchar(50) null,
age nvarchar(50) null,
classid int,
primary key clustered(id asc) with(ignore_dup_key=off) on [primary], --主键索引申明
constraint t3_t4 foreign key(classid)

references test2 (id)
on update cascade
on delete cascade
)on [primary]

--字段注释注脚
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'id主键' , @level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'test1', @level2type=N'COLUMN',@level2name=N'id';

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'姓名' , @level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'test1', @level2type=N'COLUMN',@level2name=N'name';

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'性别' , @level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'test1', @level2type=N'COLUMN',@level2name=N'sex';

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'年龄' , @level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'test1', @level2type=N'COLUMN',@level2name=N'age';

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'班级id' , @level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'test1', @level2type=N'COLUMN',@level2name=N'classid';

go

图片 20

始建表时增进私下认可约束

先是判断表是不是选在,借使存在则先删除表再添加,假若不存在则一向抬高。

语法:

--成立新表时添加私下认可约束
--数据库评释
use 数据库名
go
--假设表已存在则先删除表再成立,假设表不设有则平素开立
if exists(select * from sysobjects where name=表名 and type ='U')
drop table 表名;
go
--建表语法评释
create table 表名
(
--字段注脚
列名 列类型 identity(1,1) not null,
列名 列类型) null,
列名 列类型 null,
列名 列类型 null,
列名 列类型,
列名 列类型 constraint 约束名 default 默认值,
primary key clustered(列名 asc) with(ignore_dup_key=off) on [primary] --主键索引阐明
)on [primary]

--字段注释表明
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'列说明' , @level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'表名', @level2type=N'COLUMN',@level2name=N'列名';

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'列说明' , @level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'表名', @level2type=N'COLUMN',@level2name=N'列名';

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'列说明' , @level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'表名', @level2type=N'COLUMN',@level2name=N'列名';

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'列说明' , @level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'表名', @level2type=N'COLUMN',@level2name=N'列名';

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'列说明' , @level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'表名', @level2type=N'COLUMN',@level2name=N'列名';

go

示例:

--创造新表时添加暗中认可约束
--数据库注明
use testss
go
--假诺表已存在则先删除表再成立,即使表不设有则直接创制
if exists(select * from sysobjects where name='test1' and type ='U')
drop table test1;
go
--建表语法申明
create table test1
(
--字段阐明
id int identity(1,1) not null,
name nvarchar(50) null,
sex nvarchar(50) null,
age nvarchar(50) null,
classid int,
height int constraint default_he default 166,
primary key clustered(id asc) with(ignore_dup_key=off) on [primary] --主键索引评释
)on [primary]

--字段注释表明
exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'id主键' , @level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'test1', @level2type=N'COLUMN',@level2name=N'id';

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'姓名' , @level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'test1', @level2type=N'COLUMN',@level2name=N'name';

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'性别' , @level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'test1', @level2type=N'COLUMN',@level2name=N'sex';

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'年龄' , @level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'test1', @level2type=N'COLUMN',@level2name=N'age';

exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'班级id' , @level0type=N'SCHEMA',
@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'test1', @level2type=N'COLUMN',@level2name=N'classid';

go

图片 21

图片 22

应用T-SQL脚本添加CHECK约束

5:添加唯一约束

行使T-SQL脚本添加外键约束

DEFAULT约束优缺点

优点:

壹 、使用暗许值能够收缩代码量,新增多少时得以不用写新增暗许值列,执行新增操作时时默许填充。

二 、较有利于开始展览总计和剖析,以及福利程序逻辑操作。

缺点:

壹 、使用不为NULL的暗中认可值,占用了越多的储存空间。

 

CHECK约束添加规则

壹 、CHECK 约束用于限制列中的值的范围。

二 、Check约束通过逻辑表达式来判定数据的管事,用来限制输入一列或多列的值的范围,在列中立异数据时,所要输入的始末必须满意Check约束的基准,不然将不可能正确输入。

③ 、假诺对单个列定义 CHECK 约束,那么该列只同意特定的值。

④ 、倘使对一个表定义 CHECK 约束,那么此约束会在一定的列中对值实行限制。

9:删除约束

当表结构已存在时

设若要充足约束的表已存在外键约束,须求先删除此之外键约束再添加外键约束。借使不存在外键约束能够增进外键约束。

语法:

if exists(select * from sysobjects where name=约束名)
alter table 数据库名.[dbo].表名 drop constraint 约束名;
alter table 数据库名.[dbo].表名 with check add constraint 约束名 foreign key(列名)
references 数据库名.[dbo].表名(列名)

on delete cascade
on update cascade;
go

示例:

if exists(select * from sysobjects where name='t1_t2')
alter table [testss].[dbo].[test1] drop constraint t1_t2;
alter table [testss].[dbo].[test1] with check add constraint t1_t2 foreign key(classid)
references [testss].[dbo].[test2](id)
on delete cascade
on update cascade;
go

图片 23

当表结构已存在时

率先判断表中是还是不是留存默许约束,倘诺存在则先删除暗中同意约束再添加,假诺不设有则平素抬高。

语法:

use 数据库
go
--判断私下认可约束是或不是存在,借使存在则先删除,借使不设有则平素助长
if exists(select * from sysobjects where name=约束名)
alter table 表名 drop constraint 约束名;
go
--给内定列添加暗许约束
alter table 表名 add constraint 约束名 default(约束值) for 列名;
go

示例:

use [testss]
go
--判断暗许约束是还是不是存在,借使存在则先删除,借使不设有则一向抬高
if exists(select * from sysobjects where name='defalut_height')
alter table [testss].[dbo].[test1] drop constraint defalut_height;
go
--给钦命列添加暗许约束
alter table [testss].[dbo].[test1] add constraint defalut_height default(160) for height;
go

图片 24

图片 25

当表结构存在时

添加CHECK约束时首先校验约束是还是不是已存在,如若存在应该是先删除再添加,假使不存在则直接助长。

语法:

-- 添加四个暗许约束
use 数据库名;
go

if exists(select * from sysobjects where name=约束名)
alter table 表名 drop constraint 约束名;
go

alter table 表名 add constraint 约束名 check(约束规则),constraint 约束名 check(约束规则);
go

示例:

-- 添加1个默许约束
use testss;
go

if exists(select * from sysobjects where name='check1')
alter table test1 drop constraint check1;
go

alter table test1 add constraint check1 check(height>=170 and height<=175);
go

 图片 26

图片 27

exec sp_rename '[原表名]','[新表名]'

12:为表添加描述新闻
EXECUTE sp_addextendedproperty N'MS_Description', '职员消息表', N'user', N'dbo', N'TABLE', N'表名', NULL, NULL

版权声明:本文由ca888发布于程序人生,转载请注明出处:SQLServer之FOREIGN KEY约束