一、SQL语法——4-数据库约束

4-数据库约束

1.概述

（1）约束是在表上强制执行的数据校验规则，约束主要用于保证数据库里数据的完整性；此外当表中的数据存在相互依赖性时，可以保护相关的数据不被删除；

（2）大部分数据库支持如下五种完整性约束：

　　1）NOT NULL:非空约束，指定某列不允许为空；

　　2）UNIQUE:唯一约束，指定某列或者几列组合不能重复；

　　3）PRIMARY KEY:主键约束，指定该列的值可以唯一的标识该条记录；

　　4）FOREIGN KEY:外键约束，指定该行记录从属于主表中的一条记录，主要用于保证参照完整性；

　　5）CHECK:检查，指定一个布尔表达式，用于指定列的值必须满足该表达式；

（3）约束是数据库对象，也被保存在系统表中，也拥有自己的名字；

（4）根据约束对列的限制，约束可分为如下两类：

　　1）单列约束：每个约束只约束一列；

　　2）多列约束：每个约束可以约束多个数据列；

（5）为数据表指定约束的时机：

　　1）建表时为相应的数据列指定约束；

　　2）建表后，以修改表结构的方式增加约束。

2.NOT NULL约束

（1）NOT NULL约束主要用于确保指定列不可以为空，NOT NULL只能作为列级约束使用，只能使用列级约束语法定义；

（2）NULL在SQL中的解释：

　　1）所有数据类型的值都可以是null;

　　2）空字符串不是NULL，0也不是NULL;

　　3）NULL不等于NULL;

（3）示例：

#建表时为指定列指定NOT NULL约束
create table hehe(
    #建立NOT NULL约束，指定hehe_id不能为NULL;
    hehe_id int not null,
    #MySQL的NOT NULL约束不能指定名字
    hehe_name varchar(255) default 'test' not null,
    #下面列可以为NULL，默认就是可以为NULL
    hehe_gender varchar(2) null
)

#使用alter table在修改表时添加或者删除NOT NULL约束
#增加NOT NULL约束
alter table hehe
modify hehe_gender varchar(2) not null;
#取消NOT NULL约束
alter table hehe
modify hehe_name varchar(255) null;
#取消NOT NULL约束，并指定默认值
alter table hehe
modify hehe_name varchar(255) default 'test' not null;

 

3.UNIQUE约束

（1）UNIQUE约束用于保证指定列或指定列组合不允许出现重复值；

（2）同个表内可以建立多个UNIQUE，UNIQUE也可以由多个列组合而成。当为某个列添加UNIQUE时，MySQL会为该列相应的创建唯一索引。如果不给UNIQUE起名，该UNIQUE默认与列名相同；

（3）UNIQUE可以使用列级语法建立，也可以使用表级语法建立；如果需要为多列建立UNIQUE，或者为UNIQUE起名，只能使用表级约束语法；

（4）示例：

#建表时创建UNIQUE,使用列级约束语法
create table unique_test(
    #建立非空约束，表示此列不可为null
    test_id int not null,
    #使用列级约束语法建立UNIQUE
    test_name varchar(255) unique
);

#为多列组合建立UNIQUE，或者为UNIQUE自定义名称则需要使用表级约束语法建立UNIQUE

#表级约束语法格式：[constraint 约束名] 约束定义
#建表时创建UNIQUE,使用表级约束语法
create table unique_test2(
    #建立NOT NULL约束
    test_id int not null,
    test_name varchar(255),
    test_pass varchar(255),
    #使用表级约束语法建立UNIQUE
    unique (test_name),
    #使用表级约束语法建立UNIQUE并指定约束名
    constraint test2_uk unique(test_pass)
    #分别为test_name和test_pass建立UNIQUE
);

#使用表级约束语法，建表时为列组合建立UNIQUE
create table unique_test3(
    #NOT NULL
    test_id int not null,
    test_name varchar(255),
    test_pass varchar(255),
    #使用表级约束语法为列组合建立UNIQUE:两列的组合不能出现重复值
    constraint test3_uk unique(test_name,test_pass)
);


#在修改表结构时，使用add关键字添加UNIQUE
#为列组合添加UNIQUE
alter table unique_test3
add unique(test_name,test_pass);

#在修改表时，使用modify关键字为单列添加UNIQUE
alter table unique_test3
modify test_name varchar(255) unique;

#删除约束（MySQL）
alter table unique_test3
drop index test3_uk;

 

4.PRIMARY KEY约束

（1）PRIMARY KEY相当于NOT NULL+UNIQUE；即PRIMARY KEY约束的列既不可以存在null也不能够出现重复值；如果对多列组合建立PRIMARY KEY，则多列里包含的每一列都不能为null,但只要求这些列组合不重复，建立了PRIMARY KEY的列值（或列的组合值）可以用于唯一的标识一条记录；

（2）每个表中最多只有一个列（或者一个列组合建立PRIMARY KEY）；

（3）建立PRIMARY KEY既可以使用表级约束语法,也可以使用列级约束语法，如果为列组合建立PRIMARY KEY只能使用表级约束语法。使用表级约束语法时，可以为约束起约束名（MySQL中无效，默认名称为PRIMARY）;

（4）当创建PRIMARY KEY时，MySQL在所在列或列组合上建立对应的唯一索引；

（5）示例：

#使用列级约束语法，建表时建立PRIMARY KEY
create table primary_test(
    #建立了PRIMARY KEY
    test_id int primary key,
    test_name varchar(255)
);

#使用表级约束语法，建表时建立PRIMARY KEY
create table primary_test2(
    test_id int,
    test_name varchar(255),
    test_pass varchar(255),
    #指定约束名称为test2_pk，对大部分数据库有效，对MySQL无效
    #MySQL默认PRIMARY KEY的约束名为PRIMARY
    constraint test2_pk primary key(test_id)
);

#为多列组合建立PRIMARY KEY,只能使用表级约束语法
create table primary_test3(
    test_name varchar(255),
    test_pass varchar(255),
    #使用表级约束语法为多列组合建立PRIMARY KEY
    primary key(test_name,test_pass)
);

#删除指定表的PRIMARY KEY
alter table primary_test3
drop primary key;

#使用表级约束语法添加PRIMARY KEY(列组合)
alter table primary_test3
add primary key(test_name,test_pass);

#使用列级约束语法为单列添加PRIMARY KEY
alter table primary_test3
modify test_name varchar(255) primary key;

#为主键添加自增长策略
create table primary_test4(
    #添加PRIMARY KEY,生成策略为自增长
    test_id int auto_increment primary key,
    test_name varchar(255),
    test_pass varchar(255)
);

 

5.FOREIGN KEY约束

（1）FORRIGN KEY主要用于保证一个或两个数据表之间的参照完整性，FOREGIN KEY是构建于一个表的两个字段或者两个表的两个字段之间的参照关系。FOREIGN KEY确保了两个字段的参照关系：子表外键列的值必须在主表被参照列的值范围内，或者为空（允许使用NOT NULL来约束FOREIGN KEY列不为空）。

（2）当主表的记录被从表的记录参照时，主表记录不允许被删除，必须先把从表参照该记录的所有记录全部删除后，才可删除主表的该记录。也可以在删除主表记录时级联删除从表中的所有参照该记录的从表记录。

（3）从表参照的必须是主表的PRIMARU KEY列或者UNIQUE列，这样才能保证从表记录可以准确定位到被参照的主表记录。同一个表中可以有多个FOREIGN KEY。

（4）FOREIGN KEY通常用于定义两个实体间的一对多、一对一的关联关系。对于一对多通常在多的一段添加FOREIGN KEY;对于一对一可以在任意一个表中添加FOREIGN KEY;对于多对多的关联关系，需要额外增加一个链接表记录他们的关联关系。

（5）建立FOREIGN KEY同样可以使用表级约束语法和列级约束语法。如果仅仅对单独的列建立FOREIGN KEY使用列级约束语法即可，如果要为多个列组合建立FOREIGN KEY或者为FOREIGN KEY起名，则必须使用表级约束语法。

（6）示例：

#采用列级约束语法建立FOREIGN KEY直接使用references关键字，references指定该列参照那个主表，以及参照主表的哪个列
#为保证从表参照的主表存在，通常应该先建立主表
#MySQL中使用列级语法建立FOREIGN KEY不会生效，只有使用表级约束语法建立的FOREIGN KEY才会生效
create table teacher_table(
    teacher_id int auto_increment,
    teacher_name varchar(255),
    primary key(teacher_id)
);
create table student_table(
    #为本表建立PRIMARY KEY
    student_id int auto_increment primary key,
    student_name varchar(255),
    #指定java_teacher参照到teacher_table的teacher_id列
    java_teacher int references teacher_table(teacher_id)
);




#使用表级约束语法建立FOREIGN KEY
create table teacher_table1(
    teacher_id int auto_crement,
    teacher_name varchar(255),
    primary key(teacher_id)
);
create table student_table1(
    #建立PRIMARY KEY
    student_id int auto_increment primary key,
    student_name varchar(255),
    #指定java_teacher列参照到teacher_table1表中的teacher_id列
    java_teacher int,
    foreign key(java_teacher) references teacher_table1(teacher_id)
);




#如果使用表级约束语法，则需要使用foreign key来指定本表的外键列，并使用references指定参照那个主表，以及餐超到
#主表的哪个数据列。使用表级约束语法可以为外键约束指定外键名，如果创建外键约束时没有指定约束名，则MySQL会为该
#外键约束命名为tableName_ibfk_n，其中tableName是从表的表名，而n是从1开始的整数；

#如果显式指定外键约束的名字，需要使用constraint来指定
create table teacher_table2(
    teacher_id int auto_increment,
    teacher_name varchar(255),
    primary key(teacher_id)
);
create table student_table2(
    #为本表建立主键约束
    student_id int auto_increment primary key,
    student_name varchar(255),
    java_teacher int,
    #使用表级约束语法建立foreign key并为其指定名字
    constraint student_teacher_fk foreign key(java_teacher) references teacher_table2(teacher_id)
);



#如果需要建立多列组合的foreign key则必须使用表级约束语法
create table teacher_table3(
    teacher_name varchar(255),
    teacher_pass varchar(255),
    #使用teacher_name和teacher_pass建立联合主键
    primary key(teacher_name,teacher_pass)
);
create table student_table3(
    #为本表建立主键约束
    student_id int auto_increment primary key,
    student_name varchar(255),
    java_teacher_name varchar(255),
    java_teacher_pass varchar(255),
    #使用表级约束语法为java_teacher_name和java_teacher_pass建立联合foreign key
    constraint student_table3_ibfk_1 foreign key(java_teacher_name,java_teacher_pass) references teacher_table3(teacher_name,teacher_pass)
);


#删除foreign key
alter table student_table3
drop foreign key student_table3_ibfk_1;

#修改表结构时增加foreign key
alter table student_table3
add foreign key(java_teacher_name,java_teacher_pass) references teacher_table3(teacher_name,teacher_pass);



#foreign key不仅仅可以参照其他表，也可参照自身，这种参照自身的情况称为自关联
create table foreign_test(
    foreign_id int auto_increment primary key,
    foreign_name varchar(255),
    #使用该表的refer_id参照到本表的foreign_id
    refer_id int,
    foreign key(refer_id) references foreign_test(foreign_id)
);

#如果需要定义删除主表记录时，从表记录也会随之删除，需要在建立外键约束后添加on delete cascade或者
#添加on delete set null；
#on delete cascade:删除主表记录时，把参照该主表记录的从表记录全部级联删除
#on delete set null:删除主表记录时，把参照该主表记录的从表记录的外键设置为null

create table teacher_table4(
    teacher_id int auto_increment,
    teacher_name varchar(255),
    primary key(teacher_id)
);
create table student_table4(
    student_id auto_increment primary key,
    student_name varchar(255),
    java_teacher int,
    #使用表级约束语法建立foreign key并定义级联删除
    foreign key(java_teacher) references teacher_table4(teacher_id) on delete cascade
    #或者
    #foreign key(java_teacher) references teacher_table4(teacher_id) on delete set null
);

 

 

 

6.CHECK约束

用于指定一个列的值的取值范围

示例：

create table teacher_table(
    teacher_id int auto_increment primary key,
    teacher_age int,
    teacher_name varchar(255),
    teacher_pass varchar(255),
    check(teacher_age<1000)
);