MySQL的约束

喜欢ヅ旅行 2022-05-25 11:22 137阅读 0赞

### 1 MySQL常用的几种约束： ###

（1)非空约束 NOT NULL;

(2) 主键约束 PRIMARY KEY;

(3)唯一约束 UNIQUE；

(4) 默认约束 DEFAULT；

(5) 外键约束 FOREIGN KEY;

### 2 非空约束： ###

语法规则：列名 数据类型 NOT NULL(在创建表的时候添加);

也可以通过修改表的方式添加非空约束：

ALTER TABLE bookinfo MODIFY book\_name VARCHAR(20) NOT NULL;

![70][]

删除非空约束：

ALTER TABLE bookinfo MODIFY book\_name VARCHAR(20)；

![70 1][]

### 3 主键约束： ###

（1）单字段主键：

a. 在定义列的同时指定主键:

列名 数据类型 PRIMARY KEY；

![70 2][]

b. 在列定义的后面指定主键：

\[CONSTRAINT<约束名>\] PRIMARY KEY(列名）；

![70 3][]

（2）多字段联合主键（复合主键）：

a. 主键有多个字段（列）联合组成：

PRIMARY KEY(字段1， 字段2，…… ， 字段n)

b. 通过修改表的方式添加主键：

ALTER TABLE bookinfo MODIFY book\_id INT PRIMARY KEY;

ALTER TABLE bookinfo ADD PRIMARY KEY(book\_id);

ALTER TABLE bookinfo ADD CONSTRAINT PK\_ID PRIMARY KEY(book\_id);

![70 4][]

c. 删除主键：

ALTER TABLE bookinfo DROP PRIMARY KEY;

![70 5][]

### 4 唯一约束： ###

语法规则：

a.在定义列的同时指定唯一约束：

列名 数据类型 UNIQUE

![70 6][]

b. 在列定义的后面指定唯一约束：

\[CONSTRAINT<约束名>\] UNIQUE(列名）

c. 通过修改表的方式添加唯一约束：

ALTER TABLE bookinfo MODIFY book\_name varchar(20) UNIQUE;

ALTER TABLE bookinfo ADD UNIQUE (book\_id);

ALTER TABLE bookinfo ADD CONSTRAINT uk\_bname UNIQUE (book\_name);

d. 删除唯一约束：

ALTER TABLE bookinfo DROP INDEX uk\_bname;(uk\_bname表示唯一性约束的名称）

ALTER TABLE bookinfo DROP KEY uk\_bname;

![70 7][]

### 5 默认约束： ###

语法规则：

a.在定义列的同时指定默认约束：

列名 数据类型 DEFAULT 默认值；

![70 8][]

b.通过修改表的方式添加唯一默认约束：

ALTER TABLE bookinfo MODIFY book\_name varchar(20) DEFAULT 'xxxxx';

ALTER TABLE bookinfo ALTER COLUMN book\_name SET DEFAULT 'xxxxx';

![70 9][]

c. 删除默认约束：

ALTER TABLE bookinfo MODIFY book\_name VARCHAR(20)；

ALTER TABLE bookinfo ALTER COLUMN book\_name DROP DEFAULT；

![70 10][]

### 6 外键约束： ###

在外键约束里首先我们所要解决的就是如何确定父表与子表：

有外键是子表，外键去关联主键，一个主键可能对应多个表中的多个外键（就像一个父亲可以有多个儿子，而一个儿子只能有一个父亲）

外键对应参照完整性，一个表的外键可以为空值，若不为空，则每一个外键值必须等于另一个表中主键的某个值，一个表可以有多个外键。

a.外键约束的语法格式：

\[CONSTRAINT\] <外键约束的名称> FOREIGN KEY(列名）references <主表名>(主键）

![70 11][]

![70 12][]

b.修改表添加外键约束：

ALTER TABLE bookinfo ADD FOREIGN KEY(book\_category\_id) REFERENCES bookcategory(category\_id);

alter table 子表的数据表名 add foreign key(子表的外键名称) references 父表的数据表名称（父表的主键名称）

![70 13][]

c.删除外键约束：

ALTER TABLE　bookinfo DROP FOREIGN KEY fk\_id;(fk\_id表示外键的名称）

![70 14][]

CASCADE: 从父表中删除或更新对应的行，同时自动的删除或更新自表中匹配的行。

当使用ON DELETE CASCADE时候，如果父类的中的某个元素被删除，那么子类中与之涉及的元素也一并会被删除。

下面来看一些使用MySQL `ON DELETE CASCADE`的例子。

假设有两张表：建筑物(`buildings`)和房间(`rooms`)。 在这个数据库模型中，每个建筑物都有一个或多个房间。 然而，每个房间只属于一个建筑物。没有建筑物则房间是不会存在的。

建筑物和房间表之间的关系是一对多(1：N)，如下面的数据库图所示：

![451110706_15701.png][]

当我们从`buildings`表中删除一行时，还要删除`rooms`表中引用建筑物表中行的行。 例如，当删除建筑编号(`building_no`)为`2`的行记录时，在`buildings`表上执行如下查询：

DELETE FROM buildings 
    WHERE
        building_no = 2;

SQL

我们希望`rooms`表中涉及到建筑物编号`2`的行记录也将被删除(讲得通俗一点：假设`2`号楼倒塌了，那么`2`号楼的房间应该也就不存在了)。以下是演示MySQL `ON DELETE CASCADE`参考操作如何工作的步骤。

第一步， 创建`buildings`表，如下创建语句：

USE testdb;
    CREATE TABLE buildings (
        building_no INT PRIMARY KEY AUTO_INCREMENT,
        building_name VARCHAR(255) NOT NULL,
        address VARCHAR(255) NOT NULL
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;

SQL

第二步， 创建`rooms`表，如下创建语句：

USE testdb;
    CREATE TABLE rooms (
        room_no INT PRIMARY KEY AUTO_INCREMENT,
        room_name VARCHAR(255) NOT NULL,
        building_no INT NOT NULL,
        FOREIGN KEY (building_no)
            REFERENCES buildings (building_no)
            ON DELETE CASCADE
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;

SQL

请注意，在外键约束定义的末尾添加`ON DELETE CASCADE`子句。

第三步， 将一些数据插入到`buildings`表，如下插入语句：

INSERT INTO buildings(building_name,address)
    VALUES('海南大厦','海口市国兴大道1234号'),
          ('万达水城','海口市大同路1200号');

SQL

第四步， 查询`buildings`表中的数据：

SQL

现在可以看到，在建筑物表中有两行记录。

第五步， 将一些数据插入到`buildings`表，如下插入语句：

INSERT INTO rooms(room_name,building_no)
    VALUES('Amazon',1),
          ('War Room',1),
          ('Office of CEO',1),
          ('Marketing',2),
          ('Showroom',2);

SQL

第六步， 查询`rooms`表中的数据：

mysql> select * from rooms;
    +---------+---------------+-------------+
    | room_no | room_name     | building_no |
    +---------+---------------+-------------+
    |       1 | Amazon        |           1 |
    |       2 | War Room      |           1 |
    |       3 | Office of CEO |           1 |
    |       4 | Marketing     |           2 |
    |       5 | Showroom      |           2 |
    +---------+---------------+-------------+
    5 rows in set

SQL

从上面行记录中可以看到，building\_no=1的建筑有`3`个房间，以及building\_no=2有`2`个房间。

第七步， 删除编号为2的建筑物：

DELETE FROM buildings WHERE building_no = 2;

SQL

第八步， 查询 `rooms`表中的数据 -

mysql> DELETE FROM buildings WHERE building_no = 2;
    Query OK, 1 row affected
    
    mysql> SELECT * FROM rooms;
    +---------+---------------+-------------+
    | room_no | room_name     | building_no |
    +---------+---------------+-------------+
    |       1 | Amazon        |           1 |
    |       2 | War Room      |           1 |
    |       3 | Office of CEO |           1 |
    +---------+---------------+-------------+
    3 rows in set

SQL

可以看到，表中只剩下引用`building_no=1`的记录了，引用`building_no=2`的所有行记录都被自动删除了。

> 请注意，`ON DELETE CASCADE`仅支持使用[存储引擎][Link 1]支持外键(如`InnoDB`)的表上工作。 某些表类型不支持诸如`MyISAM`的外键，因此应该在使用MySQL `ON DELETE CASCADE`引用操作的表上选择适当的存储引擎。

查找受MySQL ON DELETE CASCADE操作影响的表的技巧

有时，当要从表中删除数据时，知道哪个表受到MySQL `ON DELETE CASCADE`参考操作的影响是有用的。 可从`information_schema`数据库中的`referential_constraints`表中查询此数据，如下所示：

USE information_schema;
    
    SELECT 
        table_name
    FROM
        referential_constraints
    WHERE
        constraint_schema = 'database_name'
            AND referenced_table_name = 'parent_table'
            AND delete_rule = 'CASCADE'

SQL

例如，要使用示例数据库(`testdb`，因为上面两个表是建立在`testdb`数据库之上的)中的`CASCADE`删除规则查找与建筑表相关联的表，请使用以下查询：

USE information_schema;
    
    SELECT 
        table_name
    FROM
        referential_constraints
    WHERE
        constraint_schema = 'testdb'
            AND referenced_table_name = 'buildings'
            AND delete_rule = 'CASCADE'

SQL

执行上面查询语句，得到以下结果 -

+------------+
    | table_name |
    +------------+
    | rooms      |
    +------------+
    1 row in set

[70]: /images/20220525/849058bd5a304e63bd44030ee5e761e3.png
[70 1]: /images/20220525/a90ef8853a184fbc9da7fcb3a64673d7.png
[70 2]: /images/20220525/dcfea3aaee734f12bc36099aa3c87638.png
[70 3]: /images/20220525/3851c308bb594379afa86359c792422d.png
[70 4]: /images/20220525/052b1458d4f142de81a999b89bfba683.png
[70 5]: /images/20220525/38c58e6a6611472c9c07a98d440f427c.png
[70 6]: /images/20220525/8920ee3836a84384b206156f8f22aa94.png
[70 7]: /images/20220525/f67fc6d2bf274f52b03a4f0587f8a67c.png
[70 8]: /images/20220525/659e1d167eee4a8899caa34ba0bd0159.png
[70 9]: /images/20220525/e626e8e493d34962b9b5810917df9b7b.png
[70 10]: /images/20220525/9dcbcf135c18419f80fcd93b35678ec8.png
[70 11]: /images/20220525/9520cedaa6964ad684995290df0312e2.png
[70 12]: /images/20220525/76639c3e38a042e5b6cecf82bb57a368.png
[70 13]: /images/20220525/05148bc1b786412293fdf8ab1b4591eb.png
[70 14]: /images/20220525/05284159da3c47b7aa7f07894a9bdb4a.png
[451110706_15701.png]: /images/20220525/8edfba3948aa4640ab1d6bce7ff4a9cb.png
[Link 1]: http://www.yiibai.com/understand-mysql-table-types-innodb-myisam.html