MySQL之约束

通常数据库约束有五类：

1.not null：非空约束，指定某列不为空；

2.unique：唯一性约束，指定某列或者某几列的组合值在数据表中的唯一性（不重复）；

3.primary key：主键约束，指定某列数据不重复；

4.foreign key：外键约束，指定某列关联其他表的列；

5.check：检查约束，根据表达式检验数据是否符合规则；

注意：MySQL不支持check约束，使用check约束不报错，但没效果；

约束主要完成对数据的检验，保证数据库数据的完整性；如果有相互依赖数据，保证该数据不被删除；

MySQL的约束保存在information_schema数据库的table_constraints表中，可通过该表查看约束信息。

1.NOT NULL：

CREATE TABLE `user2`(`user_name` VARCHAR(20) NOT NULL,`age` INT(3) NOT NULL,`mental_age` INT(3) NOT NULL,`salary` DECIMAL(5,2) NOT NULL,`birthday` DATE NOT NULL,`address` VARCHAR(200),`remark` VARCHAR(100));-- show CREATE TABLE `user2`;CREATE TABLE `user2` (  `user_name` VARCHAR(20) NOT NULL,  `age` INT(3) NOT NULL,  `mental_age` INT(3) NOT NULL,  `salary` DECIMAL(5,2) NOT NULL,  `birthday` DATE NOT NULL,  `address` VARCHAR(200) DEFAULT NULL,  `remark` VARCHAR(100) DEFAULT NULL) ENGINE=INNODB DEFAULT CHARSET=utf8;

增加NOT NULL约束：

1.在创建表时指定（如上）；

2.alter table时增加字段not null属性；

数值型的字段not null，则必须指定默认值；否则添加数据时，否则报错 Error code:1364  Field 'age' doesn't have a default value（字段类型后设置默认值，并不是插入数据NOT NULL就行）

ALTER TABLE `user2` MODIFY `age` INT(3) DEFAULT 0 NOT NULL;ALTER TABLE `user2` MODIFY `mental_age` INT(3) DEFAULT 0 NOT NULL;ALTER TABLE `user2` MODIFY `salary` DECIMAL(5,2) DEFAULT 0 NOT NULL;

所有类型的值都可以为NULL，未设置NOT NULL约束的字段，默认值都是NULL

int、float类型和空字符串都不等于null；

删除NOT NULL约束：

ALTER TABLE `user2` MODIFY age INT(3)

2. UNIQUE

新增唯一性约束（UNIQUE）

CREATE TABLE `user3`(`user_name` VARCHAR(20) UNIQUE,`age` INT(3) UNIQUE,`mental_age` INT(3),`salary` DECIMAL(5,2),`birthday` DATE,`address` VARCHAR(200),`remark` VARCHAR(100)  NOT NULL,CONSTRAINT UNIQUE(remark),CONSTRAINT UNIQUE(age,mental_age),CONSTRAINT UNIQUE(mental_age,salary));-- SHOW CREATE TABLE `user3`;CREATE TABLE `user3` (  `user_name` VARCHAR(20) DEFAULT NULL,  `age` INT(3) DEFAULT NULL,  `mental_age` INT(3) DEFAULT NULL,  `salary` DECIMAL(5,2) DEFAULT NULL,  `birthday` DATE DEFAULT NULL,  `address` VARCHAR(200) DEFAULT NULL,  `remark` VARCHAR(100) NOT NULL,  UNIQUE KEY `remark` (`remark`),  UNIQUE KEY `user_name` (`user_name`),  UNIQUE KEY `age` (`age`),  UNIQUE KEY `age_2` (`age`,`mental_age`),  UNIQUE KEY `mental_age` (`mental_age`,`salary`)) ENGINE=INNODB DEFAULT CHARSET=utf8

唯一性约束的列或者组合的列值不能重复，但可以为多个NULL；

如果不给唯一约束名称，就默认和列同名（组合列和第一个列同名，该列参与多个UNIQUE则会添加_下标区分）；

MySQL会给唯一约束的列上默认创建一个唯一索引；

如果列或者组合列的值存在重复，则无法为其添加UNIQUE约束；

ALTER TABLE `user3` ADD UNIQUE(`salary`);-- 只适用于给单列增加UNIQUE

下面的方式可为组合列增加UNIQUE

ALTER TABLE `user3` MODIFY `salary` DECIMAL(5,2) UNIQUE;

删除唯一性约束：

ALTER TABLE `user3` DROP INDEX salary

3. PRIMARY KEY

主键相当于NOT NULL + UNIQUE；一张表只能有一个主键；

CREATE TABLE `user4`(`id` INT PRIMARY KEY,`user_name` VARCHAR(20),`age` INT(3),`birthday` DATE,`address` VARCHAR(200),`remark` VARCHAR(100));-- SHOW CREATE TABLE `user4`;CREATE TABLE `user4` (  `id` INT(11) NOT NULL,  `user_name` VARCHAR(20) DEFAULT NULL,  `age` INT(3) DEFAULT NULL,  `birthday` DATE DEFAULT NULL,  `address` VARCHAR(200) DEFAULT NULL,  `remark` VARCHAR(100) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=INNODB DEFAULT CHARSET=utf8

组合模式：

CREATE TABLE `user5`(`id` INT,`user_name` VARCHAR(20),`age` INT(3),`birthday` DATE,`address` VARCHAR(200),`remark` VARCHAR(100),CONSTRAINT PRIMARY KEY(id,user_name));-- show create table `user5`;CREATE TABLE `user5` (  `id` INT(11) NOT NULL DEFAULT '0',  `user_name` VARCHAR(20) NOT NULL DEFAULT '',  `age` INT(3) DEFAULT NULL,  `birthday` DATE DEFAULT NULL,  `address` VARCHAR(200) DEFAULT NULL,  `remark` VARCHAR(100) DEFAULT NULL,  PRIMARY KEY (`id`,`user_name`)) ENGINE=INNODB DEFAULT CHARSET=utf8

删除主键：

ALTER TABLE `user4` DROP PRIMARY KEY;

增加主键：

也可以在新增时增加主键，见上面SQL；

ALTER TABLE `user4` ADD PRIMARY KEY(user_name);-- 主键并非只能是int类型，ADD 和 PRIMARY KEY之间可以增加CONSTRAINT关键字

组合主键：

见于本文的`class_02`表;

修改列为主键：

alter table `user4` modify `user_name` varchar(20) primary key;

4.FOREIGN KEY

主表：

CREATE TABLE `class_01`(id INT PRIMARY KEY AUTO_INCREMENT,class_name VARCHAR(20));

从表：

CREATE TABLE `student_01`(student_id INT,student_name VARCHAR(20),class_id INT NOT NULL REFERENCES class_01(id));

-- show create table `student_01`;CREATE TABLE `student_01` (  `student_id` INT(11) DEFAULT NULL,  `student_name` VARCHAR(20) DEFAULT NULL,  `class_id` INT(11) NOT NULL) ENGINE=INNODB DEFAULT CHARSET=utf8

由student_01表可见，这种方式并未创建外键；

在表内新建外键：

CREATE TABLE `student_02`(student_id INT,student_name VARCHAR(20),class_id INT,CONSTRAINT FOREIGN KEY(class_id) REFERENCES class_01(id));-- show create table `student_02`;CREATE TABLE `student_02` (  `student_id` INT(11) DEFAULT NULL,  `student_name` VARCHAR(20) DEFAULT NULL,  `class_id` INT(11) DEFAULT NULL,  KEY `class_id` (`class_id`),  CONSTRAINT `student_02_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class_01` (`id`)) ENGINE=INNODB DEFAULT CHARSET=utf8

根据`student_02`的外键列可知外键值仍可为空，如果不允许为空，需要自己给`class_id`列增加NOT NULL；

注意：外键约束的参照列，在主表中引用的只能是主键或唯一键约束的列，假定引用的主表列不是唯一的记录， 那么从表引用的数据就不确定记录的位置，则无法创建外键。

在表外新增外键：

ALTER TABLE `student_02` ADD CONSTRAINT FOREIGN KEY(`class_id`) REFERENCES `class_01`(`id`);

增加外键时如果不指定名称，数据库会默认给一个CONSTRAINT名称；

删除外键：

ALTER TABLE `student_02` DROP FOREIGN KEY `student_02_ibfk_1`;

因一张表可有多个外键，删除外键要根据外键名称删除；

多外键：

CREATE TABLE `teacher_01`(teacher_id INT PRIMARY KEY AUTO_INCREMENT,teacher_name VARCHAR(20));CREATE TABLE `student_03`(student_id INT,student_name VARCHAR(20),class_id INT,teacher_id INT,CONSTRAINT `student_03_fk_1` FOREIGN KEY(class_id) REFERENCES class_01(id),CONSTRAINT `student_03_fk_2` FOREIGN KEY(teacher_id) REFERENCES teacher_01(teacher_id));-- show create table `student_03`;CREATE TABLE `student_03` (  `student_id` INT(11) DEFAULT NULL,  `student_name` VARCHAR(20) DEFAULT NULL,  `class_id` INT(11) DEFAULT NULL,  `teacher_id` INT(11) DEFAULT NULL,  KEY `student_03_fk_1` (`class_id`),  KEY `student_03_fk_2` (`teacher_id`),  CONSTRAINT `student_03_fk_1` FOREIGN KEY (`class_id`) REFERENCES `class_01` (`id`),  CONSTRAINT `student_03_fk_2` FOREIGN KEY (`teacher_id`) REFERENCES `teacher_01` (`teacher_id`)) ENGINE=INNODB DEFAULT CHARSET=utf8

多列组合外键：

CREATE TABLE `class_02`(id INT AUTO_INCREMENT,class_name VARCHAR(20),PRIMARY KEY(id,class_name));CREATE TABLE `student_04`(student_id INT,student_name VARCHAR(20),class_id INT,class_name VARCHAR(20));ALTER TABLE `student_04` ADD CONSTRAINT FOREIGN KEY(class_id,class_name) REFERENCES class_02(id,class_name)-- show create table `student_04`;CREATE TABLE `student_04` (  `student_id` INT(11) DEFAULT NULL,  `student_name` VARCHAR(20) DEFAULT NULL,  `class_id` INT(11) DEFAULT NULL,  `class_name` VARCHAR(20) DEFAULT NULL,  KEY `class_id` (`class_id`,`class_name`),  CONSTRAINT `student_04_ibfk_1` FOREIGN KEY (`class_id`, `class_name`) REFERENCES `class_02` (`id`, `class_name`)) ENGINE=INNODB DEFAULT CHARSET=utf8

自关联、自引用

（如菜单目录树、递归表）

CREATE TABLE `sys_menu`(         id INT AUTO_INCREMENT PRIMARY KEY,         menu_name VARCHAR(50),         parent_id INT,         FOREIGN KEY(parent_id) REFERENCES sys_menu(id) );

级联删除：

设置外键后的表，删除主表记录：如果从表有外键值对应主表行记录，则无法删除该主表记录；

删除主表数据时，从表数据也删除，则需要在建立外键的后面增加on delete set null或者on delete cascade；

CREATE TABLE `student_05` (  `student_id` INT(11),  `student_name` VARCHAR(20),  `class_id` INT(11),  CONSTRAINT `student_05_fk_1` FOREIGN KEY (`class_id`) REFERENCES `class_01` (`id`) ON DELETE SET NULL);-- 删除主表数据，从表对应行的外键值设置为NULL

CREATE TABLE `student_06` (  `student_id` INT(11),  `student_name` VARCHAR(20),  `class_id` INT(11),  CONSTRAINT `student_06_fk_1` FOREIGN KEY (`class_id`) REFERENCES `class_01` (`id`) ON DELETE CASCADE);-- 删除主表数据，从表对应行数据也会被删除

5.CHECK约束

CREATE TABLE `student_07`(id INT AUTO_INCREMENT,student_name VARCHAR(20),age INT,PRIMARY KEY(id),CHECK(age > 20));-- show CREATE TABLE `student_07`CREATE TABLE `student_07` (  `id` INT(11) NOT NULL AUTO_INCREMENT,  `student_name` VARCHAR(20) DEFAULT NULL,  `age` INT(11) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=INNODB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

加check约束，实际语句中并没有check约束，不会对数据行进行check；即MySQL不支持CHECK约束，但无任何作用。

参考：http://blog.csdn.net/kqygww/article/details/8882990#comments