constraint mysql,MySQL约束总结(CONSTRAINT)

约束的含义

一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性。MySQL数据库通过约束(constraints)防止无效的数据进入到表中，以保护数据的实体完整性。

约束的分类

在 MySQL 中，主要有六种约束：

1、NOT NULL：非空约束，用于约束该字段的值不能为空。比如姓名、学号等。

2、DEFAULT：默认值约束，用于约束该字段有默认值，约束当数据表中某个字段不输入值时，自动为其添加一个已经设置好的值。比如性别。

3、PRIMARY KEY：主键约束，用于约束该字段的值具有唯一性，至多有一个，可以没有，并且非空。比如学号、员工编号等。

4、UNIQUE：唯一约束，用于约束该字段的值具有唯一性，可以有多个，可以没有，可以为空。比如座位号。

5、CHECK：检查约束，用来检查数据表中，字段值是否有效。比如年龄、性别。

6、FOREIGN KEY：外键约束，外键约束经常和主键约束一起使用，用来确保数据的一致性，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值。在从表添加外键约束，用于引用主表中某列的值。比如学生表的专业编号，员工表的部门编号，员工表的工种编号。

主要归类为列级约束和表级约束

列级约束：NOT NULL | DEFAULT | PRIMARY KEY | UNIQUE | CHECK

表级约束：PRIMARY KEY | UNIQUE | CHECK | FOREIGN KEY

实例

列约束：在字段名和类型后面追加约束类型即可

表约束：在各个列字段的最下面，CONSTRAINT 开头进行约束

CREATE TABLE 表名(

字段名 字段类型 列级约束,

字段名 字段类型,

表级约束

)

创建表时添加约束

-- 列约束

mysql> CREATE TABLE teachers(

-> id INT PRIMARY KEY,

-> stuName VARCHAR(20) NOT NULL UNIQUE,

-> gender CHAR(1) CHECK(gender='W' OR gender='M'),

-> seat INT UNIQUE,

-> age INT DEFAULT 18

-> );

Query OK, 0 rows affected (0.04 sec)

-- 列约束和表约束

mysql> CREATE TABLE students (

-> id INT PRIMARY KEY AUTO_INCREMENT,

-> name VARCHAR(20) NOT NULL,

-> age INT NOT NULL DEFAULT 18,

-> gender CHAR CHECK(gender IN ('W','M')),

-> tid INT,

-> CONSTRAINT fk FOREIGN KEY(tid) REFERENCES teachers(id)

-> );

Query OK, 0 rows affected (0.04 sec)

mysql> show index from teachers;

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| teachers | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |

| teachers | 0 | stuName | 1 | stuName | A | 0 | NULL | NULL | | BTREE | | |

| teachers | 0 | seat | 1 | seat | A | 0 | NULL | NULL | YES | BTREE | | |

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

3 rows in set (0.00 sec)

mysql> show index from students;

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| students | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |

| students | 1 | fk | 1 | tid | A | 0 | NULL | NULL | YES | BTREE | | |

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

2 rows in set (0.00 sec)

修改表时添加约束

mysql> CREATE TABLE info (

-> id INT,

-> name VARCHAR(20),

-> age INT,

-> gender CHAR,

-> tid INT

-> );

Query OK, 0 rows affected (0.02 sec)

mysql> ALTER TABLE info MODIFY COLUMN id INT PRIMARY KEY; # 添加列主键约束

Query OK, 0 rows affected (0.05 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE info MODIFY COLUMN name VARCHAR(20) NOT NULL; # 添加列非空约束

Query OK, 0 rows affected (0.05 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE info MODIFY COLUMN age INT DEFAULT 18; # 添加列默认约束

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE info MODIFY COLUMN tid INT UNIQUE; # 添加列唯一约束

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc info;

+--------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------+-------------+------+-----+---------+-------+

| id | int(11) | NO | PRI | NULL | |

| name | varchar(20) | NO | | NULL | |

| age | int(11) | YES | | 18 | |

| gender | char(1) | YES | | NULL | |

| tid | int(11) | YES | UNI | NULL | |

+--------+-------------+------+-----+---------+-------+

5 rows in set (0.00 sec)

mysql> alter table info drop primary key; # 删除列主键约束

Query OK, 0 rows affected (0.08 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE info ADD PRIMARY KEY(id); # 添加“表”主键约束

Query OK, 0 rows affected (0.05 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE info ADD UNIQUE(tid); # 添加“表”唯一约束

Query OK, 0 rows affected, 1 warning (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 1

mysql> ALTER TABLE info ADD CONSTRAINT ue UNIQUE(tid); # 添加“表”唯一约束

Query OK, 0 rows affected, 1 warning (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 1

mysql> desc info;

+--------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------+-------------+------+-----+---------+-------+

| id | int(11) | NO | PRI | NULL | |

| name | varchar(20) | NO | | NULL | |

| age | int(11) | YES | | 18 | |

| gender | char(1) | YES | | NULL | |

| tid | int(11) | YES | UNI | NULL | |

+--------+-------------+------+-----+---------+-------+

5 rows in set (0.01 sec)

mysql> ALTER TABLE info ADD CONSTRAINT ck CHECK(gender IN ('W','M')); # 添加“表”检查约束

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE info ADD CONSTRAINT fy FOREIGN KEY(tid) REFERENCES teachers(id); # 添加“表”外键约束

Query OK, 0 rows affected (0.08 sec)

Records: 0 Duplicates: 0 Warnings: 0

修改表时删除约束

mysql> ALTER TABLE info MODIFY COLUMN name varchar(20) NULL; # 删除列非空约束

Query OK, 0 rows affected (0.06 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE info MODIFY COLUMN age INT(11); # 删除列默认约束

Query OK, 0 rows affected (0.00 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE info DROP PRIMARY KEY; # 删除“表”主键约束

Query OK, 0 rows affected (0.07 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE info DROP INDEX tid; # 删除“表”唯一约束

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table info \G

*************************** 1. row ***************************

Table: info

Create Table: CREATE TABLE `info` (

`id` int(11) NOT NULL,

`name` varchar(20) DEFAULT NULL,

`age` int(11) DEFAULT NULL,

`gender` char(1) DEFAULT NULL,

`tid` int(11) DEFAULT NULL,

UNIQUE KEY `tid_2` (`tid`),

UNIQUE KEY `ue` (`tid`),

CONSTRAINT `fy` FOREIGN KEY (`tid`) REFERENCES `teachers` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=gb2312

1 row in set (0.00 sec)

mysql> ALTER TABLE info DROP INDEX ue; # 删除“表”唯一约束

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE info DROP FOREIGN KEY fy; # 删除“表”外键约束

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table info \G

*************************** 1. row ***************************

Table: info

Create Table: CREATE TABLE `info` (

`id` int(11) NOT NULL,

`name` varchar(20) DEFAULT NULL,

`age` int(11) DEFAULT NULL,

`gender` char(1) DEFAULT NULL,

`tid` int(11) DEFAULT NULL,

UNIQUE KEY `tid_2` (`tid`)

) ENGINE=InnoDB DEFAULT CHARSET=gb2312

1 row in set (0.00 sec)

mysql> desc info;

+--------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------+-------------+------+-----+---------+-------+

| id | int(11) | NO | | NULL | |

| name | varchar(20) | YES | | NULL | |

| age | int(11) | YES | | NULL | |

| gender | char(1) | YES | | NULL | |

| tid | int(11) | YES | UNI | NULL | |

+--------+-------------+------+-----+---------+-------+

5 rows in set (0.00 sec)

主键和唯一的区别

约束名称保证唯一性是否允许为空一个表中可以有多少个是否允许组合主键√×最多有1个，可以没有√(不推荐)

唯一√√可以有多个√(不推荐)

标签：总结,info,rows,CONSTRAINT,mysql,约束,sec,MySQL,NULL

来源： https://blog.csdn.net/z_johnny/article/details/113820405