约束类型

约束类型

从查询information_schema中查询指定表中的约束

mysql> USE INFORMATION_SCHEMA;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT CONSTRAINT_NAME FROM TABLE_CONSTRAINTS WHERE TABLE_NAME='t1';
+-----------------+
| CONSTRAINT_NAME |
+-----------------+
| PRIMARY         |
+-----------------+
1 row in set (0.01 sec)
sqlCopy
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

主键约束 PRIMARY KEY

添加主键约束

  1. 建表时直接添加

    CREATE TABLE t_user(
        user_id INT(10) PRIMARY KEY,
        ...
    );
    
    CREATE TABLE t_user(
        user_id INT(10),
        PRIMARY KEY(user_id)
    );
    
    CREATE TABLE t_user(
        user_id INT(10),
        user_name VARCHAR(30),
        CONSTRAINT PK_ID_NAME PRIMARY KEY(user_id,user_name)
    );
    
    sqlCopy
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
  2. 通过ALTER语句

    ALTER TABLE t_user ADD PRIMARY KEY(user_id);
    ALTER TABLE t_user ADD CONSTRAINT PK_ID_NAME PRIMARY KEY(user_id);
    
    sqlCopy
    • 1
    • 2
    ALTER TABLE t_user MODIFY user_id INT(10) PRIMARY KEY;
    
    sqlCopy
    • 1

    中间的INT(10)不可少,否则报错

    ALTER TABLE t_user CHANGE user_id user_id INT(10) PRIMARY KEY;
    
    sqlCopy
    • 1

    使用 CHANGE 子句, 语法有很大的不同。 在 CHANGE 关键字之后,紧跟着的是你要修改的字段名,然后指定新字段名及类型。

删除主键约束

ALTER TABLE t_user DROP PRIMARY KEY;
sqlCopy
  • 1

注:主键约束相当于(唯一约束+非空约束)
一张表中最多有一个主键约束,如果设置多个主键,就会出现如下提示:
Multiple primary key defined!!!
删除主键约束前,如果有自增长需要先删除自增长,如果不删除自增长就无法删除主键约束

唯一约束 UNIQUE

添加唯一约束

  1. 建表时直接添加

    CREATE TABLE t_user(user_id INT(10) UNIQUE);
    
    CREATE TABLE t_user(
        user_id INT(10),
        UNIQUE KEY(user_id)
    );
    
    CREATE TABLE t_user(
        user_id INT(10),
        user_name VARCHAR(30),
        CONSTRAINT UN_PHONE_EMAIL UNIQUE(user_id,user_name)
    );
    
    sqlCopy
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
  2. 通过ALTER语句

    ALTER TABLE t_user MODIFY user_id INT(10) UNIQUE;
    ALTER TABLE t_user CHANGE user_id user_id INT(10) UNIQUE;
    ALTER TABLE t_user ADD UNIQUE(user_id);
    ALTER TABLE t_user ADD UNIQUE KEY(user_id);
    ALTER TABLE t_user ADD CONSTRAINT UN_ID UNIQUE(user_id);
    ALTER TABLE t_user ADD CONSTRAINT UN_ID UNIQUE KEY(user_id);
    
    sqlCopy
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

删除唯一性约束

ALTER TABLE t_user DROP INDEX user_id;
sqlCopy
  • 1

唯一但是可以为空(空和空不相等)

非空约束 NOT NULL

添加非空约束

  1. 建表时直接添加

    CREATE TABLE t_user(user_id INT(10) NOT NULL);
    
    sqlCopy
    • 1
  2. 通过ALTER 语句

    ALTER TABLE t_user MODIFY user_id INT(10) NOT NULL;
    ALTER TABLE t_user CHANGE user_id user_id INT(10) NOT NULL;
    
    sqlCopy
    • 1
    • 2

删除非空约束

ALTER TABLE t_user MODIFY user_id INT(10);
ALTER TABLE t_user CHANGE user_id user_id INT(10);
sqlCopy
  • 1
  • 2

自增长约束 AUTO_INCREMENT

添加自增长约束

  1. 在创建表的时候添加

    CREATE TABLE t_user(user_id INT(10) AUTO_INCREMENT PRIMARY KEY);
    
    sqlCopy
    • 1
  2. 通过ALTER语句

       ALTER TABLE t_user MODIFY user_id INT(10) AUTO_INCREMENT;
    ALTER TABLE t_user CHANGE user_id user_id INT(10) AUTO_INCREMENT;
    
    sqlCopy
    • 1
    • 2

删除自增长约束

ALTER TABLE t_user MODIFY user_id INT(10);
ALTER TABLE t_user CHANGE user_id user_id INT(10);
sqlCopy
  • 1
  • 2

一张表只能有一个自增长列,并且该列必须定义了约束(可以是主键约束,也可以是唯一约束,也可以是外键约束,但是不可以是非空和检查约束)
不过自增长一般配合主键使用,并且只能在数字类型中使用

外键约束 FOREIGN KEY

对应的字段只能是主键或者唯一约束修饰的字段
创建外键约束
主表:

CREATE TABLE class(
    cla_id INT(6) AUTO_INCREMENT PRIMARY KEY,
    cla_name VARCHAR(30) NOT NULL UNIQUE
);
sqlCopy
  • 1
  • 2
  • 3
  • 4

从表:

CREATE TABLE students(
    stu_id INT(10) AUTO_INCREMENT PRIMARY KEY,
    stu_name VARCHAR(30) NOT NULL,
    stu_score FLOAT(5,2) DEFAULT 0.0,
    cla_id INT(10),
    CONSTRAINT FK_CLA_ID FOREIGN KEY(cla_id) REFERENCES class(cla_id) ##添加外键约束
);
sqlCopy
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

也可以这样添加:

ALTER TABLE students ADD CONSTRAINT FK_CLA_ID FROEIGN KEY(cla_id) REFERENCES class(cla_id);
sqlCopy
  • 1

删除外键约束

ALTER TABLE students DROP FOREIGN KEY FK_CLA_ID;
sqlCopy
  • 1

外键中的级联关系有以下几种情况:

  • ON DELETE CASCADE 删除主表中的数据时,从表中的数据随之删除
  • ON UPDATE CASCADE 更新主表中的数据时,从表中的数据随之更新
  • ON DELETE SET NULL 删除主表中的数据时,从表中的数据置为空
  • 默认 删除主表中的数据前需先删除从表中的数据,否则主表数据不会被删除

ON DELETE CASCADE

CREATE TABLE students(
    stu_id INT(10) AUTO_INCREMENT PRIMARY KEY,
    stu_name VARCHAR(30) NOT NULL,
    stu_score FLOAT(5,2) DEFAULT 0.0,
    cla_id INT(10),
    CONSTRAINT FK_CLA_ID FOREIGN KEY(cla_id) REFERENCES class(cla_id) ON DELETE CASCADE
);
sqlCopy
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

ON UPDATE CASCADE

CREATE TABLE students(
    stu_id INT(10) AUTO_INCREMENT PRIMARY KEY,
    stu_name VARCHAR(30) NOT NULL,
    stu_score FLOAT(5,2) DEFAULT 0.0,
    cla_id INT(10),
    CONSTRAINT FK_CLA_ID FOREIGN KEY(cla_id) REFERENCES class(cla_id) ON UPDATE CASCADE
);
sqlCopy
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

ON DELETE SET NULL

CREATE TABLE students(
    stu_id INT(10) AUTO_INCREMENT PRIMARY KEY,
    stu_name VARCHAR(30) NOT NULL,
    stu_score FLOAT(5,2) DEFAULT 0.0,
    cla_id INT(10),
    CONSTRAINT FK_CLA_ID FOREIGN KEY(cla_id) REFERENCES class(cla_id) ON DELETE SET NULL
);
sqlCopy
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

插入数据时,先插入主表中的数据,再插入从表中的数据。
删除数据时,先删除从表中的数据,再删除主表中的数据。

默认约束 DEFAULT

添加默认约束

  1. 在创建表的时候添加

    CREATE TABLE t_user(user_id INT(10) DEFAULT  3);
    
    sqlCopy
    • 1
  2. 通过ALTER语句

    ALTER TABLE t_user MODIFY user_id INT(10) DEFAULT  2;
    ALTER TABLE t_user CHANGE user_id user_id INT(10) DEFAULT  2;
    
    sqlCopy
    • 1
    • 2

删除默认约束

ALTER TABLE t_user MODIFY user_id INT(10);
ALTER TABLE t_user CHANGE user_id user_id INT(10);
sqlCopy
  • 1
  • 2

无符号位 UNSIGNED

添加无符号

  1. 在创建表的时候添加
    CREATE TABLE t_user(user_id INT(10) UNSIGNED);
    
    sqlCopy
    • 1
  2. 通过ALTER语句
    ALTER TABLE t_user MODIFY user_id INT(10) UNSIGNED;
    ALTER TABLE t_user CHANGE user_id user_id INT(10) UNSIGNED;
    
    sqlCopy
    • 1
    • 2

删除无符号

ALTER TABLE t_user MODIFY user_id INT(10);
ALTER TABLE t_user CHANGE user_id user_id INT(10);
sqlCopy
  • 1
  • 2

零填充 ZEROFILL

添加零填充

  1. 在创建表的时候添加

    CREATE TABLE t_user(user_id INT(10) ZEROFILL);
    
    sqlCopy
    • 1
  2. 通过ALTER语句

    ALTER TABLE t_user MODIFY user_id INT(10) ZEROFILL;
    ALTER TABLE t_user CHANGE user_id user_id INT(10) ZEROFILL;
    
    sqlCopy
    • 1
    • 2

删除零填充

ALTER TABLE t_user MODIFY user_id INT(10);
ALTER TABLE t_user CHANGE user_id user_id INT(10);
sqlCopy
  • 1
  • 2

零填充会将未将有效位以外的位用零来显示,比如某字段数据类型为INT(5),而插入的值为2,那么零填充会显示00002
但是,这个效果在Navicat for MySQL中显示不出来,只有在DOS窗口下才能显示

检查约束 CHECK

CREATE TABLE class(
    cla_id INT(6) AUTO_INCREMENT PRIMARY KEY,
    cla_name VARCHAR(30) NOT NULL UNIQUE,
    CHECK(cla_id>0)
);
sqlCopy
  • 1
  • 2
  • 3
  • 4
  • 5

mysql不支持检查约束,但是写上检查约束不会报错


转载请注明来源,欢迎对文章中的引用来源进行考证,欢迎指出任何有错误或不够清晰的表达。可以在下面评论区评论,也可以邮件至 289211569@qq.com

Gitalk 加载中 ...