第三章 第二节：修改数据表——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;