Mysql中的约束总结

数据库常见的约束值如下:

目录

1. 默认值约束

2. 主键约束

3. 唯一约束

4. 外键约束

5. 检查约束

---

---

1. 默认值约束

Notes: 当没有插入数值的时候默认值才会起作用；

演示：

--添加/删除默认约束
ALTER TABLE 表名 ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT};

--添加默认约束 例(假设前置条件都已定义)：
ALTER TABLE users ALTER age SET DEFAULT 16;
--DROP则无需指定默认值，直接后接DEFAULT

2. 主键约束

--添加主键约束
ALTER TABLE 表名 ADD [CONSTRAINT [symbol(约束名)]] PRIMARY KEY [index_type](index_col_name);
--约束名为这个约束的名称，可略
--index_col_name，即为添加约束的列名，指定其进行添加操作，注意有小括号。

--删除主键约束(一张表主键只有一个，不需指定)
ALTER TABLE 表名 DROP PRIMARY KEY;

--添加主键约束 例(假设前置条件都已定义)：
ALTER TABLE users ADD CONSTRAINT PRIKEY_users_id PRIMARY KEY (id);

3. 唯一约束

--添加唯一约束
ALTER TABLE 表名 ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,..);

--删除唯一约束
ALTER TABLE 表名 DROP {INDEX|KEY} index_name;
--为什么指定的是索引名，是因为一张表会有多个唯一约束，且需要注意的是，删除的是唯一约束这个属性，而不是整个字段！

--添加唯一约束 例(假设前置条件都已定义)：
ALTER TABLE 表名 ADD UNIQUE (name);

--删除唯一约束 例(假设前置条件都已定义)：
SHOW INDEXES FROM 表名\G;
--找到要删除唯一约束属性的列，找到key_name,即索引名
ALTER TABLE 表名 DROP INDEX 索引名;

4. 外键约束

--添加外键约束
ALTER TABLE 表名 ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,..) reference_definition;

--删除外键约束
ALTER TABLE 表名 DROP FOREIGN KEY fk_symbol;

--添加外键约束 例(假设前置条件都已定义)：
ALTER TABLE　users ADD FOREIGN KEY (vid) REFERENCE VIP (id);
--VIP是父表，users是子表

--删除外键约束 例(假设前置条件都已定义)：
SHOW CREATE TABLE 表名;
--查看外键约束名称(若没有设置则系统自动设置)，在CONSTRAINT后，有个名称即为外键约束名
ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名;
SHOW CREATE TABLE 表名;
--发现外键约束已经删除，但是还会存在索引，若想删除索引，则：
ALTER TABLE 表名 DROP INDEX 索引名;

5. 检查约束

1、在创建表时设置检查约束

创建表时设置检查约束的语法格式如下

CHECK(<检查约束>)

例 1

在 test_db 数据库中创建 tb_emp7 数据表，要求 salary 字段值大于 0 且小于 10000，SQL 语句和运行结果如下所示。

mysql> CREATE TABLE tb_emp7
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT,
    -> CHECK(salary>0 AND salary<100),
    -> FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
    -> );
Query OK, 0 rows affected (0.37 sec)

2、在修改表时添加检查约束

如果一个表创建完成，可以通过修改表的方式为表添加检查约束。

修改表时设置检查约束的语法格式如下：

ALTER TABLE tb_emp7 ADD CONSTRAINT <检查约束名> CHECK(<检查约束>)

例 2

修改 tb_emp7 数据表，要求 id 字段值大于 0，SQL 语句和运行结果如下所示。

mysql> ALTER TABLE tb_emp7
    -> ADD CONSTRAINT check_id
    -> CHECK(id>0);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

3、删除检查约束

修改表时删除检查约束的语法格式如下：

ALTER TABLE <数据表名> DROP CONSTRAINT <检查约束名>;

例 3

删除 tb_emp7 表中的 check_id 检查约束，SQL 语句和运行结果如下所示：

mysql> ALTER TABLE tb_emp7
    -> DROP CONSTRAINT check_id;
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0