MySQL 表约束超详细

目录

一. 主键约束——PRIMARY KEY

1.在建表时设置主键约束

设置单字段主键

设置联合主键

2. 在修改表的时候添加主键约束

3. 删除主键约束

4. 【补充】 MySQL设置主键自增长 ：

二. 外键约束——FOREIGN KEY

1.在创建表时设置外键约束

2. 在修改表时添加外键约束

3. 删除外键约束

三. 唯一约束——UNIQUE KEY

1.在创建表时设置唯一约束

2. 在修改表时添加唯一约束

3. 删除唯一约束

四. 检查约束——CHECK

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

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

3. 删除检查约束

五. 非空约束——NOT NULL

1.在创建表时设置非空约束

2. 在修改表时添加非空约束：

3. 删除非空约束

六. 默认值约束——DEFAULT

1.在创建表时设置默认值约束

2. 在修改表时添加默认值约束

3. 删除默认值

---

在MySQL里，“约束”指的是对表中数据的一种限制约束，它能够确保数据库中数据的准确性和有效性

比如有的数据是必填项，就像身份认证的时候，或者填注册信息的时候，手机号身份证这种就不能空着，所以就有了非空约束；又有的数据比如用户的唯一id，不能跟其他人的一样，所以就需要使用唯一约束等等。

在MySQL中主要有6种约束：主键约束、外键约束、唯一约束、检查约束、非空约束和默认值约束。

一. 主键约束——PRIMARY KEY

“主键约束“是6种约束中使用最多的约束。

在建立数据表的时候，一般情况下，为了方便更快地查找表中的记录，都会要求在表中设置一个“主键”。”主键“是表里面的一个特殊字段，这个字段能够唯一标识该表中的每条信息。

主键分为“单字段主键”和“多字段联合主键”，并且在使用主键的时候需要注意以下几个点：

• 一个表只能定义一个主键；
• 主键值必须唯一标识表中的每一行，并且不能出现null的情况，即表中不能存在有相同主键的两行或两行以上数据，严格遵守唯一性原则；
• 一个字段名只能在联合主键字段表中出现一次；
• 联合主键不能包含不必要的多余字段，以满足最小化原则。

  1.在建表时设置主键约束

  设置单字段主键

  1）在定义字段的时候设置主键约束，语法格式：

    PRIMARY KEY [默认值]

  例：在数据库中创建学生信息数据表st_info，主键为st_id, SQL语句以及运行结果如下：

  create table st_info(st_id int(10) primary key,  name varchar(20),  class varchar(10),  gender varchar(4),  age int(2));mysql> desc st_info;+--------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| st_id | int | NO | PRI | NULL | || name | varchar(20) | YES |     | NULL |       || class | varchar(10) | YES | | NULL   | || gender | varchar(4) | YES |     | NULL |       || age | int | YES | | NULL | |+--------+-------------+------+-----+---------+-------+5 rows in set (0.00 sec)

  2）在定义完所有字段之后指定部件，语法格式：

  [CONSTRAINT ] PRIMARY KEY [字段名]

  例 在数据库中创建学生信息数据表st_info2,主键为st_id, SQL语句以及运行结果如下：

  create table st_info2(st_id int(10),  name varchar(20),  class varchar(10),  gender varchar(4),  age int(2),   primary key(st_id));mysql> desc st_info2;+--------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| st_id | int | NO | PRI | NULL | || name | varchar(20) | YES |     | NULL |       || class | varchar(10) | YES | | NULL   | || gender | varchar(4) | YES |     | NULL |       || age | int | YES | | NULL | |+--------+-------------+------+-----+---------+-------+5 rows in set (0.00 sec)

  设置联合主键

  联合主键即一张表的主键由多个字段组成。

  比如在学生信息表中，可以设置st_id跟name来作为联合主键，语法格式以及SQL语句实现为：

  PRIMARY KEY [字段1，字段2，…,字段n]create table st_info3(  st_id int(10),  name varchar(20),  class varchar(10),  gender varchar(4),  age int(2),   primary key(st_id,name));mysql> desc st_info3;+--------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| st_id | int | NO | PRI | NULL | || name | varchar(20) | NO | PRI | NULL |       || class | varchar(10) | YES | | NULL   | || gender | varchar(4) | YES |     | NULL |       || age | int | YES | | NULL | |+--------+-------------+------+-----+---------+-------+5 rows in set (0.00 sec)

  需要注意的是，在设置联合主键的时候，不能在每个字段名后面直接声明主键约束。

  2. 在修改表的时候添加主键约束

  如果在创建表的时候没有设置主键约束，还可以在修改表时进行添加，但是设置成主键约束的字段不允许有空值。语法格式：

  ALTER TABLE  ADD PRIMARY KEY();mysql> desc st_info4;+--------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| st_id | int | NO | | NULL | || name | varchar(20) | NO |     | NULL |       || class | varchar(10) | YES | | NULL   | || gender | varchar(4) | YES |     | NULL |       || age | int | YES | | NULL | |+--------+-------------+------+-----+---------+-------+

  将st_id与name设置成主键：

  mysql> alter table st_info4 add primary key(st_id,name);Query OK, 0 rows affected (0.04 sec)Records: 0 Duplicates: 0  Warnings: 0mysql> desc st_info4;+--------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| st_id | int | NO | PRI | NULL | || name | varchar(20) | NO | PRI | NULL |       || class | varchar(10) | YES | | NULL   | || gender | varchar(4) | YES |     | NULL |       || age | int | YES | | NULL | |+--------+-------------+------+-----+---------+-------+5 rows in set (0.00 sec)

  3. 删除主键约束

  当一个表中不需要主键约束时，就需要从表中将其删除。

  例 删除st_info4数据表中的主键约束：

  mysql> alter table st_info4 drop primary key;Query OK, 0 rows affected (0.06 sec)Records: 0 Duplicates: 0  Warnings: 0mysql> desc st_info4;+--------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| st_id | int | NO | | NULL | || name | varchar(20) | NO |     | NULL |       || class | varchar(10) | YES | | NULL   | || gender | varchar(4) | YES |     | NULL |       || age | int | YES | | NULL | |+--------+-------------+------+-----+---------+-------+5 rows in set (0.00 sec)

  4. 【补充】 MySQL设置主键自增长 ：

  在MySQL里，当主键定义为自增长后，主键的值就不需要自己再输入数据了，而是由数据库系统根据定义自动赋值，每增加一条记录，主键就会自动根据设置的步长进行增长。

  在MySQL中自增长的关键字是AUTO_INCREMENT，语法格式为：

  字段名 数据类型 AUTO_INCREMENT

  例 创建学生信息表st_info5，指定st_id字段自增，

  create table st_info5(  st_id int(10) primary key auto_increment,    name varchar(20) not null,    class varchar(10),    gender varchar(4),    age int(2));

  此时可以不用再手动插入st_id的数据：

  insert into st_info5(name,class,gender,age) values('王思瑶','管理学1班','女',18),      ('王绮梦','管理学1班','女',18),      ('李若彤','管理学1班','男',18);      mysql> select * from st_info5;+-------+--------+-----------+--------+------+| st_id | name | class     | gender | age |+-------+--------+-----------+--------+------+| 1 | 王思瑶 | 管理学1班 | 女 | 18 || 2 | 王绮梦 | 管理学1班 | 女 | 18 || 3 | 李若彤 | 管理学1班 | 男 | 18 |+-------+--------+-----------+--------+------+3 rows in set (0.01 sec)

  但是在设置学号的时候不一定是从1开始的，比如要从2000150001开始，则需要在建表之后设置自增的起始数据：

  create table st_info6(    st_id int(10) primary key auto_increment,    name varchar(20) not null,    class varchar(10),    gender varchar(4),    age int(2))auto_increment=2000150001;insert into st_info6(name,class,gender,age) values('王思瑶','管理学1班','女',18),      ('王绮梦','管理学1班','女',18),      ('李若彤','管理学1班','男',18);      mysql> select * from st_info6;+------------+--------+-----------+--------+------+| st_id | name | class     | gender | age |+------------+--------+-----------+--------+------+| 2000150001 | 王思瑶 | 管理学1班 | 女 | 18 || 2000150002 | 王绮梦 | 管理学1班 | 女 | 18 || 2000150003 | 李若彤 | 管理学1班 | 男 | 18 |+------------+--------+-----------+--------+------+3 rows in set (0.00 sec)

  值得注意的地方是，如果在插入某一行数据产生了报错，则最终自增字段可能会出现不连续的情况。

  二. 外键约束——FOREIGN KEY

  外键约束是表的一个特殊字段，普遍会和主键约束一起使用，用来确保数据的一致性。

  对于两个具有关联关系的表来说，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。

  所以外键就是用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。

  在定义外键时需要遵守以下规则：

  • 主表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则主表与从表是同一个表，这样的表称做自参照表，这种结构称做自参照完整性；
  • 必须为主表定义主键；
  • 主键不能包含空值，但允许在外键中出现空值；
  • 在主表的表名后面指定列名或列名的组合，这个列或列的组合必须是主表的主键或候选键；
  • 外键中列的数目必须和主表的主键中列的数目相同；
  • 外键中列的数据类型必须和主表主键中对应列的数据类型相同。

    1.在创建表时设置外键约束

    在建表语句中，可以加入关键字FOREIGN KEY来指定外键，用REFERENCES来连接与主表的关系语法格式：

    CONSTRAINT FOREIGN KEY (字段名1，字段名2...)REFERENCES (主键字段名)

    例：

    ① 使用1部分创建的st_info5数据表作为主表：

    mysql> desc st_info5;+--------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+----------------+| st_id | int | NO | PRI | NULL | auto_increment || name | varchar(20) | NO |     | NULL |                || class  | varchar(10) | YES  | | NULL    | || gender | varchar(4) | YES |     | NULL |                || age | int | YES | | NULL | |+--------+-------------+------+-----+---------+----------------+

    ② 创建tb_1数据表，并在表上创建外键约束，使其中course_id作为外键关联到表st_info5的主键st_id：

    create table tb_1(    course_id int(8) not null auto_increment,    course_name varchar(25) not null,    constraint course_Choosing    foreign key fk_course(course_id)    references st_info5(st_id))auto_increment=20015001;mysql> desc tb_1;+-------------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------------+-------------+------+-----+---------+----------------+| course_id | int | NO | MUL | NULL | auto_increment || course_name | varchar(25) | NO |     | NULL |                |+-------------+-------------+------+-----+---------+----------------+

    上面语句执行成功之后，在表tb_1中添加了course_Choosing的约束名称，以及外键名称为fk_course的course_id字段，依赖于表st_info5的主键st_id。

    2. 在修改表时添加外键约束

    同样的可以在创建表之后再修改，SQL语句如下：

    alter table tb_1 add constraint course_Choosingforeign key fk_course(course_id)references st_info5(st_id);

    这里需要注意的是，从表的外键关联的必须是主表的主键，且主键和外键的数据类型必须一致。例如两者都是int型或者都是char型数据。

    3. 删除外键约束

    当一个表中不需要外键约束时，就需要从表中将其删除。外键一旦删除，就会解除主表和从表间的关联关系。

    删除的语法格式：

    ALTER TABLE  DROP FOREIGN KEY ;

    例：

    alter table tb_1dropforeign key fk_course;

    三. 唯一约束——UNIQUE KEY

    唯一约束就是指所有记录中字段的值不能重复出现，比如给'id'字段加上唯一约束之后，每条记录的id值都是唯一的，不能出现重复的情况。

    唯一约束与主键约束有一个相似的地方，就是它们都能够确保列的唯一性。与主键约束不同的是，唯一约束在一个表中可以有多个，并且设置唯一约束的列是允许有空值的，虽然只能有一个空值。

    例如，在用户信息表中，要避免表中的用户名重名，就可以把用户名列设置为唯一约束。

    1.在创建表时设置唯一约束

    唯一约束可以在创建表的时候直接进行设置，通常设置在除了主键以外的其他列上，语法格式如下：

      UNIQUE

    例 创建图书馆所有书本明细表book_info ，设定书本bk_id为主键，书本ISBN为唯一键，SQL语句如下：

    create table book_info(    bk_id int primary key,    ISBN varchar(17) unique,    author varchar(50),    Publisher varchar(20));mysql> desc book_info;+-----------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-----------+-------------+------+-----+---------+-------+| bk_id | int | NO | PRI | NULL | || ISBN | varchar(17) | YES | UNI | NULL |       || author | varchar(50) | YES | | NULL | || Publisher | varchar(20) | YES |     | NULL |       |+-----------+-------------+------+-----+---------+-------+4 rows in set (0.00 sec)

    2. 在修改表时添加唯一约束

    语法格式：

    ALTER TABLE  ADD CONSTRAINT  UNIQUE();

    例 将book_info表中author字段设置为唯一约束：

    alter table book_infoaddconstraint uni_author unique(author);mysql> desc book_info;+-----------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-----------+-------------+------+-----+---------+-------+| bk_id | int | NO | PRI | NULL | || ISBN | varchar(17) | YES | UNI | NULL |       || author | varchar(50) | YES | UNI | NULL | || Publisher | varchar(20) | YES |     | NULL |       |+-----------+-------------+------+-----+---------+-------+4 rows in set (0.01 sec)

    3. 删除唯一约束

    语法格式：

    ALTER TABLE  DROP INDEX ;

    例 删除book_info表中的唯一约束uni_author；

    alter table book_infodrop index uni_author;mysql> desc book_info;+-----------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-----------+-------------+------+-----+---------+-------+| bk_id | int | NO | PRI | NULL | || ISBN | varchar(17) | YES | UNI | NULL |       || author | varchar(50) | YES | | NULL | || Publisher | varchar(20) | YES |     | NULL |       |+-----------+-------------+------+-----+---------+-------+4 rows in set (0.00 sec)

    四. 检查约束——CHECK

    检查约束是用来检查数据表中字段值有效性的一种约束。

    例如，学生信息表中的年龄字段是不能有负数的，并且数值范围也是有限制的。如果是大学生，正常年龄一般应该在 18~30 岁之间。在设置字段的检查约束时需要根据实际情况进行设置，这样能够减少无效数据的输入。

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

    语法格式：

    CHECK()

    例 在persons表创建时id_p列创建check约束，约束条件为id_p列数据必须只包含大于0的整数：

    create table persons(    id_p int not null,    lastname varchar(255) not null,    firstname varchar(255),    address varchar(255),    city varchar(255),    check (id_p>0));mysql> desc persons;+-----------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-----------+--------------+------+-----+---------+-------+| id_p | int | NO | | NULL | || lastname | varchar(255) | NO |     | NULL |       || firstname | varchar(255) | YES | | NULL | || address | varchar(255) | YES |     | NULL |       || city | varchar(255) | YES | | NULL | |+-----------+--------------+------+-----+---------+-------+

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

    语法格式：

    ALTER TABLE  ADD CONSTRAINT  CHECK()

    例 修改persons数据表，要求city字段为“上海”：

    create table persons(    id_p int not null,    lastname varchar(255) not null,    firstname varchar(255),    address varchar(255),    city varchar(255),    constraint chk_person check (id_p>0 and city='sandnes'));alter table persons add constraint chk_ct check(city='上海');mysql> desc persons;+-----------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-----------+--------------+------+-----+---------+-------+| id_p | int | NO | | NULL | || lastname | varchar(255) | NO |     | NULL |       || firstname | varchar(255) | YES | | NULL | || address | varchar(255) | YES |     | NULL |       || city | varchar(255) | YES | | NULL | |+-----------+--------------+------+-----+---------+-------+5 rows in set (0.00 sec)

    3. 删除检查约束

    语法格式：

    ALTER TABLE  DROP CONSTRAINT ;

    例 删除persons表中的check_ct约束：

    alter table persons drop constraint chk_ct;

    五. 非空约束——NOT NULL

    非空约束是用来约束表中的字段不能为空。在表中某个列的定义后加上关键字NOT NULL作为限定词，来约束该列的取值不能为空。

    例如，在学生信息表中，如果不添加学生姓名，那么这条记录是无效的，这种情况下就可以为用户名字段设置非空约束。

    1.在创建表时设置非空约束

    语法格式：

    CREATE TABLE (    ...  NOT NULL)...);

    例 创建学生信息表st_info, 指定st_id为主键，学生姓名不能为空：

    create table st_info(    st_id int(10) primary key,    name varchar(20) not null,    class varchar(10),    gender varchar(4),    age int(2));mysql> desc st_info;+--------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| st_id | int | NO | PRI | NULL | || name | varchar(20) | NO |     | NULL |       || class  | varchar(10) | YES  | | NULL    | || gender | varchar(4) | YES |     | NULL |       || age | int | YES | | NULL | |+--------+-------------+------+-----+---------+-------+5 rows in set (0.00 sec)

    2. 在修改表时添加非空约束：

    如果在创建表的时候忘记了为字段设置非空约束，可以通过修改表进行非空约束的添加。

    语法格式：

    ALTER TABLE CHANGE COLUMN   NOT NULL;

    例 修改学生信息表st_info中class字段，设置成非空约束：

    mysql> alter table st_info    -> change column class    -> class varchar(20) not null;Query OK, 0 rows affected (0.06 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc st_info;+--------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| st_id | int | NO | PRI | NULL | || name | varchar(20) | NO |     | NULL |       || class  | varchar(20) | NO   | | NULL    | || gender | varchar(4) | YES |     | NULL |       || age | int | YES | | NULL | |+--------+-------------+------+-----+---------+-------+5 rows in set (0.00 sec)

    3. 删除非空约束

    可以在修改语句中对非空约束进行删除。

    语法格式：

    ALTER TABLE CHANGE COLUMN    NULL;

    例 修改学生信息表st_info，将class字段的非空约束删除：

    mysql> alter table st_info    -> change column class    -> class varchar(20) null;Query OK, 0 rows affected (0.04 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc st_info;+--------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| st_id | int | NO | PRI | NULL | || name | varchar(20) | NO |     | NULL |       || class  | varchar(20) | YES  | | NULL    | || gender | varchar(4) | YES |     | NULL |       || age | int | YES | | NULL | |+--------+-------------+------+-----+---------+-------+5 rows in set (0.00 sec)

    六. 默认值约束——DEFAULT

    默认值约束是当数据表中某个字段不输入值的时候，自动为其添加一个已经设置好的值。

    例如，在注册学生信息时，如果不输入学生的性别，那么会默认设置一个性别或者输入一个“未知”。

    默认值约束一般是用在已经设置了非空约束的列，这样能够防止数据表在录入数据时出现错误。

    1.在创建表时设置默认值约束

    语法格式：

    CREATE TABLE (    ...  DEFAULT     ...);

    例 创建学生信息表st_info，设置年龄age默认值为18：

    create table st_info(    st_id int(10) primary key,    name varchar(20) not null,    class varchar(10),    gender varchar(4),    age int(2) default 18);mysql> desc st_info;+--------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| st_id | int | NO | PRI | NULL | || name | varchar(20) | NO |     | NULL |       || class  | varchar(10) | YES  | | NULL    | || gender | varchar(4) | YES |     | NULL |       || age | int | YES | | 18      | |+--------+-------------+------+-----+---------+-------+5 rows in set (0.01 sec)

    2. 在修改表时添加默认值约束

    语法格式：

    ALTER TABLE CHANGE COLUMN    DEFAULT ;

    例 修改学生信息表st_info，将性别设置默认值为“保密”：

    mysql> alter table st_info    -> change column gender    -> gender varchar(4) default '保密';Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc st_info;+--------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| st_id | int | NO | PRI | NULL | || name | varchar(20) | NO |     | NULL |       || class  | varchar(10) | YES  | | NULL    | || gender | varchar(4) | YES |     | 保密 |       || age | int | YES | | 18      | |+--------+-------------+------+-----+---------+-------+5 rows in set (0.00 sec)

    需要注意的是，这里的gender字段为字符串型数据，需要加上引号。

    3. 删除默认值

    删除默认值即为将默认值设置为空（null）

    语法格式：

    ALTER TABLE CHANGE COLUMN    DEFAULT NULL;

    例 修改学生信息表st_info, 将gender性别字段的默认值删除掉：

    mysql> alter table st_info    -> change column gender    -> gender varchar(4) default null;Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc st_info;+--------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| st_id | int | NO | PRI | NULL | || name | varchar(20) | NO |     | NULL |       || class  | varchar(10) | YES  | | NULL    | || gender | varchar(4) | YES |     | NULL |       || age | int | YES | | 18      | |+--------+-------------+------+-----+---------+-------+5 rows in set (0.00 sec)