概念
表能够通过某个字段唯一区分出不同的记录,这个字段被称为主键。插入相同主键的两条记录是不被允许的。
MySQL主键设计原则:不使用任何业务相关的字段作为主键
永远也不要更新MySQL主键,修改主键会造成一系列的影响。
MySQL主键不应包含动态变化的数据,如时间戳、创建时间列、修改时间列等
主键设计的常用方案是自增ID。因为整数通常是主键的最好选择,因为它很快且可以使用AUTO_INCREAMENT,如果可能,应该避免使用字符串类型作为标识列,因为很消耗空间,且通常比数字类型慢。
MySQL支持外键的存储引擎只有InnoDB,在创建外键的时候,要求父表必须有对应的索引,子表在创建外键的时候也会自动创建对应的索引。在创建索引的时候,可以指定在删除、更新父表时,对子表进行的相应操作,外键属性包括 RESTRICT, NO ACTION, SET NULL和CASCADE。默认是restrictRESTRICT 和 NO ACTION相同,是指在子表有关联记录的情况下父表不能更新
CASCADE表示父表在更新或者删除时,更新或者删除子表对应记录
SET NULL则是表示父表在更新或者删除的时候,子表的对应字段被SET NULL
外键最好在创建表时进行,对主键外键的各种操作最好都在Workbench里进行,因为命令实在太复杂。注意主外键的类型要匹配,否则会报错:
在创建表时创建外键,一般结尾是ON DELETE CASCADE ON UPDATE NO ACTION;,对DELETE命令按CASCADE处理,在父表中执行DELETE后会同步更新子表,如果是RESTRICT,还得先操作子表,这样C++程序会复杂很多;对UPDATE按默认的不更新处理。
查看主外键可以用show create table tabelName查看表 tabelName 的创建信息,包含了主外键,当然用Workbench更好
用命令查看数据库database1的所有键,它存在表INFORMATION_SCHEMA.KEY_COLUMN_USAGE里面1select TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where CONSTRAINT_SCHEMA ='database1'
实例
新建两个表point和map,前者做子表,后者做父表,因为有了地图才能有点。
map表的情况:
point表的情况:
外键关系涉及一个包含中心数据值的父表,以及一个具有相同值的子表,指向其父表。表point中的MapName有外键约束,指向了表map的主键MapName,如果后者的MapName不是主键,创建时会报错.
外键不一定是另一个表的主键,但必须是唯一性索引,主键约束和唯一性约束都是唯一性索引。若要确保验证了组合 FOREIGN KEY 约束的所有值,请将所有参与列指定为 NOT NULL
子表若要增加一行,其MapName必须是父表中已经有的MapName.反过来,父表若要删除一行,其MapName必须是子表中没有的,否则子表中的MapName无法生存.
如果直接删除父表中的一行,gf在子表中的MapName也存在,此时删除会报错:
所以规范做法是先删除子表中的gf行,再去父表中删除gf对应的行。
也可以通过设置FOREIGN_KEY_CHECKS变量来避免这种情况:1
2
3SET FOREIGN_KEY_CHECKS = 0;
SET FOREIGN_KEY_CHECKS = 1;
但是子表不会有变化,实际相当于不限制对父表和子表的操作顺序,最稳妥的方法还是先操作子表再操作父表.
如果一个表中的外键连到另一个表的主键,注意在Workbench中设置时,在一个外键中只设置自己对应得主键,不要连另一个也设置,也就是两个外键要分开,否则会报错
问题
在MySQL Workbench上使用”TRUNCATE TABLE;”清空一个表时返回错误: Error Code: 1701. Cannot truncate a table referenced in a foreign key constraint
解决方法:1
2
3SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE TABLE_E;
SET FOREIGN_KEY_CHECKS = 1;