mysql外键约束使用详解
MySQL有两种常用的引擎类型：MyISAM和InnoDB。目前只有InnoDB引擎类型支持外键约束。InnoDB中外键约束定义的语法如下：
[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION 外键的使用需要满足下列的条件：
1. 两张表必须都是InnoDB表，并且它们没有临时表。
2. 建立外键关系的对应列必须具有相似的InnoDB内部数据类型。
3. 建立外键关系的对应列必须建立了索引。
4. 假如显式的给出了CONSTRAINT symbol，那symbol在数据库中必须是唯一的。假如没有显式的给出，InnoDB会自动的创建。
如果子表试图创建一个在父表中不存在的外键值，InnoDB会拒绝任何INSERT或UPDATE操作。如果父表试图UPDATE或者DELETE任何子表中存在或匹配的外键值，最终动作取决于外键约束定义中的ON UPDATE和ON DELETE选项。InnoDB支持5种不同的动作，如果没有指定ON DELETE或者ON UPDATE，默认的动作为RESTRICT:
1. CASCADE: 从父表中删除或更新对应的行，同时自动的删除或更新自表中匹配的行。ON DELETE CANSCADE和ON UPDATE CANSCADE都被InnoDB所支持。
2. SET NULL: 从父表中删除或更新对应的行，同时将子表中的外键列设为空。注意，这些在外键列没有被设为NOT NULL时才有效。ON DELETE SET NULL和ON UPDATE SET SET NULL都被InnoDB所支持。
3. NO ACTION: InnoDB拒绝删除或者更新父表。
4. RESTRICT: 拒绝删除或者更新父表。指定RESTRICT（或者NO ACTION）和忽略ON DELETE或者ON UPDATE选项的效果是一样的。
5. SET DEFAULT: InnoDB目前不支持。
外键约束使用最多的两种情况无外乎：
1）父表更新时子表也更新，父表删除时如果子表有匹配的项，删除失败；
2）父表更新时子表也更新，父表删除时子表匹配的项也删除。
前一种情况，在外键定义中，我们使用ON UPDATE CASCADE ON DELETE RESTRICT；后一种情况，可以使用ON UPDATE CASCADE ON DELETE CASCADE。
InnoDB允许你使用ALTER TABLE在一个已经存在的表上增加一个新的外键:
ALTER TABLE tbl_name
ADD [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
InnoDB也支持使用ALTER TABLE来删除外键：
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;
约束
约束保证数据的完整性与一致性
约束分为表级约束和列级约束
约束类型包括 -- NOT NULL (非空约束) -- PRIMARY KEY (主键约束) -- UNIQUE KEY (唯一约束) -- DEFAULT (默认约束) -- FOREIGN KEY (外键约束)
外键约束的要求解析
FOREIGN KEY 保证数据的一致性,完整性. 实现一对一或一对多关系
父表与子表必须使用相同的存储引擎,而且禁止使用临时表.
数据表的存储引擎只能为InnoDB
外键列和参照列必须具有相似的数据类型.其中数字的长度或者是否有符号位必须相同;而字符的长度则可以不同.
外键列和参照列必须创建索引.如果外键列不存在索引的话,MySQL将自动创建索引
显示数据表的创建数据
SHOW CREATE TABLE tbl_name;
显示数据表索引
SHOW INDEXES FROM tbl_name;
创建两个表并且进行外键约束
``` CREATE TABLE provinces( id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, pname VARCHAR(20) NOT NULL );
CREATE TABLE users( id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, username VARCHAR(10) NOT NULL, pid SMALLINT UNSIGNED, FOREIGN KEY(pid) REFERENCES provinces(id) );
``` 外键与主键数据类型必须的一直 比如 pname SMALLINT 对应的 pid SMALLINT 而且符号位也必须相同 有外键的表称为子表 子表参照的表称为附表 参照列自动创建了索引
外键约束的参照操作
CASCADE:从父表删除或更新且自动删除或更新子表中匹配的行
SET NULL:从父表删除或更新行,并设置子表中的外键列为NULL.如果使用该选项,必须保证子表列没有指定NOT NULL.
RESTRICT:拒绝对父表的删除或更新操作.
NO ACTION:标准的SQL关键字,在MYSQL中与RESTRICT相同.
外键约束仅仅支持innodb引擎
CREATE TABLE user1( id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, username VARCHAR(10) NOT NULL, pid SMALLINT UNSIGNED, FOREIGN KEY(pid) REFERENCES provinces(id) ON DELETE CASCADE ); 参照操作需要开启ON DELETE后再加上参数
添加数据
``` INSERT provinces(pname) VALUES("A"); INSERT provinces(pname) VALUES("B"); INSERT provinces(pname) VALUES("C");
//插入用户数据
INSERT user1(username,pid) VALUES("Tom",3); ``` 插入用户数据只需要对应设置外键的id即可.
如果记录没有写入成功,但是编号会自动递增
删除记录
删除provinces id为3的记录 DELETE FROM provinces WHERE id = 3; 那么相应的子表中外键id为3的也会删除.
表级约束与列级约束
对一个数据列建立的约束,称为列级约束.
对多个数据列建立的约束,称为表级约束.
列级约束既可以在列定义时声明,也可以在列定义后声明.
表级约束只能在列定义后声明.
修改数据表
添加单列
ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST|AFTER col_name] FIRST 指的是插入的这一列会在最前面.AFTER col_name则是在某一列后面
添加单列数据
SHOW COLUMNS FROM user1; ALTER TABLE user1 ADD age INT UNSIGNED NOT NULL DEFAULT 10; ALTER TABLE user1 ADD password VARCHAR(32) NOT NULL AFTER username;//插入某列后面 ALTER TABLE user1 ADD truename VARCHAR(20) NOT NULL FIRST; DEFAULT:未明确表明数据的时候.默认指定的数据 UNSIGNED:针对数值型类型 是否有符号
添加多列
添加多列无法指定位置
ALTER TABLE tbl_name ADD [COLUMN] (col_name column_definition);
给user1添加多列数据
ALTER TABLE user1 ADD( num INT UNSIGNED NOT NULL DEFAULT 1, sex ENUM("男","女") NOT NULL DEFAULT "男" );
删除列
ALTER TABLE user1 DROP truename;
删除多列
ALTER TABLE user1 DROP truename,DROP password;
修改列的时候可以删除的时候再添加.中间通过逗号分隔.
删除的同时再添加列
ALTER TABLE user1 DROP num, ADD pm FLOAT UNSIGNED DEFAULT 15;
添加主键约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type](index_col_name,...)
范例一
CREATE TABLE user2( username VARCHAR(20) NOT NULL, pid SMALLINT UNSIGNED ); //增加主键 ALTER TABLE user2 ADD id SMALLINT UNSIGNED; //增加主键约束 ALTER TABLE user2 ADD CONSTRAINT PK_user2_id PRIMARY KEY (id); CONSTRAINT:用来起别名
添加唯一约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type](index_col_name,...)
范例一
ALTER TABLE user2 ADD UNIQUE (username);
添加外键约束
范例一
user2中pid参照provinces; ALTER TABLE user2 ADD FOREIGN KEY (pid) REFERENCES provinces (id);
添加/删除默认约束
ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
范例一
ALTER TABLE user2 ADD age TINYINT UNSIGNED NOT NULL; ALTER TABLE user2 ALTER age SET DEFAULT 15; 给age添加默认约束
删除主键约束
任何一个表有且只有一个主键 ALTER TABLE user2 DROP PRIMARY KEY;
删除主键索引
ALTER TABLE user2 DROP INDEX username; 删除的仅仅是索引
显示索引列表名称
SHOW INDEXes FROM user2;
删除外键约束
删除约束是删除约束的名称 ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbok
范例
``` //查询外键名称
SHOW CREATE TABLE user2; //找到pid的外键名称为 user2_ibfk_1;然后进行删除
ALTER TABLE user2 DROP FOREIGN KEY user2_ibfk_1;
虽然外键不在了,但是索引还是存在的 如果不想要索引则删除即可 ALTER TABLE user2 DROP INDEX pid; ```
修改列定义和更名数据表
修改列定义
ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST |AFTER col_name]
修改列顺序
ALTER TABLE user2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST; column_definition指的是列定义 不变
修改列定义-范例
ALTER TABLE user2 MODIFY id TINYINT UNSIGNED NOT NULL; 修改列定义的时候有可能会造成数据的丢失.
修改列名称
ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new col_name column_definition [FIRST |AFTER col_name]
修改id的类型与名称 ALTER TABLE user2 CHANGE pid p_id TINYINT UNSIGNED NOT NULL;
数据表更名
一. ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name;
二. RENAME TABLE tbl_name TO new_tbl_name [,tbl_name2 TO new_tbl_name2];
范例 ``` ALTER TABLE user2 RENAME users2;
RENAME TABLE users2 TO user2; ```
多个外键存在:
product_order表对其它两个表有外键。
一个外键引用一个product表中的双列索引。另一个引用在customer表中的单行索引：
CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
price DECIMAL,
PRIMARY KEY(category, id)) TYPE=INNODB;
CREATE TABLE customer (id INT NOT NULL,
PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
product_category INT NOT NULL,
product_id INT NOT NULL,
customer_id INT NOT NULL,
PRIMARY KEY(no),
-- 双外键
INDEX (product_category, product_id),
FOREIGN KEY (product_category, product_id)
REFERENCES product(category, id)
ON UPDATE CASCADE ON DELETE RESTRICT,
-- 单外键
INDEX (customer_id),
FOREIGN KEY (customer_id)
REFERENCES customer(id)) TYPE=INNODB;
(6) 说明:
1.若不声明on update/delete,则默认是采用restrict方式.
2.对于外键约束,最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式.
总结
约束 - 按功能化为:NOT NULL,PRIMARY KEY, UNIQUE KEY, DEFAULT,FOREIGN KEY - 按数据列的数目化为:表级约束,列级约束
修改数据表 - 针对字段的操作:添加/删除字段,修改列定义,修改列名称等 - 针对约束的操作:添加/删除各种约束. - 针对数据表的操作:数据表更名(两种方式).
列级约束：只能应用于一列上。 表级约束：可以应用于一列上，也可以应用在一个表中的多个列上。
默认约束(DEFAULT)与非空约束(NOT NULL)不存在表级约束
您可能感兴趣的文章：
mysql外键约束使用详解
SQL FOREIGN KEY 约束
mysql中的外键使用详解
mysql 添加主外键约束遇到 "unknown table engine innodb"的解决方法
mysql中添加列、修改列、删除列的方法
SQL PRIMARY KEY 约束
SQLite 约束
MySQL常见错误代码解析
mysql显示表的外键的命令
SQL UNIQUE 约束