最近更新:我发现我学的版本有点老啊,现在的版本下,我下面写的有些东西都不成立了。
跟着网上自己学的,记录一下两个细节,代码演示一下
一、如何drop foreign key的问题
1.创建子表和主表并建立foreign key的练习
create table son(name varchar(10),age int,primary key(name));
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(10) | NO | PRI | NULL | |
| age | int | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
create table father(idnum int,name varchar(10));
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| idnum | int | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2.将主表和子表的name进行联系
alter table father add foreign key(name) references son(name);
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| idnum | int | YES | | NULL | |
| name | varchar(10) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+
可以看到name对应的key上出现了mul,也就是建立联系成功
3.尝试删除name上的foreign key
alter table father drop foreign key name;
会返回这样一个错误: Can't DROP 'name'; check that column/key exists
本质上就是告诉你我找不到叫name的foreign key联系
4.解决问题
采取一种新的添加foreign key的方式(加上别名),以idnum为例
创建表格son1并和father的idnum产生联系
create table son1(idnum int,age int,primary key(idnum));
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| idnum | int | NO | PRI | NULL | |
| age | int | YES | | NULL | |
+-------+------+------+-----+---------+-------+
alter table father add constraint idnum foreign key(idnum) references son1(idnum);
我把这个foreign key还是叫idnum
mysql> desc father;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| idnum | int | YES | MUL | NULL | |
| name | varchar(10) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+
尝试删除
alter table father drop foreign key idnum;
结果:Query OK, 0 rows affected (0.68 sec)
这个问题的出现,应该是因为primary key的drop方式和这个的不同
我觉得要彻底避免这个问题,就应该在定义的时候给foregin key以别名,不加别名的话我暂时不知道怎么删除这种联系。
二、foreign key drop之后key一栏还是显示mul的问题
1.再次查看表格
desc father;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| idnum | int | YES | MUL | NULL | |
| name | varchar(10) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+
这个idnum处的mul属性怎么还在
2.去除这个mul
drop index idnum on father;
!注意这个idnum 不是表中的idnum栏的名称,而是我在上面给foreign key链接取的别名,也就是上面当中“ alter father drop foreign key idnum;” drop的idnum
再次查看
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| idnum | int | YES | | NULL | |
| name | varchar(10) | YES | MUL | NULL | |
对应idnum的mul就没了
以上是一个初学者的浅见。