七、约束

七、约束

约束是一种限制，它通过限制表中的数据，来确保数据的完整性和唯一性。使用约束来限定表中的数据很多情况下是很有必要的。在 MySQL 中，约束是指对表中数据的一种约束，能够帮助数据库管理员更好地管理数据库，并且能够确保数据库中数据的正确性和有效性。例如，在数据表中存放年龄的值时，如果存入 200、300 这些无效的值就毫无意义了。因此，使用约束来限定表中的数据范围是很有必要的。

在 MySQL 中，主要支持以下 6 种约束：

7.1、主键约束

（1）主键约束

主键约束是使用最频繁的约束。在设计数据表时，一般情况下，都会要求表中设置一个主键。主键是表的一个特殊字段，该字段能唯一标识该表中的每条信息。

-- 方式1

CREATE TABLE t1(
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20)
);

-- 方式2

CREATE TABLE t2(
  id INT NOT NULL,
  name VARCHAR(20)
);

1、一张表中最多只能有一个主键

2、表中如果没有设置主键，默认设置NOT NULL的字段为主键；此外，表中如果有多个NOT NULL的字段，则按顺序将第一个设置NOT NULL的字段设为主键。所以主键一定是非空且唯一，但非空且唯一的字段不一定是主键。

3、主键类型不一定必须是整型

（2）添加删除主键

 alter table t2 add primary key(id);
 alter table t2 drop primary key;
 alter table t2 add primary key(name);

（3）复合(联合)主键
所谓的复合主键 就是指你表的主键含有一个以上的字段。

所谓的联合主键，就是这个主键是由一张表中多个字段组成的。

比如，设置学生选课数据表时，使用学生编号做主键还是用课程编号做主键呢？如果用学生编号做主键，那么一个学生就只能选择一门课程。如果用课程编号做主键，那么一门课程只能有一个学生来选。显然，这两种情况都是不符合实际情况的。

实际上设计学生选课表，要限定的是一个学生只能选择同一课程一次。因此，学生编号和课程编号可以放在一起共同作为主键，这也就是联合主键了。

-- ①创建时：

create table sc (
    studentid int,
    courseid int,
    score int,
primary key (studentid,courseid)
);        
-- ②修改时：
alter table tb_name add primary key (字段1,字段2,字段3);

(4) 主键自增约束
当主键定义为自增长后，这个主键的值就不再需要用户输入数据了，而由数据库系统根据定义自动赋值。每增加一条记录，主键会自动以相同的步长进行增长。

通过给字段添加AUTO_INCREMENT属性来实现主键自增长

 CREATE TABLE t1(
    id INT(4) PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(25) 
    );

7.2、唯一约束

唯一约束（Unique Key）是指所有记录中字段的值不能重复出现。例如，为 id 字段加上唯一性约束后，每条记录的 id 值都是唯一的，不能出现重复的情况。

（1）创建约束

例如，在用户信息表中，要避免表中的用户名重名，就可以把用户名列设置为唯一约束。

 CREATE TABLE user(
    id INT(11) PRIMARY KEY,
    name VARCHAR(22) UNIQUE
    );
    
INSERT user (id,name) values (1,"yuan"),(2,"rain");   
INSERT user (id,name) values (3,"alvin"),(4,"alvin");
-- ERROR 1062 (23000): Duplicate entry 'alvin' for key 'name'

（2）改表时添加删除唯一约束

ALTER TABLE <表名> DROP INDEX <唯一约束名>;  -- 删除唯一约束
ALTER TABLE <数据表名> ADD CONSTRAINT <唯一约束名> UNIQUE(<列名>);  -- 添加唯一约束

ALTER TABLE user  DROP INDEX name;
ALTER TABLE user ADD CONSTRAINT NAME_INDEX UNIQUE(name);

7.3、非空约束

非空约束用来约束表中的字段不能为空。比如，在用户信息表中，如果不添加用户名，那么这条用户信息就是无效的，这时就可以为用户名字段设置非空约束。

创建表时可以使用NOT NULL关键字设置非空约束

 CREATE TABLE user(
    id INT(11) PRIMARY KEY,
    name VARCHAR(22) UNIQUE NOT NULL
    );

添加和删除非空约束：

-- 删除非空约束
ALTER TABLE <数据表名>
CHANGE COLUMN <字段名> <字段名> <数据类型> NULL;
ALTER TABLE user CHANGE COLUMN name name varchar(32) NULL;
-- 添加非空约束
ALTER TABLE <数据表名>
CHANGE COLUMN <字段名> <字段名> <数据类型> NOT NULL;
ALTER TABLE user CHANGE COLUMN name name varchar(32) NOT NULL;

7.4、默认值约束

默认值约束用来约束当数据表中某个字段不输入值时，自动为其添加一个已经设置好的值。

创建表时可以使用DEFAULT关键字设置默认值约束

 CREATE TABLE stu(
    id INT(11) PRIMARY KEY,
    name VARCHAR(22) UNIQUE NOT NULL,
    gender VARCHAR(22) DEFAULT "male"
    );

添加和删除默认值：

-- 删除非空约束
ALTER TABLE <数据表名>
CHANGE COLUMN <字段名> <字段名> <数据类型> DEFAULT NULL;
ALTER TABLE stu CHANGE COLUMN gender gender varchar(32) DEFAULT NULL;
-- 添加非空约束
ALTER TABLE <数据表名>
CHANGE COLUMN <字段名> <数据类型> DEFAULT <默认值>;
ALTER TABLE stu CHANGE COLUMN gender gender varchar(32) DEFAULT "female";

7.5、外键约束

外键约束经常和主键约束一起使用，用来确保数据的一致性。

外键约束（FOREIGN KEY）是表的一个特殊字段，经常与主键约束一起使用。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。

外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。主表删除某条记录时，从表中与之对应的记录也必须有相应的改变。一个表可以有一个或多个外键，外键可以为空值，若不为空值，则每一个外键的值必须等于主表中主键的某个值。

比如上面的书籍管理案例，若删除一个清华出版社记录，没有任何影响，但是，书籍表中pub_id = 1 的记录出版社字段就没有意义了。

定义外键时，需要遵守下列规则：

• 主表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则主表与从表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。
• 必须为主表定义主键。
• 主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。
• 在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键。
• 外键中列的数目必须和主表的主键中列的数目相同。
• 外键中列的数据类型必须和主表主键中对应列的数据类型相同（非常重要）。

（1）创建表时设置外键约束

[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…]
REFERENCES <主表名> 主键列1 [，主键列2，…]

例如：

-- 书籍表
CREATE TABLE book(
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(32),
price DOUBLE(5,2),    
pub_id INT NOT NULL,
FOREIGN KEY(pub_id) REFERENCES publisher(id) ON DELETE CASCADE ON UPDATE CASCADE -- 建立外键约束    
)ENGINE=INNODB CHARSET=utf8;


-- 出版社表
CREATE TABLE publisher(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(32),
email VARCHAR(32),
addr VARCHAR(32)
)ENGINE=INNODB CHARSET=utf8;

-- 作者表
CREATE TABLE author(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(32) NOT NULL
)ENGINE=INNODB CHARSET=utf8;

-- 作者表和书籍表的多对多关系表
CREATE TABLE book2author(
id INT NOT NULL UNIQUE AUTO_INCREMENT,
author_id INT NOT NULL,
book_id INT NOT NULL,
FOREIGN KEY(author_id) REFERENCES author(id) ON UPDATE CASCADE ON DELETE CASCADE, -- 建立外键约束
FOREIGN KEY(book_id) REFERENCES book(id) ON UPDATE CASCADE ON DELETE CASCADE     -- 建立外键约束
)ENGINE=INNODB CHARSET=utf8;

（2）添加删除外键约束

-- 添加外键约束
ALTER TABLE <数据表名> ADD CONSTRAINT <外键名>
FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);

-- 删除外键约束
ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;
drop index 外键约束名 on<表名>; -- 同时将索引删除 

ALTER TABLE book ADD CONSTRAINT dep_fk
FOREIGN KEY(pub_id) REFERENCES publisher(id) ON DELETE CASCADE;
show create table book;
-- 尝试删除一个出版社记录
DELETE FROM publisher WHERE id=1;
/*
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`yuan`.`book`, CONSTRAINT `dep_fk` FOREIGN KEY (`pub_id`) REFERENCES `publisher` (`id`)) */
-- 删除外键约束
ALTER TABLE book DROP FOREIGN KEY dep_fk;
show index from book;
drop index dep_fk on book;
show create table book;

ALTER TABLE book ADD CONSTRAINT dep_fk FOREIGN KEY(pub_id) REFERENCES publisher(id) ON DELETE CASCADE; 


--  再次尝试删除一个出版社记录，此时就是级联删除了，所有book表中关联publisher表中id=1的记录都会级联删除
DELETE FROM publisher WHERE id=1;
select * from book;

（3）INNODB支持的ON语句

外键约束对子表的含义: 如果在主表中(比如dep)找不到候选键,则不允许在子表(比如emp)上进行insert/update

外键约束对父表的含义: 在主表上进行update/delete以更新或删除在子表中有一条或多条应匹配行的候选键时,父表的行为取决于：在定义子表的外键时指定的 – on update/on delete子句

FOREIGN KEY () REFERENCES () ON DELETE CASCADE;

-- (1) cascade
cascade方式 在父表上update/delete记录时，同步update/delete掉子表的匹配记录外键的级联删除：如果父表中的记录被删除，则子表中对应的记录自动被删除

-- (2) cascade     
set null方式 在父表上update/delete记录时，将子表上匹配记录的列设为null ; 要注意子表的外键列不能为not null

-- (3) Restrict
Restrict方式 :拒绝对父表进行删除更新操作(了解)

-- (4) No action
No action方式 在mysql中同Restrict,如果子表中有匹配的记录,则不允许对父表对应候选键 ; 进行update/delete操作（了解）