增加主键
ALTER TABLE tb_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_of_name,...);
增加表users2,属性id,username,pid,age
mysql> CREATE TABLE users2(
->id TINYINT UNSIGNED NOT NULL,
-> username VARCHAR(10) NOT NULL,
-> pid SMALLINT UNSIGNED
-> username VARCHAR(10) NOT NULL,
-> pid SMALLINT UNSIGNED
->age TINYINT UNSIGNED NOT NULL,
-> );
-> );
为id列增加主键,命名为PK_users2_id
ALTER TABLE users2 ADD CONSTRAINT PK_users2_id PRIMARY KEY(id);
添加唯一约束
ALTER TABLE tb_name ADD [CONSTRAINT [symbol]] UNIQUE[INDEX|KEY] [index_name] [index_type] (index_col_name,...);
为username添加唯一约束
ALTER TABLE users2 ADD UNIQUE (username);
SHOW CREATE TABLE users2;
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users2 | CREATE TABLE `users2` (
`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`)//唯一约束
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users2 | CREATE TABLE `users2` (
`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`)//唯一约束
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
添加外键
ALTER TABLE tb_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...)reference_definition
参照表provinces的主键id,添加外键
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 | |
+-------+----------------------+------+-----+---------+----------------+
+-------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| pname | varchar(20) | NO | | NULL | |
+-------+----------------------+------+-----+---------+----------------+
ALTER TABLE users2 ADD FOREIGN KEY (pid) REFERENCES provinces(id);
添加/删除默认约束
ALTER TABLE tb_name ALTER [COLUMN] col_name{SET DEFAULT literal | DROP DEFAULT}
为age添加默认值15
ALTER TABLE users2 ALTER age SET DEFAULT 15;
删除age默认值15
ALTER TABLE users2 ALTER age DROP DEFAULT;