参考： http://blog.sina.com.cn/s/blog_53729e4601011wja.html
http://blog.163.com/sejin@126/blog/static/827504552010101544755364/
http://blog.csdn.net/xubo578/article/details/8203550 E-R图的
MySQL中定义外键的表,双方必须都是Innodb。
子表中外键字段和其对应父表中的字段必须都设为索引，主键自动为索引
//E-R图中几种关系Identifying Relationship:需要提供外键给另外一张表作联合主键 Non-identifying Mandatory Relationship:强制外键 Non-identifying Optional Relationship:可选外键 One-to-one Relationship Non-specific：无特殊关系 判别一个关系是Non-Identifying还是Identifying只要区分子实体的主键，看是否需要父实体的外键来共同作为主键(个人理解类似联合主键？)，需要则为Identifying，如果子实体自己的主键就可唯一标识则它为Non-Identifying！ //貌似Mysql Workbench中 虚线1:1关联是添加外键的，实线是添加外键为主键的
ALTER TABLE yourtablename ADD [CONSTRAINT 外键名] FOREIGN KEY [id] (index_col_name, ...) //外键名 不能加引号，单双引号都不行 REFERENCES tbl_name (index_col_name, ...) [ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}] // ON DELETE CASCADE 删除父表对应字段时，子表对应外键也会被删除，不能子表操作父表 [ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}] // ON UPDATE CASCADE 修改父表对应字段时，子表对应外键也会更新
//多个外键 个人理解 要把该字段设置为索引，再foreign keyCREATE 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;
//先给父表，子表中涉及到外键的字段设置索引ALTER TABLE parts ADD INDEX idx_model (model);ALTER TABLE pc ADD INDEX idx_cpumodel (cpumodel);//在给pc表设置外键ALTER TABLE pc ADD CONSTRAINT fk_cpu_model FOREIGN KEY (cpumodel) REFERENCES parts(model) ON UPDATE CASCADE;//删除外键alter table child drop foreign key 外键名
ps:
1.若不声明on update/delete,则默认是采用restrict方式. //restrict 方式 个人理解 表示受限，比如ON UPDATE RESTRICT 时，父表对应字段更新，子表中外键不随之更新，同样对应删除
2.对于外键约束,最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式.