数据库MySQL之如何添加主键、唯一、外键和默认约束？

如何添加主键、唯一、外键和默认约束？

---

1. 添加主键约束

CONSTRAINT_PK主键名字为PK

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)

e.g:

ALTER TABLE users ADD CONSTRAINT PK_users_id PRIMARY KEY (id);

---

2. 添加唯一约束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...)

e.g:

ALTER TABLE users ADD UNIQUE (username);

---

3. 添加外键约束

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

e.g:

ALTER TABLE users ADD FOREIGN KEY (pid) REFERENCES provinces (id)

---

4. 添加/删除默认约束 DEFAULT

ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

e.g:

ALTER TABLE users ADD age TINYINT UNSIGNED NULL;
ALTER TABLE users ALTER age SET DEFAULT 15;
ALTER TABLE users ALTER age DROP DEFAULT;