通常数据库约束有五类:
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关键字
组合主键:
修改列为主键:
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