SQLServer之FOREIGN KEY约束

FOREIGN KEY约束添加规则

1、外键约束并不仅仅可以与另一表的主键约束相链接,它还可以定义为引用另一个表中
UNIQUE 约束的列。

2、如果在 FOREIGN
KEY 约束的列中输入非 NULL
值,则此值必须在被引用列中存在;否则,将返回违反外键约束的错误信息。 若要确保验证了组合外键约束的所有值,请对所有参与列指定
NOT NULL。

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

永利皇宫登录网址 ,4、FOREIGN KEY
约束可引用同一表中的其他列。 此行为称为自引用。

5、在列级指定的
FOREIGN KEY 约束只能列出一个引用列。 此列的数据类型必须与定义约束的列的数据类型相同。

6、在表级指定的
FOREIGN KEY 约束所具有的引用列数目必须与约束列列表中的列数相同。 每个引用列的数据类型也必须与列表中相应列的数据类型相同。

7、对于表可包含的引用其他表的 FOREIGN KEY
约束的数目或其他表所拥有的引用特定表的 FOREIGN KEY 约束的数目, 数据库引擎 都没有预定义的限制。 尽管如此,可使用的 FOREIGN KEY
约束的实际数目还是受硬件配置以及数据库和应用程序设计的限制。 表最多可以将 253
个其他表和列作为外键引用(传出引用)。 SQL
Server 2016 (13.x) 将可在单独的表中引用的其他表和列(传入引用)的数量限制从
253 提高至 10,000。 (兼容性级别至少必须为
130。)数量限制的提高带来了下列约束:

DELETE 和 UPDATE
DML 操作支持大于 253 个外键引用。 不支持
MERGE 操作。

对自身进行外键引用的表仍只能进行 253
个外键引用。

列存储索引、内存优化表和 Stretch Database
暂不支持进行超过 253 个外键引用。

8、对于临时表不强制 FOREIGN KEY 约束。

9、如果在 CLR
用户定义类型的列上定义外键,则该类型的实现必须支持二进制排序。

10、仅当 FOREIGN
KEY
约束引用的主键也定义为类型 varchar(max) 时,才能在此约束中使用类型为varchar(max) 的列。

DEFAULT约束优缺点

优点:

1、使用默认值可以减少代码量,新增数据时可以不用写新增默认值列,执行新增操作时时默认填充。

2、较有利于进行统计和分析,以及方便程序逻辑操作。

缺点:

1、使用不为NULL的默认值,占用了更多的存储空间。

 

FOREIGN KEY约束优缺点

优点:

1、保证数据的一致性,完整性,更可靠。

2、关联查询时,可以用到FK 的统计信息。

3、有主外键的数据库设计可以增加ER图的可读性。

缺点:

1、删队或更新关联数据时需要做检查,效率会很低。

2、手工调数据时,会存在主从表校验,会比较麻烦。

3、批量导入数据时,会存在外键校验,需要先关闭外键约束,导入完成再打开外键约束,操作比较麻烦。

 

创建表时添加默认约束

首先判断表是否选在,如果存在则先删除表再添加,如果不存在则直接添加。

语法:

–创建新表时添加默认约束
–数据库声明
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

–主键索引声明
)on

–字段注释声明
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

–主键索引声明
)on

–字段注释声明
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

永利皇宫登录网址 1

永利皇宫登录网址 2

在新表中创建外键

 语法:

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

, –主键索引声明
constraint 外键名 foreign key(列名)

references 主表名(列名)
on update cascade–是否级联操作
on delete cascade
)on

–字段注释声明
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

, –主键索引声明
constraint t3_t4 foreign key(classid)

references test2 (id)
on update cascade
on delete cascade
)on

–字段注释声明
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

永利皇宫登录网址 3

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

1、连接数据库,选择数据表-》右键点击-》选择设计。

永利皇宫登录网址 4

2、在表设计窗口中-》选择数据列-》在列属性窗口中找到默认值或绑定-》输入默认值(注意默认值的数据类型和输入格式)。

永利皇宫登录网址 5

3、点击保存按钮(或者ctrl+s)-》刷新表-》再次打开表查看结果。

永利皇宫登录网址 6

网站地图xml地图