外键

create table classes (id int primary key, classid int);
insert into classes values(1,1);
insert into classes values(2,2);

create table students(id int primary key,studentname varchar(200),classid int);
insert into students values(1,'tom1',3);

1、父表classes的classid必须唯一,才能reference classes;
mysql> alter table students add constraint fk_clsssid foreign key (classid) references classes (classid) ON DELETE CASCADE;
ERROR 1215 (HY000): Cannot add foreign key constraint
加唯一索引后可执行
alter table classes add unique key idx_classid (classid);

2、
mysql> alter table students add constraint fk_clsssid foreign key (classid) references classes (classid) ON DELETE CASCADE ON UPDATE CASCADE;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`#sql-2d2d_14`, CONSTRAINT `fk_clsssid` FOREIGN KEY (`classid`) REFERENCES `classes` (`classid`) ON DELETE CASCADE ON UPDATE CASCADE)
mysql> select * from students;
+----+-------------+---------+
| id | studentname | classid |
+----+-------------+---------+
| 1 | tom1 | 3 |
+----+-------------+---------+
1 row in set (0.00 sec)
班级里没有classid=3, 加不了外键。
delete from students； 加外键成功。

alter table students drop foreign key fk_clsssid;
insert into students values(1,'tom1',2);

班级里有classid=2, 可以加外键。

3、加外键顺便就会加索引。删除外键，索引还是保留的，还要单独删除索引。
alter table students drop foreign key fk_clsssid; fk_clsssid 索引还是存在的。

4、pt工具父表添加索引。

--alter-foreign-keys-method=drop_swap 会删除原表
--alter-foreign-keys-method=rebuild_constraints  子表的外键名会改变，更安全。表太大会有性能问题。
pt-online-schema-change  --user=***** --ask-pass   -S /tmp/mysqld.4001_xxx.sock  --critical-load="Threads_running=500"   --no-drop-old-table --no-check-replication-filters  --alter "add column classname varchar(20)" --alter-foreign-keys-method=rebuild_constraints   --charset=utf8  D=test,t=classes --print --dry-run

 

5、select * from KEY_COLUMN_USAGE\G
select * from KEY_COLUMN_USAGE where  REFERENCED_TABLE_NAME is not null;