修改数据表--删除约束

1、删除主键约束

ALTER TABLE tbl_name DROP PRMIARY KEY;

任何一张数据表有且只有一个主键，所以不用指定名称。

mysql> SHOW COLUMNS FROM users3;

+---------  -+---------------------   -+------+-----  +---------+-------+
| Field        | Type                       | Null | Key   | Default | Extra |
+---------  -+----------------------   +------+-----  +---------+-------+
| username | varchar(10)             | NO   | UNI  | NULL     |        |
| pid          | smallint(5) unsigned | YES | MUL  | NULL    |        |
| id            | smallint(5) unsigned | NO  | PRI    | 0         |        |
| age         | tinyint(3) unsigned    | NO  |         | NULL    |        |
+----------+----------------------      +------+-----+---------+-------+

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

mysql>
mysql> SHOW COLUMNS FROM users3;
+---------  -+---------------------   -+------+-----  +---------+-------+
| 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    |        |
+----------+----------------------      +------+-----+---------+-------+

2、删除唯一约束

ALTER TABLE tbl_name DROP  {INDEX|KEY} index_name

mysql> SHOW INDEXES FROM users3\G;
*************************** 1. row ***************************
Table: users3
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: users3
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)

ERROR:
No query specified

删除约束

mysql> ALTER TABLE users3 DROP KEY username;
Query OK, 0 rows affected (0.30 sec)
Records: 0 Duplicates: 0 Warnings: 0

username字段依然存在，约束已经不存在了

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

mysql> SHOW INDEXES FROM users3\G;  #刚才有两个约束，现在只剩下一个了
*************************** 1. row ***************************
Table: users3
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)

3、删除外键约束

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol

显示数据表创建时的信息

mysql> SHOW CREATE TABLE users3;

| Table | Create Table

| users3 | CREATE TABLE `users3` (
`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 `users3_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `province` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
删除外键约束

mysql> ALTER TABLE users3 DROP FOREIGN KEY users3_ibfk_1;
Query OK, 0 rows affected (0.30 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW CREATE TABLE users3;

| Table | Create Table

| users3 | CREATE TABLE `users3` (
`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 |
删除pid的索引

mysql> ALTER TABLE users3 DROP INDEX pid;
Query OK, 0 rows affected (0.25 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW CREATE TABLE users3;

| Table | Create Table

| users3 | CREATE TABLE `users3` (
`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 |

转载于:https://www.cnblogs.com/toudoubao/p/6605160.html