7.5MySQL的约束控制

数据库的完整性约束

(1)实体完整性

强制表中标识符列或主键的完整性
例如：主键约束、惟一约束

(2)参照完整性(引用完整性)

在删除和输入记录时，引用完整性保持表之间已定义的关系，引用完整性确保键值在所有表中一致。

(3)用户自定义完整性

用户自己定义的业务规则,包含域完整性。包括类型、格式、取值范围等

---

MySQL中常用约束

1.主键约束(PRIMARY KEY)

用于定义基本表的主键，起唯一标识作用(不能为NULL，不能重复)

• 用于列约束的定义([[#1 单个主键|单个列作为主键]])

字段名 数据类型 [其它约束] PRIMARY KEY

• 用于表约束的定义([[#2 多个主键|多个列组合为主键]])

PRIMARY KEY (列名[,列名])

(1)单个主键

[例7-18]建立一个Stu表，定义Sno为Stu的主键。

CREATE TABLE Stu
Sno char(6) PRIMARY KEY,
Sname char(8),
Ssex char(2),
Zno varchar(20));

or

CREATE TABLE Stu
Sno char(6) NOT NULL,
Sname char(8),
Ssex char(2),
Zno varchar(20),
PRIMARY KEY(Sno)
);

(2)多个主键

[例7-20]建立一个SC表，主键由Sno和Cno共同构成。

create table sc
sno char(12),
cno char(10) , 
score int,
primary key(sno,cno)
);

(3)给已有表添加主键约束

ALTER TABLE Stu
ADD PRIMARY KEY (Sno);

(4)删除已有主键约束

ALTER TABLE Stu
DROP PRIMARY KEY;

---

2.外键约束(FOREIGN KEY)

子表的外键引用父表的主键

[FOREIGN KEY (子表列名表)] REFERENCES 父表名(列名表)
[ on delete [ cascade | restrict | set null | no action ]]
[ on update [ cascade | restrict | set null | no action ]]

cascade：级联删除，就是删除主键表的同时，外键表同时删除。

假如院系表中的某个院系被删除了，那么在学生表中要想查询这个被删除的院系号所对应的院信息就会报错，因为已经不存在这个系了，所以，删除院系表（主键表）时必须删除其他与之关联的表，这里就说明了外键的作用，保持数据的一致性、完整性。当然反过来讲，你删除学生表中的记录，并不影响院系表中的数据，你查询院系号也能正确查询。所以删除外键表中的数据并不影响主键表。

set null：当取值为Set Null时，则当在主键表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null

no action、restrict：默认的；意思为拒绝操作，如果父表中的主键被子表引用时，删除父表主键时，会拒绝操作

NO ACTION和RESTRICT的区别：只有在及个别的情况下会导致区别，前者是在其他约束的动作之后执行，后者具有最高的优先权执行。

例题：建立SC表，定义Sno为外键，参照S表的sno

CREATE TABLE SC
(Sno char(6)，
 Cno char(3),
 Score Int,
PRIMARY KEY (Sno, Cno),
FOREIGN key (Sno) REFERENCES S(Sno)
 )ENGINE=InnoDB;

要注意：MyISAM存储引擎不支持外键

---

3.非空约束(not null)

注意：NULL不同于0、""、空格、'NULL'

(1)语法

属性名 数据类型 [NULL | NOT NULL]

(2)例题

建立一个S表，对Sname字段设置为非空

CREATE TABLE S
(Sno char(6),
Sname char(8) NOT NULL,
Ssex char(2),
Sbirth date);

(3)给已存在表的某个属性添加非空约束

如果在一个已经创建的Stu表中对Sname字段设置非空约束
使用modify修改，不能使用ADD增加

ALTER TABLE S
MODIFY Sname char(8) not null;

---

4.惟一约束(UNIQUE)

指明基本表在某一列或多个列组合的取值必须惟一
在建立UNIQUE约束时，需要考虑以下几个因素:

• 一个表中可以允许有多个UNIQUE约束。
• 满足UNIQUE约束的字段允许取NULL值。
• 可以把UNIQUE约束定义在多个字段上。
• UNIQUE约束自动在指定字段上创建唯一索引,如果删除唯一约束，可直接删除该索引

(1)单个属性

[例7-26]建立一个Dept表，定义Dname为惟一约束

CREATE TABLE Dept
(Dno char(6)PRIMARY KEY,
 Dname,varchar(20) UNIQUE,
 Tel char(13),
 Address varchar (20)
 );

(2)多个属性

[例]建立一个SC表，定义Sno+Cno为惟一键。

CREATE TABLE SC
(Sno char(6),
Cno char(8),
Score int,
UNIQUE (Sno,Cno)
);

(3)表已存在修改

ALTER TABEL S
MODIFY SNO char(20) NOT NULL UNIQUE;

(4)PRIMARY KEY与UNIQUE的区别

• 一个基本表中只能有一个PRIMARY KEY，但可以有多个UNIQUE
• 对于指定为PRIMARY KEY的一个列或多个列的组合，其中任何一个列都不能出现NULL值，而对于UNIQUE所约束的惟一键，则允许为NULL

---

5.默认约束(DEFAULT)

为某个字段添加默认值

(1)语法格式：

字段名 数据类型 [其它约束] DEFAULT 默认值

(2)例题

创建课程C表，其选课人数上限up_limit字段设置默认值约束,且默认值为整数100人。

CREATE TABLE C
(Cno char(8),
 Cname varchar(20),
 up_limit int default 100
);

---

6.自增约束(AUTO_INCREMENT)

• 是自动扩展的完整性约束，向表中插入新纪录时，字段的值会自动生成唯一的ID。
• 一个表只能建一个该约束，必须为整型。

(1)语法格式

列名 数据类型 AUTO_INCREMENT

(2)例题

创建系部表Dept表，要求系部编号自动增加。

CREATE TABLE Dept1
(Dno INT PRIMARY KEY AUTO_INCREMENT,
 Dname varchar(20) UNIQUE,
 Tel char(13),
 Address varchar (20)
);

这种递增的列必须为主键，AUTO_INCREMENT必须配合PRIMARY KEY

---

7.可以给约束命名(constraint)

(1)默认命名

若未命名，创建约束时，系统会自动给约束命名
可以用语句查看创建表时所用的所用命令包括约束

show create table <表名>

(2)自定义命名

[例3-12]建立含有给约束命名SC表。

CREATE TABLE SC1
(Sno char(8),
 Cno char(4),
 Grade INT default 100 null,
 Primary key(sno,cno),
 constraint s_sc_fk
 Foreign key (sno) references Stu(sno)
 ) ;

---

7.删除约束

(1)删除主键

alter table test 
drop primary key;

(2)删除外键约束

删除名为test_ibfk_1的外键约束

alter table test 
drop foreign key test_ibfk_1;

(3)删除惟一约束

alter table tableName 
drop index key_name;

(4)删除默认约束

ALTER TABLE <数据表名> 
CHANGE COLUMN <字段名> <字段名> <数据类型> DEFAULT NULL;