xml地图|网站地图|网站标签 [设为首页] [加入收藏]

操作语句,SQL基础之外键约束

必威betway 1必威betway 2

View Code

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中的完全一样,还有 那些空格你最好有,虽然有的时候没关系,但是有时候就有问题。

 

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

必威betway,textImage_on [ 文件组 ] [ default ]

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 如果一个实体的某个字段指向另一个实体的主键,就称为外键 被指向的实体,称之为主实体(主表),...

必威betway 3必威betway 4

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

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

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

如果一个实体的某个字段指向另一个实体的主键,就称为外键
被指向的实体,称之为主实体(主表),也叫父实体(父表)。
负责指向的实体,称之为从实体(从表),也叫子实体(子表)

 必威betway 5

作用:
用于约束处于关系内的实体
增加子表记录时,是否有与之对应的父表记录

如果主表没有相关的记录,从表不能插入

 必威betway 6

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

 必威betway 7

 

在删除或者更新主表记录时,从表应该如何处理相关的记录

设置级联操作:
在主表数据发生改变时,与之关联的从表数据应该如何处理
    使用关键字:
     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;
 必威betway 8

alter table t_student drop foreign key t_student_ibfk_1;

 必威betway 9

 

删除外键后不会对表中的数据造成任何影响,改变的只是对表的一种约束

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

 

 必威betway 10

 必威betway 11

注:on delete 与on update 可以同时出现,
 但在on delete 或on update 后不能同时出现cascade、set null、restrict,只能有一个
关于restrict的外键约束此处没有写,与其他两个一样
 restrict:拒绝主表的相关操作,在主表更新或者删除数据时,在从表中存在与主表主键相关的数据,则不允许对主表数据进行更新或者删除

在不设置任何级联关系约束时,主表默认会是restrict

 必威betway 12

在restrict约束下,如果想要删除主表数据,除了可以删除没有与子表数据相关的数据外,
可以先修改子表中的外键(修改时,外键必须也要关联到主表的主键,否则不能修改成功)
 必威betway 13

 

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

 必威betway 14

 

View Code

View Code

必威betway 15必威betway 16

删除文件

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

View Code

 

增加日志文件

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

修改表 - 为表增加列

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

必威betway 17必威betway 18

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

View Code

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

操作表

insert into test02 values(1,1);

nique可以出现在多个列上,它要求被约束的列的数据具有唯一性,不能重复,如果该列允许null值 同时又使用了Unique约束,那么null值可出现最多一次。

必威betway 19必威betway 20

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

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

 

View Code

必威betway 21必威betway 22

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

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

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

2.insert数据到外键表里,必须保证外键表中的外键列的数据是主键表对应的列里已经有的数据。

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

如果不插入数据到被dedault约束的列,你可以给定一个默认值。

必威betway 23必威betway 24

增加辅助文件

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

必威betway 25必威betway 26

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

列约束即把设置的约束放在列名-数据类型之后,表约束放在创建表的语句括号内的最后位置。

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

必威betway 27必威betway 28

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

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

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

 

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

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

关键字:create database

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

外键约束 Foreign key

 

View Code

 

必威betway 29必威betway 30

考虑如下关系

not for replication 

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

删除组

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

必威betway 31必威betway 32

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

主键约束在一个表中可以定义一个/或多个列(但使用视图模式定义时只能定义一个主键约束列,最好只定义一个)。被定义了主键约束的列不允许有空值,所以必须指定值为not null。被定为主键约束的列具有唯一可以代表它所在行的一组数据的能力。

必威betway 33

必威betway 34必威betway 35

View Code

增加文件组

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

alter table 表名称
add constraint 自定约束标识名称
foreign key (列名称) references 被引用的表名称(列名称)
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;
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 英语+数学
)
消息 547,级别 16,状态 0,第 1 行
ALTER TABLE 语句与 FOREIGN KEY 约束"fk_id11"冲突。该冲突发生于数据库"test",表"dbo.test01", column 'id1'。
craete table [ 数据库名称 [ .数据库所有者 ] ] 表名称
(
    列名称 数据类型 [ null [ not null ] ]  [ identity(种子,增量) ] [primarykeyName primary key]
    [ rowGuidcol ] [ collate 顺序名 ] [ 列约束 ]
    [列名称 as 计算表达式]
    [ 表约束 ]
) [ on [ 文件组 ] [ default ] ]  [ textImage_on [ 文件组 ] [ default ] ]

View Code

修改表 - 为表修改列

首先创建测试表

 Microsoft SQL - SQL SERVER学习总目录

View Code

必威betway 36必威betway 37

 

View Code

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

View Code

alter table Tab add constraint uq_fruit unique(cell_1,cell_2,cell_3)
alter database 数据库名称
modify name=重命名

唯一约束:Unique

修改文件

必威betway 38必威betway 39

View Code

View Code

必威betway 40必威betway 41

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

修改文件组名称

操作语句(Operation Statement)

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

View Code

use 数据库名称

必威betway 42必威betway 43

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

View Code

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 )

创建数据库

约束指通过限制字段中的数据来保证数据的完整性,即为列字段指定一个约束规则,使其只能录入指定规则的数据。

必威betway 44必威betway 45

必威betway 46必威betway 47

必威betway 48必威betway 49

增加检查约束

修改数据库

修改数据库名称

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

View Code

必威betway 50必威betway 51

必威betway 52必威betway 53

 

必威betway 54必威betway 55

修改表 - 为表删除列

View Code

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

必威betway 56必威betway 57

alter database 数据库名称
remove filegroup 组名

必威betway 58必威betway 59

约束

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

必威betway 60必威betway 61

alter table 联系
with nocheck/*不检查表里以前的所有数据*/
add constraint cn_phone/*添加约束*/
check( (len(phone) =7) and  (phone between 111111 and 9999999) )
alter table 表名称
add constraint 自定约束标识名称
check (列名称+条件)

必威betway 62必威betway 63

collate

alter database 数据库名称
remove file 文件逻辑名

必威betway 64必威betway 65

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

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

这个设定可以让表自动生成一个列,此列的数据是基于你的计算表达式来生成的。

必威betway 66必威betway 67

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

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

关键字:alter 

drop table 表名,表名2,……

操作数据库

关键字:create table 

View Code

主键约束:primary key

View Code

规则

新数据禁用旧约束

重命名表

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

必威betway 68必威betway 69

必威betway 70必威betway 71

检查约束:Check

创建表

View Code

alter table 表名称
add constraint 自定约束标识名称
default 默认值 for 列名称
alter database 数据库名称
modify filegroup 存在的组名
name=新的组名

nique也可以同时约束多个列,如果为多个列指定了同一个名称的nique约束,那么新插入的数据的三个具备同一个约束的字段就不能与已经存在的记录的对应的列值相同。以下代码中cell_1、cell_3、cell_3三个列具有同一个约束,假设有一条存在的记录对应了这三个列的值分别是香蕉、菠萝、荔枝,那么新插入的数据就不能是香蕉、菠萝、荔枝,因为他们被约束为不能同时相等。

View Code

View Code

增加唯一约束

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

View Code

on [ 文件组 ] [ default ]

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

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

增加约束:Add

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

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

必威betway 72必威betway 73

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

表自引用

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

必威betway 74必威betway 75

复制

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

增加默认约束

View Code

列名 as 计算表达式

表已经建好,之后想要修改表为表增加约束,可alter表并add约束。

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

View Code

View Code

必威betway 76必威betway 77

删除表

View Code

View Code

删除数据库

drop database 数据库名称

View Code

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

View Code

View Code

View Code

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

必威betway 78必威betway 79

rowGUIDcol

View Code

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

必威betway 80必威betway 81

必威betway 82必威betway 83

必威betway 84必威betway 85

 

默认约束:Default

必威betway 86必威betway 87

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

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

列约束和表约束

使用外键约束可使多个表之间建立起联系,多个表之间其中必有一个列的数据是相同的。比如学员等级表和学员成绩表,它们都以学员ID作为联系。被foreign key约束的表称为主

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

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

增加主键约束

 

 

必威betway 88必威betway 89

切换数据库

旧数据忽略新约束:with nocheck

 identity

View Code

增加外键约束

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

alter table Article
add body varchar(500)

本文由必威发布于数据库,转载请注明出处:操作语句,SQL基础之外键约束