MySQL笔记-数据库约束条件与主键重排序

电玩女神 2022-07-18 23:33 222阅读 0赞

#### 一、数据库约束 ####

数据库的DDL 语句仅仅指定了一些列定义，这仅是数据表的基本功能。为了数据的完整性，统一性，数据库存在许多约束。

大部分数据库支持的5中完整性约束：

1、NOT NULL：非空约束。

2、UNIQUE：唯一约束。

3、PRIMARY KEY：主键，唯一标识该条记录，相当于id。

4、FOREIGN KEY：外键，指定该行记录从属于主表中的一条记录。

5、CHECK：检查，指定一个布尔表达式，用于指定对应列的值必须满足该表达式。

注：MySQL不支持CHECK约束，虽然仍可使用，但无任何作用（对5.6版mysql，更高版本未知）。

为数据表指定约束有如下两个时机：

1、建表同时为相应的数据列指定约束。

2、建表后创建，以修改表的方式增加约束。

注：MySQL使用information\_schema数据库里的TABLE\_CONSTRAINTS表保存该数据库实例中的所有约束信息，用户可查询此表查看约束信息。

#### 二、5种约束简介 ####

##### 1、NOT NULL #####

指定此约束：

1）、建表时在列后面加not null即可；

2）、alter table时增加或删除（列后加null）非空约束，如：

alter table tableName modify columName1 datatype \[default value\] not null/null;

##### 2、UNIQUE #####

UNIQUE约束的列不可出现重复值，但可出现多个NULL 值（因为数据库中NULL不等于NULL）。

指定此约束：

1）、建表时在datatype后加unique即可，适用于单列增加此约束。

2）、建表时通过表级约束语法增加此约束，适用于单列或多列同时添加此约束。语法：

constraint \[约束名\] unique（添加此约束的列名，多列用逗号隔开）;

例：

create table myTable（

name varchar（10） ,

phoneNum int ,

\#使用表级约束语法建立约束，指定两列组合不允许重复，且指定约束名为mytable\_unique。

constraint mytable\_unique unique(name,phoneNum)

);

3）、alter修改表语句+add增加unique约束。例：

\#为表info中的name、number列增加此约束

alter table info add unique（name，number）；

4）、alter修改表语句+modify增加unique约束。例：

alter table info modify addr varchar（40） unique;

##### 3、PRIMARY KEY #####

增加此约束方法与unique相似，将unique换成primary key即可。

primary key 与unique约束相似，但primary key约束的列不能为NULL。

说明：1）、当创建主键约束时，MySQL在主键约束所在列或列组合上建立对应的唯一索引。

2）、主键约束可以是单列主键约束，也可以是列组合主键约束，但同一个表中只能有一个主键约束（组合主键约束也只算一个主键约束，关键在于标识记录）。

3）、如果某数据列类型是整形，且作为主键列，则可指定自增长。只需在primary key前加auto\_increment。

删除主键约束，使用drop，如：

alter table xxx drop primary key；

（**注意：**

**1、实践发现modify可以增加primary key约束，但无法去除此约束！**

**2、auto\_increment 并不是与主键关联的，而是与字段关联，但使用它有个条件：只能在指定索引的字段上使用。所以删除主键**

**时，要先去掉modify 主键列去掉auto\_increment ，之后drop 主键才能成功。示例：**

create table test(id int primary key auto_increment);//定义顺序可理解为依赖关系:primary key 定义在字段上，auto_increment 定义在具有索引的字段上
    //删除主键前需要先去掉auto_increment 
    alter table test modify id int;//modify 相当于重新定义字段为：id int(去掉了auto_increment)。没有此句，下句不会成功
    alter table test drop primary key;

）

##### 4、FOREIGN KEY #####

\*外键约束主要用于保证一个或两个数据表之间的参照完整性，外键是构建于一个表的两个字段或者两个表的两个字段之间的参照关系。

##### 外键确保了相关的两个字段的参照关系： #####

1）、子（从）表外键列的值必须在主表被参照列的值范围之内，或者为空（也可通过非空约束不为空）。

2）、当主表的记录被从表参照时，主表记录不允许被删除，必须先把从表的相关参照记录全部删除后，方可删除主表的该记录。删除主表记录时，级联删除从表中相关的参照记录。

外键定义了两个实体之间一对多、一对一的关联关系。

##### \*建立外键约束语法： #####

1）、列级约束语法（创建或修改表时单列内增加约束的语法，如：name int references table1（id）），但在MySQL中没实际作用，仅为了兼容SQL标准。

2）、表级约束语法：

方式一：创建表时指定外键约束：

create talbe student\_table（

student\_id int auto\_increment primary key,

java\_teacher int,

\#指定java\_teacher列为外键，外键约束名为student\_teacher\_fk，java\_teacher参照主表teacher\_table的teacher\_id列。

constraint student\_teacher\_fk foreign key(java\_teacher) referrences teacher\_table(teacher\_id)

);

说明：若不需指定外键约束名，则将foreign key之前的去掉即可。此时MySQL会自动为该外键约束命名为table\_name\_ibfk\_n，其中table\_name是从表的表名，此处即为student\_table，而n是从1开始的整数。外键名可用于drop删除外键约束，同理主键名也如此。

方式二：alter+add方式：

alter table student\_table

add foreign key（java\_teacher） references teacher\_table(teacher\_id);

此外，可建立多列组合的外键约束，此时必须使用表级约束语法。外键约束也可以是参照自身，场景如经理与与普通员工同为员工，处在一个表中，但经理与员工之间又存在一对多的关系，所以此时应该参照自身，语法相同。

##### \*定义级联关系： #####

##### \*删除外键： #####

ALTER TABLE 表名 DROP FOREIGN KEY 外键名，如：alter table my\_table drop foreign key fk1；

#### 5、CHECK ####

建表时指定check(布尔表达式)即可，但MySQL中check不会有任何效果(mysql5.6以上未知)。

例：

create table test（

number int，

\#检查number是否大于0，不满足是插入记录会失败

chech（number>0）

);

注：对于MySQL支持不太好的check约束甚至更复杂的约束，可借助MySQL 触发器机制（trigger）实现，后面学。

**删除某些数据记录后主键列数组不连续，如何重新排序？**

**方法一：删除主键列并重设**

删除主键列——>add主键列并指定auto\_increment——>modify主键列的位置

![Center][]

说明：1、mysql中只有定义为键(key)的列可以auto\_increment；2、若已有数据记录，则添加主键时，主键列数据不能相同且不为空；3、主键列的自增效果是在设置时才开始发挥作用，即对前面已有的数值型数据，并不会重新排列（即若在截图示例中，若直接删除原来的主键再指定id列为auto\_increment的主键，124并不会变成123）。

综合结论：重新排序时，直接删除整个主键列，之后重新添加自增的主键列，再通过modify修改主键列位置。

方法二：

[Center]: /images/20220718/c4e8fab61dc3420cbd6d16032db0fb97.png