本章主要讲解外键约束以及其他约束的增加删除操作，通过本章将会对数据表列的操作更加熟练。

1.外键约束

目的：保持数据一致性，完整性；实现一对一或一对多关系。
约束保证数据的完整性和一致性
约束分为表级约束和列级约束（根据约束所针对的字段数目的多少决定）
列级约束：只针对表中的一个字段进行约束；
表级约束：针对表中的两个及以上字段进行约束。
约束类型包括：（功能划分）
NOT NULL(非空约束)
PRIMARY KEY(主键约束)
UNIQUE KEY(唯一约束)
DEFAULT(默认约束)
FOREIGN KEY(外键约束)
外键约束的要求：

父表和子表必须使用相同的存储引擎，而且禁止使用临时表
子表：具有外键列的表
父表：子表参照的表
数据表的存储引擎只能为InnoDB
外键列和参照列必须具有类似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同
外键列：加过FOREIGN 关键字的列
参照列：外键列所参照的列

外键列和参照列必须创建索引。如果参照列不存在索引的话，MYSQL将自动创建索引
【外键约束】FOREIGN KEY(column) REFERENCES table(key)
【显示索引】SHOW INDEXES FROM [table]\G;
要创建外键约束必须满足以上四点要求。

1）编辑数据表的默认存储引擎：

mysql配置文件（D:\MySQL\MySQL Server 5.5\my.ini 安装路径下）：
default-storage-engine=INNODB；
（一般默认都为这个）

2）创建数据类型相同的外键列和参照列

登录客户端，进入test数据库后，让我们来创建一个新的数据表：

mysql> CREATE TABLE provinces(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> pname varchar(20) NOT NULL
    -> );
Query OK, 0 rows affected (0.05 sec)

查看数据表是否采用了我们的默认引擎INNODB

mysql> SHOW CREATE TABLE provinces;
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                              |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| provinces | CREATE TABLE `provinces` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `pname` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

可以看到ENGINE=InnoDB，说明设置成功。这样provinces数据表就创建好啦！
接下来创建另一个数据表
创建一个用户数据表，包括用户的id、姓名，还有存储用户所在的省份信息。以前添加省份信息的话，需要增加省份字段还要添加字符，现在是关系型数据库，并不需要这样做啦，只需要存储省份的id即可。
pid参考provinces 的id

mysql> CREATE TABLE users(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(10) NOT NULL,
    -> pid BIGINT,
    -> FOREIGN KEY (pid) REFERENCES provinces (id)
    -> );
ERROR 1005 (HY000): Can't create table 'test.users' (errno: 150)

居然出现了错误，仔细观察就会发现，pid的数据类型为BIGINT和参考provinces中id的数据类型不一致，所以产生错误。
再来创建一次

mysql> CREATE TABLE users(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(10) NOT NULL,
    -> pid SMALLINT,
    -> FOREIGN KEY (pid) REFERENCES provinces (id)
    -> );
ERROR 1005 (HY000): Can't create table 'test.users' (errno: 150)

此时还是错误，说明大小不仅需要相同，是否有符号位，也必须相同。
最后一次

mysql> CREATE TABLE users(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(10) NOT NULL,
    -> pid SMALLINT UNSIGNED,
    -> FOREIGN KEY (pid) REFERENCES provinces (id)
    -> );
Query OK, 0 rows affected (0.04 sec)

user就是我们创建的子表，provinces就是父表。pid就是外键列， id就是参照列，当为数字的情况下时，要保证长度和是否有符号位相同，字符的情况下可以不同。

provinces的id并没有创建索引，但是系统有没有为我们自动创建呢，答案是肯定的，因为我们使用了主键约束，主键约束在创建的同时会自动创建索引。我们来证明一下是否存在索引：

mysql> SHOW INDEXES FROM provinces;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| provinces |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.04 sec)

这样看的不直观，改一下命令：

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

ERROR:
No query specified

可以看到id字段已经有了索引（ Column_name: id ）
外键列的pid我们并没有创建主键，那么有没有创建索引呢？

mysql> SHOW INDEXES FROM users\G;
*************************** 1. row ***************************
        Table: users
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: users
   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)

一共存在两个索引 Column_name: id （主键索引） Column_name: pid
主键在创建的时候会自动创建索引，外键在创建的时候也会自动创建索引。所以当参照列不是主键的时候不会自动创建索引，需要手动创建。
再看一下users数据表

mysql> SHOW CREATE TABLE users;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                            |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(10) NOT NULL,
  `pid` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pid` (`pid`),
  CONSTRAINT `users_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

其中 KEY pid (pid),是系统自动给我们添加的。

索引和存储引擎会在后面详细介绍。

3）外键约束的参照操作

CASCADE:从父表删除或更新且自动删除或更新子表中匹配的行
SET NULL:从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL
RESTRICT:拒绝对父表的删除或更新操作
NO ACTION:标准SQL的关键字，在MySQL中与RESTRICT相同
下面我们使用CASCADE做一个说明：
首先创建一个新的数据表和users一样,只是增加CASCADE

mysql> CREATE TABLE users1(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(10) NOT NULL,
    -> pid SMALLINT UNSIGNED,
    -> FOREIGN KEY (pid) REFERENCES provinces (id) ON DELETE CASCADE
    -> );
Query OK, 0 rows affected (0.03 sec)

查看一下这个表

mysql> SHOW CREATE TABLE users1;
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                                                                |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users1 | CREATE TABLE `users1` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(10) NOT NULL,
  `pid` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pid` (`pid`),
  CONSTRAINT `users1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

其中ON DELETE CASCADE就表示父表中更新或删除记录，子表也同时更新相应的行。
要对users1写入记录，就必须要对其父表先写入记录。原因很简单：如果不对父表写入记录，子表的pid将无从参照。
对provinces写入记录

mysql> INSERT provinces(pname) VALUES('A');
Query OK, 1 row affected (0.03 sec)

mysql> INSERT provinces(pname) VALUES('B');
Query OK, 1 row affected (0.03 sec)

mysql> INSERT provinces(pname) VALUES('C');
Query OK, 1 row affected (0.04 sec)

查看一下

mysql> SELECT * FROM provinces;
+----+-------+
| id | pname |
+----+-------+
|  1 | A     |
|  2 | B     |
|  3 | C     |
+----+-------+
3 rows in set (0.03 sec)

对users1写入记录

mysql> INSERT users1(username,pid) VALUES('hua',1);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT users1(username,pid) VALUES('hui',3);
Query OK, 1 row affected (0.04 sec)

mysql> INSERT users1(username,pid) VALUES('yuan',4);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`users1`, CONSTRAINT `users1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`) ON DELETE CASCADE)
mysql> INSERT users1(username,pid) VALUES('qing',3);
Query OK, 1 row affected (0.04 sec)

当我插入第三条记录时发生错误，因为父表中不存在这样的id。
外键约束所关联的父表字段值必须存在，子表中才能创建。
查看users1中的记录

mysql> SELECT * FROM users1;
+----+----------+------+
| id | username | pid  |
+----+----------+------+
|  1 | hua      |    1 |
|  2 | hui      |    3 |
|  4 | qing     |    3 |
+----+----------+------+
3 rows in set (0.00 sec)

当记录没写入成功时，编号仍然会自动递增，所以是1，2，4；
删除父表中id=3的记录

1 2	mysql> DELETE FROM provinces WHERE id=3; Query OK, 1 row affected (0.00 sec)

注意：删除操作：DELETE FROM tbname WHERE 约束条件（id =3）
看一下父表的记录

mysql> SELECT * FROM provinces;
+----+-------+
| id | pname |
+----+-------+
|  1 | A     |
|  2 | B     |
+----+-------+
2 rows in set (0.00 sec)

C已经被删除。
看一下子表的记录

mysql> SELECT * FROM users1;
+----+----------+------+
| id | username | pid  |
+----+----------+------+
|  1 | hua      |    1 |
+----+----------+------+
1 row in set (0.00 sec)

pid为3的均被删除。

实际开发中不怎么使用物理外键，因为只有innodb引擎支持，通常使用逻辑外键。

物理外键指的是使用foreign key 作为外键关联另一张的字段的连接方法，而且限定了引擎为InnoDB,而逻辑外键，又叫做事实外键，在定义两张表时，是按照某种结构的方式去定义，不去使用foreign key 来定义。

4）表级约束与列级约束：

对一个数据列建立的约束，称为列级约束；
对多个数据列建立的约束，称为表级约束；
列级约束既可以在列定义时声明，也可以在列定义后声明；
表级约束只能在列定义后声明；
并不是所有约束都存在表级约束。如NOT NULL 和DEFAULT约束是不存在表级约束的；
实际开发过程中，很少使用表级约束。
5）修改数据表
添加列
ALTER TABLE table_name ADD [COLUMN] column_name column_definition [FIRST | AFTER column_name]

FIRST :添加到列的第一个位置
AFTER column_name：在指定列的后面
默认添加到最后。
现在我们来实战演练一下，列的添加。
先看一个users1的列信息

mysql> SHOW COLUMNS FROM users1;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(10)          | NO   |     | NULL    |                |
| pid      | smallint(5) unsigned | YES  | MUL | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.04 sec)

接下来添加一列

1
2
3

mysql> ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

查看一下数据表结构

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

可以看出默认插入到最后一列。
再使用FIRST插入一列

1
2
3

mysql> ALTER TABLE users1 ADD password VARCHAR(20) NOT NULL FIRST;
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

再次查看数据表结构就会发现，password在第一例的位置。这里不再演示。
使用AFTER插入一列

1
2
3

mysql> ALTER TABLE users1 ADD habital VARCHAR(20) NOT NULL AFTER username;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

habital被添加在username的后面。

添加多列
ALTER TABLE tbl_name ADD [COLUMN] (col_name column_definiton,…)
和添加一列基本类似，不再操作。需要注意的是，添加多列时，不能指定位置，只能在列尾添加。

删除列

ALTER TABLE tbl_name DROP [COLUMN] col_name
如：ALTER TABLE user1 DROP truename;删除表user1的truename单列
如：ALTER TABLE user1 DROP password,DROP age;删除多列，用逗号分隔开
删除password列

1
2
3

mysql> ALTER TABLE users1 DROP password;
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

查看一下：

mysql> SHOW COLUMNS FROM users1;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(10)          | NO   |     | NULL    |                |
| habital  | varchar(20)          | NO   |     | NULL    |                |
| pid      | smallint(5) unsigned | YES  | MUL | NULL    |                |
| age      | tinyint(3) unsigned  | NO   |     | 10      |                |
+----------+----------------------+------+-----+---------+----------------+

如果我想删除多列呢？
删除多列，通过“,”隔开即可

1
2
3

mysql> ALTER TABLE users1 DROP age,DROP habital;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

再查看一下吧

mysql> SHOW COLUMNS FROM users1;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(10)          | NO   |     | NULL    |                |
| pid      | smallint(5) unsigned | YES  | MUL | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.04 sec)

6）添加约束

当约束不够时，如何增加约束？或者如何删除约束？
为此，我们先创建一个新的数据表：

mysql> CREATE TABLE users2(
    -> username VARCHAR(20) NOT NULL,
    -> id SMALLINT UNSIGNED,
    -> pid TINYINT UNSIGNED
    -> );
Query OK, 0 rows affected (0.05 sec)

添加主键约束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARYKEY [index_type] (index_col_name,…)
CONSTRAINT 可以添加也可以不添加，如果添加可以起个名字。

1
2
3

mysql> ALTER TABLE users2 ADD CONSTRAINT PK_users2_id PRIMARY KEY (id);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

再来看一下数据表的结构：

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

可以看到id已经变为主键约束。

添加唯一约束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,…)
与主键约束不同的是唯一约束可以有多个。
下面我们给pid添加一个唯一约束：

1
2
3

mysql> ALTER TABLE users2 ADD UNIQUE (pid);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

检查一下数据表结构:

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

pid已经被设置为唯一约束。

添加外键约束

ALTER TABLE tb1_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] [index_type] (index_col_name,…) reference_definition;
首先看一下要设置的父表的结构（还是我们之前的provinces）：

mysql> SHOW COLUMNS FROM provinces;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type                 | Null | Key | Default | Extra          |
+-------+----------------------+------+-----+---------+----------------+
| id    | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| pname | varchar(20)          | NO   |     | NULL    |                |
+-------+----------------------+------+-----+---------+----------------+
2 rows in set (0.04 sec)

添加一个和id类型一样的列：

1
2
3

mysql> ALTER TABLE users2 ADD cid SMALLINT UNSIGNED;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

接下来设置username的外键约束：

1
2
3

mysql> ALTER TABLE users2 ADD FOREIGN KEY (cid) REFERENCES provinces (id);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看一下：

mysql> SHOW CREATE TABLE users2;
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                                                                                                                 |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users2 | CREATE TABLE `users2` (
  `username` varchar(20) NOT NULL,
  `id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `pid` tinyint(3) unsigned DEFAULT NULL,
  `cid` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `pid` (`pid`),
  KEY `cid` (`cid`),
  CONSTRAINT `users2_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `provinces` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

cid已经被设置为外键约束。

添加/删除默认约束

ALTER TABLE tbl_name ALTER[COLUMN] col_name{SET DEFAULT literal|DROP DEFAULT}

1
2
3

mysql> ALTER TABLE users2 ALTER COLUMN username SET DEFAULT 'pp';
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看列信息：

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

username的默认值被设置为‘pp’。

下面删除默认约束：

1
2
3

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

看下是否被删除：

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

大功告成！

7）删除约束

删除主键约束

ALTER TABLE tbl_name DROP PRIMARY KEY
不需要指定名称，因为一张表只有一个主键

1
2
3

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

查看一下：

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

删除唯一约束

ALTER TABLE tbl_name DROP {INDEX|KEY} index_name
需要指定名称，因为一张表可以有多个唯一约束
我们接下来要删除的是唯一约束，所以首先我们应该查看约束的名字：

mysql> SHOW INDEXES FROM users2\G;
*************************** 1. row ***************************
        Table: users2
   Non_unique: 0
     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. row ***************************
        Table: users2
   Non_unique: 1
     Key_name: cid
 Seq_in_index: 1
  Column_name: cid
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.00 sec)

ERROR:
No query specified

删除pid的约束：

1
2
3

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

现在只剩下一个约束：

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

ERROR:
No query specified

删除外键约束

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol
要想删除外键约束，必须知道外键约束的名字；

mysql> SHOW CREATE TABLE users2;
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                                                               |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users2 | CREATE TABLE `users2` (
  `username` varchar(20) NOT NULL,
  `id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `pid` tinyint(3) unsigned DEFAULT NULL,
  `cid` smallint(5) unsigned DEFAULT NULL,
  KEY `cid` (`cid`),
  CONSTRAINT `users2_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `provinces` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

可以得到外键的名字为 users2_ibfk_1
下面进行删除：

1
2
3

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

查看：

mysql> SHOW CREATE TABLE users2;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                               |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users2 | CREATE TABLE `users2` (
  `username` varchar(20) NOT NULL,
  `id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `pid` tinyint(3) unsigned DEFAULT NULL,
  `cid` smallint(5) unsigned DEFAULT NULL,
  KEY `cid` (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

可以看到cid还是存在索引的，如果不想要还可以进一步进行删除：

1
2
3

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

再来查看一下:

mysql> SHOW CREATE TABLE users2;
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                          |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users2 | CREATE TABLE `users2` (
  `username` varchar(20) NOT NULL,
  `id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `pid` tinyint(3) unsigned DEFAULT NULL,
  `cid` smallint(5) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

cid的索引已经删除。

8）修改列定义和更名数据表

修改列定义

（列名字不存在问题，列的类型或者位置可能存在问题）
ALTER TABLE tbl_name MODIFY [COLUMN] col_name colunm_definition [FIRST | AFTER col_name]

修改列名称

（既可以修改列定义，也可以修改列名称）
ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]

数据表更名

（列名称和数据表名称尽量不要改动，因为实际使用中可能会有关联，导致存储过程出现问题）
方法1
ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name
方法2（可以修改多张表的名称）
RENAME TABLE tbl_name TO new_tbl_name [,tbl_name2 TO new_tbl_name2]…

小结：

约束按照功能来划分：
not null（非空约束）
primary key（主键约束）
unique key（唯一约束）
default（默认约束）
foreign key（外键约束）
按照数据列的数目划分：
表级约束
列级约束
修改数据表的操作：
针对字段的操作：添加/删除字段。修改列定义，修改列名称
针对约束的操作：添加/删除约束
针对数据表的操作：数据表更名（两种方式）

1.外键约束

1）编辑数据表的默认存储引擎：

2）创建数据类型相同的外键列和参照列

3）外键约束的参照操作

4）表级约束与列级约束：

5）修改数据表

添加列

删除列

6）添加约束

添加主键约束

添加唯一约束

添加外键约束

添加/删除默认约束

7）删除约束

删除主键约束

删除唯一约束

删除外键约束

8）修改列定义和更名数据表

修改列定义

修改列名称

数据表更名

小结：