MySQL外键约束创建及删除创建外键定义(转)

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;