1、添加主键约束
ALTER TABLE tbl_name ADD [CONSTRAINT[symbol] ] PRIMARY KEY[index_type] (index_col_name,...);
mysql> CREATE TABLE users3(
-> username VARCHAR(10) NOT NULL,
-> pid SMALLINT UNSIGNED
-> );
Query OK, 0 rows affected (0.11 sec)
mysql> ALTER TABLE users3 ADD id SMALLINT UNSIGNED;
Query OK, 0 rows affected (0.27 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> SHOW COLUMNS FROM users3;
+---------- +----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------- +----------------------+------+-----+---------+-------+
| username | varchar(10) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | | NULL | |
| id | smallint(5) unsigned | YES | | NULL | |
+----------+----------------------+------+-----+---------+-------+
把id设置为主键约束
mysql> ALTER TABLE users3 ADD CONSTRAINT PK_users3_id PRIMARY KEY (id);
Query OK, 0 rows affected (0.29 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW COLUMNS FROM users3;
+--------- -+-------------------- --+------+---- -+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------- -+--------------------- -+------+-----+---------+-------+
| username | varchar(10) | NO | | NULL | |
| pid | smallint(5) unsigned | YES | | NULL | |
| id | smallint(5) unsigned | NO | PRI | 0 | |
+---------- +--------------------- -+------+-----+--------- +-------+
2、添加唯一约束
ALTER TABLE tbl_name ADD [CONSTRAINT[symbol] ] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name, ... );
为username添加唯一约束
mysql> ALTER TABLE users3 ADD UNIQUE (username);
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW COLUMNS FROM users3;
+---------- +--------------------- -+------ +---- +---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------- +-------------------- --+------ +---- -+-- -------+-------+
| username | varchar(10) | NO | UNI | NULL | |
| pid | smallint(5) unsigned | YES | | NULL | |
| id | smallint(5) unsigned | NO | PRI | 0 | |
+----------+----------------------+------+-----+---------+-------+
3、添加外键约束
ALTER TABLE tbl_name ADD [CONSTRAINT[symbol] ] FOREIGN KEY [index_name] (index_col_name, ... ) reference_definition
mysql> ALTER TABLE users3 ADD FOREIGN KEY (pid) REFERENCES province (id);
Query OK, 0 rows affected (0.33 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',
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
KEY `pid` (`pid`),
CONSTRAINT `users3_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `province` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
4、添加/删除默认约束
mysql> ALTER TABLE users3 ADD age TINYINT UNSIGNED NOT NULL;
Query OK, 0 rows affected (0.27 sec)
Records: 0 Duplicates: 0 Warnings: 0
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 | |
+----------+---------------------- +------+-----+---------+-------+
4 rows in set (0.01 sec)
添加默认约束age 15
mysql> ALTER TABLE users3 ALTER age SET DEFAULT 15;
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
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 | | 15 | |
+----------+---------------------- +------+-----+---------+-------+
删除默认约束
mysql> ALTER TABLE users3 ALTER age DROP DEFAULT;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
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 | |
+----------+---------------------- +------+-----+---------+-------+
4 rows in set (0.01 sec)