Mysql数据库中给表的字段创建约束时，自动给表加了一行关键字Key，撤销时没有把 KEY `fk_ClaStudents` (`c_id`) 删除

野性酷女 2021-10-16 00:20 79阅读 0赞

### 1.首先是创建两张表 Classes 和 Students ###

CREATE TABLE Classes(
        c_id  int NOT NULL,
        name varchar(200),
        PRIMARY KEY (c_id)
    );
    CREATE TABLE Students(
        s_id  int NOT NULL,
        name varchar(200),
        c_id int NOT NULL,
        PRIMARY KEY (s_id)
    );

** 查看建表语句**

### 2.创建Students表的 **'c\_id'** 字段约束 ###

ALTER TABLE Students
    ADD CONSTRAINT fk_ClaStudents
    FOREIGN KEY (c_id)
    REFERENCES Classes (c_id)

**查看建表语句**

mysql> SHOW CREATE TABLE Students;
    +----------+---------------------------------------------------------------------
    ---------------------------------------------------------------------------------
    ----------------------------------------------------------------+
    | Table    | Create Table
                                                                    |
    +----------+---------------------------------------------------------------------
    ---------------------------------------------------------------------------------
    ----------------------------------------------------------------+
    | Students | CREATE TABLE `students` (
      `s_id` int(11) NOT NULL,
      `name` varchar(200) DEFAULT NULL,
      `c_id` int(11) NOT NULL,
      PRIMARY KEY (`s_id`),
      KEY `fk_ClaStudents` (`c_id`),           # 创建约束后查看表定义多了一行Key
      CONSTRAINT `fk_ClaStudents` FOREIGN KEY (`c_id`) REFERENCES `classes` (`c_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
    +----------+---------------------------------------------------------------------
    ---------------------------------------------------------------------------------
    ----------------------------------------------------------------+
    1 row in set (0.00 sec)

### **3.撤销Students表的 'c\_id' 字段约束** ###

ALTER TABLE Students
    DROP FOREIGN KEY fk_ClaStudents

**   查看建表语句**

mysql> SHOW CREATE TABLE Students;
    +----------+-----------------------------------------
    -----------------------------------------------------
    | Table    | Create Table
    
    +----------+-----------------------------------------
    -----------------------------------------------------
    | Students | CREATE TABLE `students` (
      `s_id` int(11) NOT NULL,
      `name` varchar(200) DEFAULT NULL,
      `c_id` int(11) NOT NULL,
      PRIMARY KEY (`s_id`),
      KEY `fk_ClaStudents` (`c_id`)            # 竟然没给给删除
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
    +----------+-----------------------------------------
    -----------------------------------------------------
    1 row in set (0.00 sec)