Java面向对象系列[v1.0.0][SQL语法之数据库约束]

末蓝、 2022-12-10 07:21 100阅读 0赞

## 数据库约束 ##

所有关系型数据库都支持对数据表使用约束，在表面上是强制执行的数据校验规则，通过约束可以更好的的保证数据表里数据的完整性

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

*  NOT NULL:非空约束，指定某列不能为空
 *  UNIQUE:唯一约束，指定某列或者几列组合不能重复
 *  PRIMARY KEY:主键，指定该列的值可以唯一地标识该条记录
 *  FOREIGN KEY:外键，指定该行记录从属于主表中的一条记录，住院哦用于保证参照完整性
 *  CHECK:检查，指定一个布尔表达式，用于指定对应列的值必须满足该表达式

>  *  大部分数据库都支持以上5种约束，但MySQL不支持CHECK约束，虽然MySQL的SQL语法可以使用CHECK约束，但不会有任何作用
>  *  虽然约束的作用只是用于保证数据表里的数据完整性，但约束也是数据库对象，并被存储在系统表中，也拥有自己的名字

### 约束分类 ###

*  单列约束：每个约束只约束一列
 *  多列约束：每个约束可以约束多个数据列

>  *  建立约束，可以在建表的同时为相应的数据列指定约束，也可以建表后，以修改表的方式增加约束
>  *  MySQL使用information\_schema数据库里的TABLE\_CONSTRAINTS表来保存该数据库实例中所有的约束信息

### NOT NULL约束 ###

非空约束用于确保指定列不允许为空，非空约束是比较特殊的约束，他只能作为列级约束使用，只能使用列级约束语法定义  
SQL中的null不区分大小写，且所有数据类型的值都可以是null，包括int、float、boolean等数据类型，且空值是空值，0是0，null是null；

create table davieyang_test_table_not_null
    (
    	davieyang_id int not null,
    	davieyang_name varchar(255) default 'YangDaWei' not null,
    	davieyang_gender varchar(2) null
    );

此外还可以使用`alter table`增加或删除非空约束

alter table davieyang_test_table modify davieyang_gender varchar(2) not null;
    alter table davieyang_test_table modify davieyang_name varchar(255) null;
    alter table davieyang_test_table modify davieyang_name varchar(255) default 'davieyang' null;

### UNIQUE 约束 ###

唯一约束用于保证指定列或者指定列组合不允许出现重复值，虽然唯一约束的列不可以出现重复值，但可以出现多个null值(在数据库中null不等于null)  
同一个表内可建多个唯一约束，唯一约束也可由多列组合而成，当为某列创建唯一约束时，MySQL会为该列相应地创建唯一索引，如果不给唯一约束起名，则该唯一约束默认与列名相同  
唯一约束既可以使用列级约束语法建立，也可以使用表级约束语法建立，如果需要为多列建组合约束，或者需要为唯一约束指定约束名，则只能用表级约束语法  
当建立唯一约束时，MySQL在唯一约束所在列或列组合上建立对应的唯一索引

#### 列级语法建立唯一约束 ####

create table davieyang_unique_test(
    	davieyang_id int not null,
    	davieyang_name varchar(255) unique
    );

#### 表级语法建立唯一约束 ####

为多列组合建立唯一约束，或者自行制定约束名，语法如下

[constraint 约束名] 约束定义

create table davieyang_unique_test2
    (
    	davieyang_id int not null,
    	davieyang_name varchar(255),
    	davieyang_description varchar(255),
    	unique(davieyang_name),
    	constraint davieyang_constraint unique(davieyang_description)
    );

create table davieyang_unique_test3(
    	davieyang_id int not null,
    	davieyang_name varchar(255),
    	davieyang_description varchar(255),
    	constraint davieyang_constraint unique(davieyang_name, davieyang_description)
    );

如代码所示，第一个建表约束中要求davieyang\_name和davieyang\_description 都不能出现重复值，而第二个建表约束则要求两者的组合不能出现重复

#### 修改和添加唯一约束 ####

alter table davieyang_unique_test4 add unique(davieyang_name, davieyang_description);

alter table davieyang_unique_test5 modify davieyang_name varchar(255) unique;

#### 删除唯一约束 ####

对于大部分数据库而言，删除约束都是在alter table语句后使用`drop constraint 约束名`语法来完成，但MySQL并不是用这个方式，而是使用`drop index 约束名`的方式来删除约束

alter table davieyang_unique_test6 drop index davieyang_constraint

### PRIMARY KEY 约束 ###

*  主键约束相当于非空约束和唯一约束，也就是说主键约束的列既不允许重复，也不允许出现null值，如果对多列组合建立主键约束，则多列里包含的每一列都不能为空，但只要求这些列组合不能重复
 *  主键列的值可用于唯一地标识表中的一条记录
 *  每个表只能有一个主键，但这个主键约束可以由堕饿鬼数据列组合而成，主键是表中唯一确定一行记录的字段或字段组合
 *  建立主键约束时可以使用列级语法，也可以用表级语法，如果需要对多个字段建立组合主键约束，则只能用表级语法
 *  使用表级语法来建立约束时，可以为该约束指定约束名，但无论是否为该主键约束指定约束名，MySQL总会将所有的主键约束命名为PRIMARY

> MySQL允许在建立主键约束时为该约束命名，但这个字段没有用，是为了保持与标准SQL兼容，大部分数据库都允许自定义主键约束的名字，而且一旦指定了主键约束名，则该约束名就是指定的名字

*  当创建主键约束时，MySQL在主键约束所在列或列组合上建立对应的唯一索引

#### 列级语法建立主键约束 ####

create table davieyang_primary_test(
    	davieyang_id int primary key,
    	davieyang_name varchar(255),
    );

#### 表级越发建立主键约束 ####

create table davieyang_primary_test2(
    	davieyang_id int not null,
    	davieyang_name varchar(255),
    	davieyang_description varchar(255),
    	constraint davieyang_constraint primary key(davieyang_id)
    );

create table davieyang_primary_test3(
    	davieyang_name varchar(255),
    	davieyang_description varchar(255),
    	primary key(davieyang_name, davieyang_desctiption)
    );

#### 删除主键约束 ####

alter table davieyang_primary_test4
    drop primary key;

#### 修改或增加主键约束 ####

alter table davieyang_primary_test5
    add primary key(davieyang_name, davieyang_description);

alter table davieyang_primary_test6
    modify davieyang_name varchar(255) primary key;

很多数据库都对主键支持自增长的特性，指定自增长的列必须是整型，将该列作为主键，通常用于设置逻辑主键列，该列的值没有任何实际意义，仅起到标识作用，MySQL使用auto\_increment来设置自增长

create table davieyang_primary_test_finally(
    	davieyang_id int auto_increment primary key,
    	davieyang_name varchar(255),
    	davieyang_description varchar(255),
    );

### FOREIGN KEY 约束 ###

*  外键约束主要用于保证一个或两个数据表之间的参照完整性，外键是构建与一个表的两个字段或者两个表的两个字段之间的参照关系
 *  外间保证了相关的两个字段的参照关系：子表外间列的值必须在主表被参照lieder值范围之内或者为空（也可以通过非空约束外键列不允许为空）
 *  当主表的记录被字表记录参照时，主表记录不允许被删除，必须先把子表里参照该记录的所有记录全部删除后，才可以删除主表的该记录；还有一种方式，删除主表记录时级联删除子表中所有参照该记录的从表记录
 *  子表外键参照的只能是主表主键列或者唯一键列，如此才能保证子表记录可以准确定位到被参照的主表记录
 *  同一个表可以有多个外键

>  *  外键约束通常用于定义两个实体之间的一对多、一对一的关联关系，而对于一对多的关联关系，通常在多的一端增加外键列
>  *  对于一对一的关联关系，则可选择任意一方来增加外键，增加外键列的表被称为子表，只要为外键列增加唯一约束就可以表示一对一的关联关系
>  *  对于多对多的关联关系，则需要额外增加一个链接表来记录他们的关联关系

建立外键约束同样可以采用列级约束语法和表级约束语法，如果仅对单独的数据列建立外键约束，则使用列级约束语法即可，如果需要对多列组合建立外键约束或者需要为外键约束命名，则必须使用表级约束语法

#### 列级语法建立外键约束 ####

create table davieyang_test(
    	davieyang_id int auto_increment,
    	davieyang_name varchar(255),
    	primary key(davieyang_id)
    );

create table davieyang_foreign_test(
    	davieyang_f_id int auto_increment primary key,
    	davieyang_f_name varchar(255),
    	davieyang_f_description varchar(255) references davieyang_test(davieyang_id)
    )

MySQL支持使用列级约束语法来建立外键约束，但不会生效，MySQL提供这种列级约束语法仅仅为了和标准SQL的兼容性

#### 表级语法建立外键约束 ####

create table davieyang_test(
    	davieyang_id int auto_increment,
    	davieyang_name varchar(255),
    	primary key(davieyang_id)
    );

create table davieyang_foreign_test(
    	davieyang_f_id int auto_increment primary key,
    	davieyang_f_name varchar(255),
    	davieyang_f_description varchar(255),
    	foreign key(davieyang_f_description) references davieyang_test(davieyang_id)
    );

使用表级约束语法可以为外键约束指定约束名，如果创建外键没有指定约束名，则MySQL会把该外键约束命名为table\_name\_ibfk\_n，其中table\_name是子表的表明，n是从1开始的整数；如果显示的指定外键约束的名字，则可以使用constraint来指定

create table davieyang_test2(
    	davieyang_id int auto_increment,
    	davieyang_name varchar(255),
    	primary key(davieyang_id)
    );

create table davieyang_foreign_test2(
    	davieyang_f_id int auto_increment primary key,
    	davieyang_f_name varchar(255),
    	davieyang_f_description varchar(255),
    	constraint davieyang_foreign_test2_fk foreign key(davieyang_f_description) references davieyang_test2(davieyang_id)
    );

##### 多列组合外键约束 #####

create table davieyang_test3(
    	davieyang_name varchar(255),
    	davieyang_description varchar(255),
    	primary key(davieyang_name, davieyang_description)
    );

create table davieyang_foreign_test3(
    	davieyang_f_id int auto_increment primary key,
    	davieyang_f_name varchar(255),
    	davieyang_f_name varchar(255),
    	davieyang_f_description varchar(255),
    	foreign key(davieyang_f_name, davieyang_f_description) references davieyang_test3(davieyang_name, davieyang_description)
    );

#### 删除外键约束 ####

在alter table后增加`drop foreign key`约束名子句即可删除外键

alter table davieyang_foreign_test3
    drop foreign key davieyang_foreign_test3_ibfk_1;

#### 增加外键约束 ####

alter table davieyang_foreign_test3
    add foreign key(davieyang_f_name, davieyang_f_description) references davieyang_test3(davieyang_name, davieyang_description);

#### 自关联 ####

外键约束不仅可以参照其他表，还可以参照自身，这种参照自身就称为自关联

create table davieyang_foreign_test(
    	davieyang_id int auto_increment primary key,
    	davieyang_name varchar(255),
    	davieyang_refer_id int,
    	foreign key(davieyang_refer_id) references davieyang_foreign_test(davieyang_id)
    );

#### 级联删除 ####

如果想定义删除主表记录时，子表记录也随之删除，则需要在建立外键约束后添加on delete cascade或者添加on delete set null，第一种是删除主表记录时，把参照该主表记录的子表记录全部级联删除，而第二种则是把参照该主表记录的子表记录的外键设为null

create table davieyang_test4(
    	davieyang_id int auto_increment,
    	davieyang_name varchar(255),
    	primary key(davieyang_id)
    );

create table davieyang_foreign_test4(
    	davieyang_f_id int auto_increment primary key,
    	davieyang_f_name varchar(255),
    	davieyang_f_description varchar(255),
    	foreign key(davieyang_f_description) references davieyang_test4(davieyang_id) on delete cascade
    );

### CHECK约束 ###

MySQL支持建表时指定CHECK约束，但这个CHECK约束不会有任何作用，建立CHECK约束只需要在建表的列定义后增加check逻辑表达式即可

create table check_test(
    	davieyang_id int auto_increment,
    	davieyang_name varchar(255),
    	davieyang_salary decimal,
    	primary key(davieyang_id),
    	check(davieyang_salary>0)
    );

如果确实需要使MySQL创建的数据表有CHECK约束，甚至更复杂的完整性约束，实际上可以借助于MySQL的触发器机制