约束类型

分类 Mysql 标签 Mysql 评论:
  1. 约束类型
    1. 从查询information_schema中查询指定表中的约束
    2. 主键约束 PRIMARY KEY
    3. 唯一约束 UNIQUE
    4. 非空约束 NOT NULL
    5. 自增长约束 AUTO_INCREMENT
    6. 外键约束 FOREIGN KEY
    7. 默认约束 DEFAULT
    8. 无符号位 UNSIGNED
    9. 零填充 ZEROFILL
    10. 检查约束 CHECK

约束类型

从查询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)

主键约束 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)
);

  2. 通过ALTER语句

    ALTER TABLE t_user ADD PRIMARY KEY(user_id);
ALTER TABLE t_user ADD CONSTRAINT PK_ID_NAME PRIMARY KEY(user_id);

    ALTER TABLE t_user MODIFY user_id INT(10) PRIMARY KEY;

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

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

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

删除主键约束

ALTER TABLE t_user DROP PRIMARY KEY;

注：主键约束相当于(唯一约束+非空约束)
一张表中最多有一个主键约束,如果设置多个主键,就会出现如下提示：
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)
);

  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);

删除唯一性约束

ALTER TABLE t_user DROP INDEX user_id;

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

非空约束 NOT NULL

添加非空约束

  1. 建表时直接添加

    CREATE TABLE t_user(user_id INT(10) NOT NULL);

  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;

删除非空约束

ALTER TABLE t_user MODIFY user_id INT(10);
ALTER TABLE t_user CHANGE user_id user_id INT(10);

自增长约束 AUTO_INCREMENT

添加自增长约束

  1. 在创建表的时候添加

    CREATE TABLE t_user(user_id INT(10) AUTO_INCREMENT PRIMARY KEY);

  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;

删除自增长约束

ALTER TABLE t_user MODIFY user_id INT(10);
ALTER TABLE t_user CHANGE user_id user_id INT(10);

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

外键约束 FOREIGN KEY

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

CREATE TABLE class(
    cla_id INT(6) AUTO_INCREMENT PRIMARY KEY,
    cla_name VARCHAR(30) NOT NULL UNIQUE
);

从表：

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) ##添加外键约束
);

也可以这样添加：

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

删除外键约束

ALTER TABLE students DROP FOREIGN KEY FK_CLA_ID;

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

  • 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
);

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
);

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
);

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

默认约束 DEFAULT

添加默认约束

  1. 在创建表的时候添加

    CREATE TABLE t_user(user_id INT(10) DEFAULT  3);

  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;

删除默认约束

ALTER TABLE t_user MODIFY user_id INT(10);
ALTER TABLE t_user CHANGE user_id user_id INT(10);

无符号位 UNSIGNED

添加无符号

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

删除无符号

ALTER TABLE t_user MODIFY user_id INT(10);
ALTER TABLE t_user CHANGE user_id user_id INT(10);

零填充 ZEROFILL

添加零填充

  1. 在创建表的时候添加

    CREATE TABLE t_user(user_id INT(10) ZEROFILL);

  2. 通过ALTER语句

    ALTER TABLE t_user MODIFY user_id INT(10) ZEROFILL;
ALTER TABLE t_user CHANGE user_id user_id INT(10) ZEROFILL;

删除零填充

ALTER TABLE t_user MODIFY user_id INT(10);
ALTER TABLE t_user CHANGE user_id user_id INT(10);

零填充会将未将有效位以外的位用零来显示,比如某字段数据类型为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)
);

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

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

©2016-2023 Daryl