初学者记录一下mysql学习中遇到的两个问题

最近更新：我发现我学的版本有点老啊，现在的版本下，我下面写的有些东西都不成立了。

跟着网上自己学的，记录一下两个细节，代码演示一下

一、如何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就没了

以上是一个初学者的浅见。