SQL基础之外键限制,操作语句

第少年老成创制测量试验表

操作语句(Operation Statement)

mysql--外键(froeign key),mysql--froeign

设若二个实体的某些字段指向另一个实体的主键,就称为外键
被针对的实体,称之为主实体(主表),也叫父实体(父表)。
担任指向的实业,称之为从实体(从表),也叫子实体(子表)

 图片 1

作用:
用以节制处于关系内的实业
充实子表记录时,是不是有与之相应的父表记录

设若主表没有有关的笔录,从表无法插入

 图片 2

先插入主表数据后再插入从表数据:

 图片 3

 

在剔除或然更新主表记录时,从表应该怎么管理相关的记录

安装级联操作:
在主表数据发生更动时,与之提到的从表数据应该什么处理
    使用主要字:
     on update
     on delete
    来标识
允许的级联合浮动作:
cascade关联操作,假设主表被更新或删除,从表也会施行相应的操作
set null,表示从表数据不对准主表任何笔录
restrict:谢绝主表的相干操作

alter table t_student add foreign key (class_id) references t_class (class_id)
on delete set null; # 在剔除此而外键时,将从表的外键值设置为null

订正外键:
先删除这几个表的外键,然后再扩大
alter table tb_name drop froeign key 外键名称
外键名称在创建外键时方可自定义名称,假若不自定义,会依据mysql自动生成贰个名称
show create table tb_name;
 图片 4

alter table t_student drop foreign key t_student_ibfk_1;

 图片 5

 

除去外键后不会对表中的数额产生任何影响,更动的只是对表的黄金年代种限制

alter table t_student add foreign key (class_id) references t_class (class_id)
on delete set null; # 在剔除此而外键时,将从表的外键值设置为null

 

 图片 6

 图片 7

注:on delete 与on update 能够何况现身,
 但在on delete 或on update 后不能够并且现身cascade、set null、restrict,只可以有二个
关于restrict的外键限制此处未有写,与任何四个相近
 restrict:拒绝主表的连带操作,在主表更新只怕去除数据时,在从表中存在与主表主键相关的数额,则不容许对主表数据开展立异可能去除

在不安装任何级联关系约束时,主表暗中认可会是restrict

 图片 8

在restrict限定下,固然想要删除主表数据,除了可以去除未有与子表数据有关的多寡外,
能够先改过子表中的外键(修改时,外键必需也要涉及到主表的主键,不然不能够改良成功)
 图片 9

 

也能够先删除与想要删除的主表数据的子表数据,再去删除此条主表数据

 图片 10

 

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);

操作数据库

mysql增添外键

包含外健的表,要先建把外健作为主贱的表,然后本领建你相当满含外健的标。
create table A( aaa int primary key,...)

create table B(
bbb int primary key,
aaa int,
... ...
foreign key (aaa) references A (aaa) on delete ... on update ...

)
要注意 B中的aaa类型 和 名字要和A中的完全同样,还会有那三个空格你最佳有,即使一时没什么,可是一时就反常。

 

虚构如下事关

缔造数据库

mysql主外键关系

-- 创设测量检验主表. ID 是主键.
CREATE TABLE test_main (
id INT,
value VARCHAR(10),
PRIMARY KEY(id)
);

-- 创立测验子表.
CREATE TABLE test_sub (
id INT,
main_id INT,
value VARCHAR(10),
PRIMARY KEY(id)
);

私下认可外键限定措施
mysql> ALTER TABLE test_sub
-> ADD CONSTRAINT main_id_cons
-> FOREIGN KEY (main_id)
-> REFERENCES test_main(id);
-> //
Query OK, 2 rows affected (0.17 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> DELETE FROM
-> test_main
-> WHERE
-> id = 1;
-> //
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constrai
nt fails (`test`.`test_sub`, CONSTRAINT `main_id_cons` FOREIGN KEY (`main_id`) R
EFERENCES `test_main` (`id`))

MySQL使用上边那几个讲话删除此之外键约束
ALTER TABLE test_sub DROP FOREIGN KEY main_id_cons;
参谋资料:hi.baidu.com/...3.html  

key),mysql--froeign 假诺三个实体的某部字段指向另八个实体的主键,就叫做外键 被针没错实体,称之为主实体(主表),...

图片 11

关键字:create database

test02表中的id11依附于test0第11中学的id1,由此为test02制造外键

用于成立各个数据库对象(数据库、表、触发器、存款和储蓄进程等) 格式如:
create <对象类型> <对象名称>

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

图片 12图片 13

小心:test01表中的id1亟须为主键或许唯一索引,不然无法创建基于id1的外键。

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

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

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

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

 

View Code

创造外键之后,我们将开掘无法在test0第22中学输入不在test01的id1范围内的数目

图片 14图片 15

图片 16图片 17

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 )
insert into test02 values(1,1);

View Code

View Code

改革数据库

图片 18图片 19

关键字:alter 

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

充实援救文件

View Code

图片 20图片 21

若果在成立外键在此之前就已经在test0第22中学有了不在test01的id1范围内的数码,则开创外键会退步

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

图片 22图片 23

View Code

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);

扩张日志文件

View Code

图片 24图片 25

图片 26图片 27

alter database 数据库名称
add log file(name='',size=,maxsize=,filewgrowth=,filename=) , (n……)
多个日志文件之间用括号括起来并使用逗号分开即可
消息 547,级别 16,状态 0,第 1 行
ALTER TABLE 语句与 FOREIGN KEY 约束"fk_id11"冲突。该冲突发生于数据库"test",表"dbo.test01", column 'id1'。

View Code

View Code

追Gavin件组

那儿能够通过with nocheck筛选,强制跳过现成数量检查

图片 28图片 29

alter table test02 with nocheck
  add constraint fk_id11
  foreign key(id11) references test01(id1);
alter database 数据库名称
add filegroup 组名

虽说在test01表中id1设置为了主键,不容许null,可是在test02表中的id2能够允许null值

View Code

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

除去数据库

 

能够在别的随便三个数据库中运用它,但不可能在要刨除的数据库中央银行使这一个命令

当大家从test01进行删减或改良数据操作的时候,如若在test02表中也可能有连带数据则会报错,拒却操作;

图片 30图片 31

图片 32图片 33

drop database 数据库名称
insert into test01 values(2,1);
insert into test02 values(2,1);
update test01 set id1=3 where id1=2;

View Code

View Code

剔除文件

图片 34图片 35

图片 36图片 37

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

View Code

View Code

那个时候大家得以由此级联操作来一起删除或改换几个表中的数目。

删除组

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;

图片 38图片 39

如此那般test02表中的数据也会相应改正

alter database 数据库名称
remove filegroup 组名

 

View Code

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

订正文件

内部cascade是做相仿纠正;set null是test02表中相应数据修正为null;set default则是呼应数额校订为暗中同意值。

图片 40图片 41

 

alter database 数据库名称
modify file(name=,size=,maxsize=,filwgrowth=,filenamme=)
* 修改时 修改的size只能改大,不能改小。

View Code

校订数据库名称

图片 42图片 43

alter database 数据库名称
modify name=重命名

View Code

修改文件组名称

图片 44图片 45

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

View Code

切换数据库

图片 46图片 47

use 数据库名称

View Code

操作表

创建表

关键字:create table 

图片 48图片 49

craete table [ 数据库名称 [ .数据库所有者 ] ] 表名称
(
    列名称 数据类型 [ null [ not null ] ]  [ identity(种子,增量) ] [primarykeyName primary key]
    [ rowGuidcol ] [ collate 顺序名 ] [ 列约束 ]
    [列名称 as 计算表达式]
    [ 表约束 ]
) [ on [ 文件组 ] [ default ] ]  [ textImage_on [ 文件组 ] [ default ] ]

View Code

图片 50图片 51

create table 产品
(
    编号 int not null ,
    产品名称 nvarchar(2) ,
    数量 int,
    单价 int,
    总价 as 数量*单价
)

create table 新闻
{
    编号 int identity(1,1) not null,
    标题 varchar not null,
    内容 text not null,
    时间 datatime
}

View Code

给表定义列时,限定、数据类型、标记、主键无顺序限制,可随意写,比方: 

图片 52图片 53

create table Teacher(
    教师编号 int identity primary key not null
)
--也可以写成
create table Teacher(
    教师编号 not null int primary key identity 
)

View Code

 identity

系统为该列自动生成的唯生龙活虎性的标记数据,种子平日从1起来,增量默感到1,可以改换种子和增量。标识列不容许客户insert数据,除非动用set identity_insert=on来开启自定义插入标识,插入标志操作只扶持insert不援助update。获取近来一回系统生成的identity值:select @@identity as 列别称。

not for replication 

复制

rowGUIDcol

GUI全局唯生龙活虎标志符,此首要字用于那样额景况,举个例子当要把当前数据库的某张表的多少复制到另四个数据库的表时,因为两张表都有identity自动标记列,那时若是当前表有唯大器晚成标志100,另一张表也可以有其后生可畏标记,那么就能现出错误,那时能够行使rowGUIDcol来消灭这样的主题素材。

collate

多少排序、区分朗朗上口写、区分重音。

列名 as 计算表达式

本条设定能够让表自动生成七个列,此列的数据是基于你的总计表明式来扭转的。

列节制和表节制

列限制即把设置的羁绊放在列名-数据类型之后,表节制放在创制表的语句括号内的末段地方。

on [ 文件组 ] [ default ]

设若不设置on,或值为default,默许将把表关联到主文件组里,不然按自定义的安装将表关联到此外文件组。

textImage_on [ 文件组 ] [ default ]

与on相近,可是只有当表中定义了text、image数据类型的列时,这几个子句才是可用的,它会把BLOB数据移动到区别的文件组中,通过把数据库分成多个公文并将这一个文件存储在分化的情理磁盘上能够着重进级质量I/O。 

重命名表

图片 54图片 55

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

View Code

删除表

二次可删四个表 使用逗号隔离

图片 56图片 57

drop table 表名,表名2,……

View Code

更改表 - 为表扩充列

图片 58图片 59

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

alter table Article
add body varchar(500)

View Code

校正表 - 为表删除列

图片 60图片 61

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

View Code

改革表 - 为表修改列

图片 62图片 63

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

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

View Code

约束

自律指通过节制字段中的数据来保险数据的完整性,即为列字段钦命一个封锁法则,使其只好录入钦点法规的多少。

主键限定:primary key

主键限定在一个表中能够定义叁个/或多个列(但运用视图情势定义时一定要定义一个主键限制列,最棒只定义三个)。被定义了主键节制的列不容许有空值,所以必需钦定值为not null。被定为主键节制的列具备唯黄金时代能够代表它所在行的大器晚成组数据的本领。

图片 64图片 65

create table 表名
(
    列名 数据类型 是否可空 identity(种子,增量) 自定义的主键名 primary key,
    ……
)


create table 成绩
(
    ID int not null identity(1,1) constraint pk_ID primary key,
    英语 int not null,
    数学 int not null,
    总分 as 英语 数学
)

View Code

外键限制 Foreign key

接收外键约束可使四个表之间创制起联系,三个表之间个中必有一个列的数额是千篇生机勃勃律的。比如学员品级表和学子战表表,它们都是学子ID作为联系。被foreign key节制的表称为主

表的外键表,references指向的表称为主表。 

规则

1.主键表不能够去除,因为有外键表要求援用它,除非先删除此之外键表的外键约束,或许选取级联删除。

2.insert多少到外键表里,必需确认保证外键表中的外键列的多少是主键表对应的列里已经部分数据。

表自援引

一张表能够引用它自个儿,可正如概念:

图片 66图片 67

create table 员工
(
    员工ID int identity not null ,
    名称 varchar(20) not null,
    经理ID int null foreign key references 员工 ( 员工ID )/*外键列允许null*/
)

View Code

级联操作

delete或update主表某行数据,是或不是还要delete或update外键表相关联的数码。必得在开创表、定义外键限制时设置是或不是可级联操作,假若定义为on delete(update)cascade,表示同意

级联操作自动删除或更新相关联的表的数码。假设定义为on delete(update)action,表示不容许删除或更新主表的多少。

图片 68图片 69

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 子句,规定了主表相关数据被删除时自动执行级联删除*/

View Code

反省节制:Check

Check约束能够在钦赐的列上检查该列的值是还是不是满足Check的原则。

图片 70图片 71

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),
)

View Code

唯生龙活虎节制:Unique

nique能够出今后七个列上,它必要被束缚的列的数码颇负唯生龙活虎性,无法再度,如若该列允许null值 相同的时间又选用了Unique节制,那么null值可现身最多三回。

图片 72图片 73

create table 成绩
(
      ID int not null identity primary key,
      name varchar(10) not null unique 
)

View Code

nique也能够况兼节制多个列,若是为五个列钦点了同三个名称的nique节制,那么新插入的多少的四个有着同一个羁绊的字段就不可能与已经存在的笔录的相应的列值相像。以下代码中cell_1、cell_3、cell_3四个列具有同贰个束缚,假诺有一条存在的记录对应了这些列的值分别是金蕉、凤梨、荔果,那么新插入的数目就不能是西贡蕉、凤梨、丹荔,因为她们被封锁为不能够同不常候相等。

 

 

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

 

 

 

暗许限定:Default

假定不插入数据到被dedault约束的列,你能够给定七个暗中同意值。

图片 74图片 75

create table 成绩
(
    ID int not null identity primary key,
    name varchar(10) not null unique ,
    english int not null check(english>60),    
    midifyTime smalldatetime default getdate()
)

insert into 成绩(name,english)values('lily',100)
insert into 成绩(name,english,midifyTime )values('leo',100,'2012-12-12')

View Code

追加约束:Add

表已经济建设好,之后想要改进表为表扩张节制,可alter表并add节制。

日增主键节制

图片 76图片 77

alter table 表名称
add constraint 自定约束标识名称
primary key(列名称)

View Code

充实外键节制

图片 78图片 79

alter table 表名称
add constraint 自定约束标识名称
foreign key (列名称) references 被引用的表名称(列名称)

View Code

追加检查约束

图片 80图片 81

alter table 表名称
add constraint 自定约束标识名称
check (列名称 条件)

View Code

扩大唯大器晚成限定

图片 82图片 83

alter table 表名称
add constraint 自定约束标识名称
unique (列名称)

View Code

追加私下认可节制

图片 84图片 85

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

View Code

旧数据忽视新限定:with nocheck

在为某些列创造节制时,该列恐怕在此之前未有限定而且意气风发度有了数码,那时你想约束那个列。比方联系表的对讲机一列,早先填写的是'0871-00-78-90',今后你想要限定为这种格

式:'9028392'。但因为从前的旧数据不满足以往的约束原则,所以约束将无法创建,那时能够使用限制忽视语句来忽视旧数据,只限制新录入的数量。 

图片 86图片 87

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

View Code

新数据禁止使用旧限定

大器晚成经插入的新数据不满足从前曾经存在的封锁,可一时禁止使用节制,直到数据插入达成后再启用。

图片 88图片 89

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

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

View Code

 

 Microsoft SQL - SQL SE普拉多VESportage学习总目录

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:SQL基础之外键限制,操作语句

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。