MySQL外键约束创建及删除

创建外键定义 ：

CREATE TABLE categories (    

category_id tinyint(3) unsigned NOT NULL AUTO_INCREMENT,

name varchar(30) NOT NULL,    

PRIMARY KEY(category_id)    

) ENGINE=INNODB;

INSERT INTO categories VALUES (1, ‘SQL Server’), (2, ‘Oracle’), (3, ‘PostgreSQL’), (4, ‘MySQL’), (5, ‘SQLite’);

CREATE TABLE members (    

member_id INT(11) UNSIGNED NOT NULL,

name VARCHAR(20) NOT NULL,    

PRIMARY KEY(member_id)    

) ENGINE=INNODB;

CREATE TABLE articles (   

article_id INT(11) unsigned NOT NULL AUTO_INCREMENT,

title varchar(255) NOT NULL,    

category_id tinyint(3) unsigned NOT NULL,    

member_id int(11) unsigned NOT NULL,    

INDEX (category_id),    

FOREIGN KEY (category_id) REFERENCES categories (category_id),     CONSTRAINT fk_member FOREIGN KEY (member_id) REFERENCES members (member_id),    

PRIMARY KEY(article_id)    

) ENGINE=INNODB;

categories.category_id和articles.category_id、members.member_id和articles.member_id已经建立外键关系，只有articles.category_id的值存在与categories.category_id表中并且articles.member_id的值存在与members.member_id表中才会允许被插入或修改。：

删除外键定义 ：

定义外键的时候articles.member_id外键比articles.category_id子句多了一个CONSTRAINT fk_member。这个fk_member就是用来删除外键定义用的：

ALTER TABLE articles DROP FOREIGN KEY fk_member;

这样articles.member_id外键定义就被删除

如果定义时没有指定CONSTRAINT fk_symbol（即外键符号）时MySQL会自己创建一个，可以通过以下命令查看：

SHOW CREATE TABLE articles;

+———-+————————————+ | Table   | Create Table                      | +———-+————————————+ | articles | CREATE TABLE `articles` ( `article_id` int(11) unsigned NOT NULL auto_increment, `category_id` tinyint(3) unsigned NOT NULL, `member_id` int(11) unsigned NOT NULL, `title` varchar(255) NOT NULL, PRIMARY KEY (`article_id`), KEY `category_id` (`category_id`), KEY `member_id` (`member_id`), CONSTRAINT `articles_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1         | +———-+————————————+ 1 row in set (0.01 sec)

可以看出articles.category_id的外键符号为articles_ibfk_1，因此就可以执行以下命令删除外键定义：

ALTER TABLE articles DROP FOREIGN KEY articles_ibfk_1;