图片 29

T-SQL基础之外键约束

图片 1图片 2

默认约束:Default

考虑如下关系

临时禁用约束
关键字:nocheck
alter table 表名称
nocheck
constraint 约束标识名称

重新启用约束
关键字:check
alter table 表名称
check
constraint 约束标识名称

 

View
Code

如果在创建外键之前就已经在test02中有了不在test01的id1范围内的数据,则创建外键会失败

图片 3图片 4

alter table test02 drop constraint fk_id11;
alter table test02 with nocheck
    add constraint fk_id11
    foreign key(id11) references test01(id1)
    on update cascade;
update test01 set id1=3 where id1=2;

图片 5图片 6

insert into test02 values(1,1);

在为某个列创建约束时,该列可能之前没有约束并且已经有了数据,此时你想约束这个列。比如联系表的电话一列,以前填写的是’0871-00-78-90’,现在你想要约束为这种格

图片 7图片 8

alter table 表名
alter column
列名 修改操作……

alter table student
alter column
学号 varchar(20)
* 修改列只能修改其数据类型,不能修改约束,被约束的列不能修改,除非先删除约束。

View Code

关键字:create
database

 

View
Code

 

use 数据库名称

 

增加检查约束

此时可以通过with nocheck选项,强制跳过现有数据检查

增加唯一约束

消息 547,级别 16,状态 0,第 1 行
ALTER TABLE 语句与 FOREIGN KEY 约束"fk_id11"冲突。该冲突发生于数据库"test",表"dbo.test01", column 'id1'。

规则

图片 9图片 10

alter database 数据库名称
modify filegroup 存在的组名
name=新的组名

图片 11图片 12

collate

其中cascade是做相同修改;set null是test02表中相应数据修改为null;set
default则是相应数据修改为默认值。

可以在其他任意一个数据库中使用它,但不能在要删除的数据库中使用这个命令

View Code

级联操作

use test;
create table test01
(
id1 int not null,
id2 int not null
);
create table test02
(
id11 int not null,
id22 int not null
);
alter table test01 add constraint pk_id1
  primary key(id1);

增加默认约束

虽然在test01表中id1设置为了主键,不允许null,但是在test02表中的id2可以允许null值

View
Code

创建外键之后,我们将发现无法在test02中输入不在test01的id1范围内的数据

drop database 数据库名称

当我们从test01进行删除或修改数据操作的时候,如果在test02表中也有相关数据则会报错,拒绝操作;

图片 13图片 14

View Code

View
Code

注意:test01表中的id1必须为主键或者唯一索引,否则无法创建基于id1的外键。

alter table Tab add constraint uq_fruit unique(cell_1,cell_2,cell_3)

图片 15图片 16

用于创建各种数据库对象(数据库、表、触发器、存储过程等)
格式如:
create <对象类型>
<对象名称>

级联操作包括cascade/set null/set default,跟在操作on delete/on
update之后

创建表

这样test02表中的数据也会相应修改

增加主键约束

alter table test02 add constraint fk_id11
  foreign key(id11) references test01(id1);

修改文件组名称

View Code

图片 17图片 18

消息 547,级别 16,状态 0,第 1 行
UPDATE 语句与 REFERENCE 约束"fk_id11"冲突。该冲突发生于数据库"test",表"dbo.test02", column 'id11'。
语句已终止。

图片 19图片 20

图片 21图片 22

 identity

消息 547,级别 16,状态 0,第 1 行
INSERT 语句与 FOREIGN KEY 约束"fk_id11"冲突。该冲突发生于数据库"test",表"dbo.test01", column 'id1'。
语句已终止。

图片 23图片 24

此时我们可以通过级联操作来同步删除或修改两个表中的数据。

View
Code

首先创建测试表

外键约束 Foreign
key

alter table test02 alter column id11 int null;
insert into test02 values(null,1);

增加外键约束

alter table test02 with nocheck
  add constraint fk_id11
  foreign key(id11) references test01(id1);

图片 25图片 26

View Code

关键字:create
table 

insert into test01 values(2,1);
insert into test02 values(2,1);
update test01 set id1=3 where id1=2;

图片 27图片 28

alter table test02 drop constraint fk_id11;
insert into test02 values(1,1);
alter table test02 add constraint fk_id11
  foreign key(id11) references test01(id1);

增加文件组

图片 29

textImage_on [ 文件组
] [ default ]

View Code

View Code

test02表中的id11依赖于test01中的id1,因此为test02创建外键

图片 30图片 31

View
Code

图片 32图片 33

删除文件

View
Code

图片 34图片 35

图片 36图片 37

alter table 表名称
add 列名称 数据类型 null [约束][,n……]
* 追加的列必须允许空值存在,否则需要给出默认值 。增加多列使用逗号隔开。

alter table Article
add body varchar(500)

增加日志文件

alter database 数据库名称
add file(name='',size=,maxsize=,filewgrowth=,filename=) , (n……)
多个辅助文件之间用括号括起来并使用逗号分开即可

delete或update主表某行数据,是否同时delete或update外键表相关联的数据。必须在创建表、定义外键约束时设置是否可级联操作,如果定义为on
delete(update)cascade,表示允许

 Microsoft SQL – SQL
SERVER学习总目录

create database 数据库名称
on primary
(
    name='主文件名' ,
    size=初始化大小 按kb、mb、gb、tb设置 ,
    maxsize=最大容量 unlimited(无限)/ 按kb、mb、gb、tb设置 ,
    filegrowth=文件增长率 按%设置 ,
    filename='主文件存放路径' 如'd:\base\base.mdf' 

) ,
(
    name='辅助文件名', 
    size=初始化大小 按kb、mb、gb、tb设置 ,
    maxsize=最大容量 unlimited(无限)/ 按kb、mb、gb、tb设置 ,
    filegrowth=文件增长率 按%设置 ,
    filename='辅助文件存放路径' 如'd:\base\base.ndf' 

)
log on 
(
    name='主日志文件',
    size=初始化大小 按kb、mb、gb、tb设置 ,
    maxsize=最大容量 unlimited(无限)/ 按kb、mb、gb、tb设置 ,
    filegrowth=文件增长率,
    filename='主日志文件存放路径' 如'd:\base\base.ldf'
)

* 其中辅助文件和主日志文件为可选项,只创建数据库主文件就可以了 
如果没有创建主日志文件,系统将自动创建一个。如果手动创建主日志文件,最好将其存放在与主数据库文件不同的磁盘上 这样,当数据库主文件虽在的磁盘故障时,日志文件可以恢复数据库

View
Code

View
Code

数据排序、区分大小写、区分重音。

级联操作自动删除或更新相关联的表的数据。如果定义为on
delete(update)action,表示不允许删除或更新主表的数据。

drop table 表名,表名2,……

主键约束:primary
key

View
Code

式:’9028392’。但因为以前的旧数据不满足现在的约束条件,所以约束将无法创建,此时可以使用约束忽略语句来忽略旧数据,只约束新录入的数据。 

图片 38图片 39

系统为该列自动生成的唯一性的标识数据,种子一般从1开始,增量默认为1,可以更改种子和增量。标识列不允许用户insert数据,除非使用set
identity_insert=on来开启自定义插入标识,插入标识操作只支持insert不支持update。获取最近一次系统生成的identity值:select
@@identity as 列别名。

图片 40图片 41

列约束和表约束

GUI全局唯一标识符,此关键字用于这样额情况,比如当要把当前数据库的某张表的数据复制到另一个数据库的表时,因为两张表都有identity自动标识列,此时如果当前表有唯一标识100,另一张表也有这个标识,那么就会出现错误,此时可以使用rowGUIDcol来解决这样的问题。

一张表可以引用它自身,可如下定义:

View
Code

复制

alter database 数据库名称
add log file(name='',size=,maxsize=,filewgrowth=,filename=) , (n……)
多个日志文件之间用括号括起来并使用逗号分开即可

表自引用

修改表 –
为表修改列

操作表

create database mydb1
on primary ( name='mydb1', filename='c:\mydb1.mdf', size=10,maxsize=50,filegrowth=5 )
log on ( name='mydb1Log',filename='c:\mydb1Log.ldf',size=5,maxsize=25,filegrowth=5 )

图片 42图片 43

View
Code

如果不设置on,或值为default,默认将把表关联到主文件组里,否则按自定义的设置将表关联到其他文件组。

Check约束可以在指定的列上检查该列的值是否满足Check的条件。

alter table 联系
with nocheck/*不检查表里以前的所有数据*/
add constraint cn_phone/*添加约束*/
check( (len(phone) =7) and  (phone between 111111 and 9999999) )

1.主键表不能删除,因为有外键表需要引用它,除非先删除外键表的外键约束,或者使用级联删除。

View
Code

create table 供应商
(
    ID int not null  identity primary key,
    供应商编号int not null unique,
    名字varchar(50) not null
)

create table 食品
(
    ID int not null identity primary key,
    名称varchar(50) not null,
    供应商ID int foreign key references 供应商(供应商编号)
    on delete cascade 
)

/*以上创建了外键表食品和主表供应商,在食品表的外键约束中,定义了on delete cascade 子句,规定了主表相关数据被删除时自动执行级联删除*/

增加约束:Add

alter table 表名称
drop column 
列名称[,n……]
*有约束的列需要先删除约束才能删除列,删除多列使用逗号隔开

如果插入的新数据不满足之前已经存在的约束,可临时禁用约束,直到数据插入完成后再启用。

图片 44图片 45

alter table 表名称
add constraint 自定约束标识名称
default 默认值 for 列名称

图片 46图片 47

 

切换数据库

与on类似,不过只有当表中定义了text、image数据类型的列时,这个子句才是可用的,它会把BLOB数据移动到不同的文件组中,通过把数据库分成多个文件并将这些文件存储在不同的物理磁盘上可以重大提升性能I/O。 

create table 成绩
(
    ID int not null identity primary key,
    sex varchar(2) not null check(sex ='男' or sex='女'),
        english int not null check(english>60),
)