第三章 第二节：修改数据表——ALTER(二) 修改约束

增加主键

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     ->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 |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

添加外键

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    |                |
+-------+----------------------+------+-----+---------+----------------+
 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;