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

 

添加外键

ALTER TABLE tb_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...)reference_definition

 参照表provinces的主键id，添加外键

 ALTER TABLE users2 ADD FOREIGN KEY (pid) REFERENCES 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 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;