【转】《与MySQL的零距离接触》第三章:约束以及修改数据表 (3-7:MySQL 修改数据表–删除约束)

时间:2022-10-20 07:24:43

3-7:MySQL 修改数据表–删除约束

一. 前言

上一节最后我们讲到了删除默认约束,本节我们来讲解删除主键约束和唯一约束以及外键约束

二. 删除主键约束

删除主键约束的语法结构:

ALTER TABLE tbl_name DROP PRIMARY KEY

因为任何一个数据表只有一个主键约束,所以删除的时候不用加名字:

mysql> ALTER TABLE users2 DROP PRIMARY KEY;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW COLUMNS FROM users2;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| username | varchar(10)          | NO   | PRI | NULL    |       |
| pid      | smallint(5) unsigned | YES  | MUL | NULL    |       |
| id       | smallint(5) unsigned | NO   |     | 0       |       |
| age      | tinyint(3) unsigned  | NO   |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

可以看到id已经不再是主键约束

三. 删除唯一约束

删除唯一约束的语法结构:

ALTER TABLE tbl_name DROP {INDEX | KEY} index_name

因为一张表可以有多个唯一约束,所以删除的时候需要指定名字,首先需要获取约束的名字,

可以通过查看一下user2表的索引来获取:

mysql> SHOW INDEXES FROM users2\G
*************************** 1. row ***************************
        Table: users2
   Non_unique: 0
     Key_name: username
 Seq_in_index: 1
  Column_name: username
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: users2
   Non_unique: 1
     Key_name: pid
 Seq_in_index: 1
  Column_name: pid
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.00 sec)

Key_name 便是定义的索引名字,接下来我们来删除唯一约束:

mysql> ALTER TABLE users2 DROP INDEX username;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

注意这里只是删除约束,而不是删除字段,接下来再来看一下user2表的索引:

mysql> SHOW INDEXES FROM users2\G
*************************** 1. row ***************************
        Table: users2
   Non_unique: 1
     Key_name: pid
 Seq_in_index: 1
  Column_name: pid
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.00 sec)

发现只剩一个索引,删除成功。因为创建唯一约束的时候,索引

这里遇到几个问题就是:

问:为什么删除唯一约束,却需要唯一索引的名字呢?而且为什么Column_name :字段/列名称 和Key_name:索引名称相同呢?

答:创建主键约束、唯一约束的时候,会自动创建一个唯一的索引。主键默认生成的索引名字是PRIMARY,而唯一约束生成的是同名的索引,所以你看到这个key_name(索引名称)和column_name是同一个名字。 ——摘自Gemma_Tong的回答

问:删除约束为什么是DROP INDEX ,index 不是索引么? 删除约束为什么是DROP INDEX ,index 不是索引么?

答一:如果说我们要删除一个unique key ,但是这个unique key在一张表中有很多个,这时候我们单纯的写drop unique key系统不知道的要删除的是哪一个,会全部删除,这不是我们想要的结果,好在的是每一个约束都存在一个名字,你可以吧把ndex理解成约束的名字,这样我们就可以指定删除某个约束了; 查看INDEX : SHOW INDEXES FROM table name;——摘自greenhandc的回答

答二:在你建立unique约束的同时系统会给你自动建立一个同名的索引,在删除unique约束的时候你直接去删除索引就可以了,只有unique约束可以这样使用。在删除主键和外键的时候还是要删除约束的,值得注意的是删除外键之后,由于创建外键的时候系统也自动创建了一个同名索引,删除外键索引还在,为了避免查询表结构的时候产生混乱,在删除外键之后最后顺带着连同索引一起删除。——摘自大佬金的回答

 

四. 删除外键约束

删除外键约束语法结构:

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol

通过查看一下user2表的创建命令可以查看外键约束的名字:

mysql> SHOW CREATE TABLE users2\G
*************************** 1. row ***************************
       Table: users2
Create Table: CREATE TABLE `users2` (
  `username` varchar(10) NOT NULL,
  `pid` smallint(5) unsigned DEFAULT NULL,
  `id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `age` tinyint(3) unsigned NOT NULL,
  KEY `pid` (`pid`),
  CONSTRAINT `users2_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

可以看到,外键约束的名字是users2_ibfk_1,这是系统赋予的,接下来删除外键约束:

mysql> ALTER TABLE users2 DROP FOREIGN KEY users2_ibfk_1;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE users2\G
*************************** 1. row ***************************
       Table: users2
Create Table: CREATE TABLE `users2` (
  `username` varchar(10) NOT NULL,
  `pid` smallint(5) unsigned DEFAULT NULL,
  `id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `age` tinyint(3) unsigned NOT NULL,
  KEY `pid` (`pid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

可以看到外键约束已经被删除,但是还存在着名为pid的索引,接下来删除索引:

mysql> SHOW CREATE TABLE users2\G
*************************** 1. row ***************************
       Table: users2
Create Table: CREATE TABLE `users2` (
  `username` varchar(10) NOT NULL,
  `pid` smallint(5) unsigned DEFAULT NULL,
  `id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `age` tinyint(3) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

可以看到pid的索引也已经被删除