修改数据表--删除约束
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 |
