mysql篇 - mysql约束 - 《mysql》

约束

约束

sql 约束用于规定表中的数据规则，如果存在违反约束的数据行为，行为会被约束终止。

约束可以在创建表时规定，如 create table，也可以在表创建之后规定，如 alter table。

约束的分类：

主键约束
外键约束
唯一约束
非空约束
默认值约束
检查约束（mysql 不支持 Oracle 支持）
自增约束（mysql 特有的）

主键约束PK

确保某列（或两个列多个列的结合）有唯一标识。NOT NULL （非空）和 UNIQUE （唯一）的结合。

外键约束FK

用于维护两张表之间的关系。用于保证该字段的值必须来自于主表的关联列的值。

唯一约束UK

唯一，用于保证该字段的值具有唯一性，可以为空。

非空约束

非空，用于保证该字段的值不能为空。

默认值约束

默认值，用于保证该字段有默认值。

检查约束

mysql不支持。检查该字段的值是否为指定的值。

自增约束

一组整数：1,2,3。该列（字段）实现自动增长。

案例

约束添加分类
1. 列级约束：六大约束语法上都支持，但外键约束没有效果
2. 表级约束：除了非空、默认，其他的都支持

列级约束

create table if not exists t_stu (
    id int primary key auto_increment,    # 主键约束和自增约束
  stu_name varchar(20) not null,    # 非空约束
  gender char(1) check(gender='男' or gender='女'), # 检查约束，mysql没有效果但不报错
  seat int unique, # 唯一约束
  age int default 18, # 默认（值）约束
  major_id int references t_major(id) # 外键约束，mysql没有效果但不报错，连接的另外一个表的id
);

不遵守约束的数据在添加时会报错。

表级约束

-- 需要先创建一个t_major关联
create table if not exists t_major (
    id int primary key not null
);

-- 表级约束
create table if not exists t_stu1 (
  id int,
  stu_name varchar(20),
  gender char(1),
  seat int,
  age int,
  major_id int,

  constraint pk primary key (id), # id字段 主键约束
  constraint ck check(gender='男' or gender='女'), # 检查约束，mysql没有效果但不报错
  constraint un unique key(seat),    # seat字段，唯一约束
  constraint fk_stu foreign key(major_id) references t_major(id) # 外键约束，mysql没有效果但不报错，连接的major表的id
);

现在如果想要直接删除 t_major 表是无法删除的。因为 t_major 表中的主键和 t_stu1 表的外键有外键约束，删除了 t_major 表的 major_id 字段的数据就无处查找了。

如果真的要删除，那么就需要先删除外键关系。删除 t_stu1 表的外键 fk_stu 关系。

删除后两个表就没有关系了，这时就可以删除 t_major 表了。

表级约束和列级约束合并使用

从上面可以看到，无论是表级约束还是列级约束都有限制，开发中可以将两者结合来使用。

-- 列级约束和表级约束
create table if not exists t_stu2 (
  id int primary key auto_increment,    # 主键约束，自增约束
  stu_name varchar(20) not null,            # 非空约束
  gender char(1) check(gender='男' or gender='女'), # 检查约束，mysql没有效果但不报错
  seat int unique,    # 唯一约束
  age int default 18,    # 默认值约束
  major_id int,
  constraint fk_stu foreign key(major_id) references t_major(id) # 外键约束，mysql没有效果但不报错，连接的major表的id
);

主键约束和唯一约束对比

建表和删表顺序

建表时，先创建从表，再创建主表
删表时，先删除从表的关联关系，再删除从表
插入数据时，先插入主表（主键所在的表），再插入从表（外键对应的表）
删除数据时，先删除从表，再删除主表

实现一对多、多对多关系

一对一关系

/**
    1 : 1    夫妻
  A   B
  FK    PK
*/

-- 妻子表
create table if not exists t_wife (
    wid int primary key,    -- 编号
    wname varchar(20),        -- 名字
    age int                 -- 年龄
);

-- 丈夫表
create table if not exists t_husband (
    hid int primary key,    -- 编号
    hname varchar(20),        -- 名字
    age int,                 -- 年龄
    wid int unique,            -- 一个丈夫只能对应一个妻子，这里要加唯一约束
    constraint fk_wife_hus foreign key(wid) references t_wife (wid)    -- 外键wid映射到t_wife的wid字段
);

一对多关系

外键一定是放在 n 的那一边，一个部门对应多个员工，外键放在员工表。

/**
    1 : n    部门 : 员工
  A   B
  PK    FK
*/

-- 部门表
create table if not exists t_dept (
    deptno int primary key,        -- 编号
    deptname varchar(20)        -- 名称
);

-- 员工表,外键必须放在n的一方
create table if not exists t_emp (
    empno int primary key,        -- 编号
    empname varchar (20),
    deptno int,
    constraint fk_emp_dept foreign key(deptno) references t_dept(deptno)    -- 外键deptno映射到t_dept的deptno字段
);

多对多关系

/**
 在多对多关系中，需要创建第三张表C来关联A、B表

  n  :  n    学生 : 课程
  1  n  1
  A  C  B
*/

-- 学生表
create table if not exists t_student (
    sid int primary key,    -- 编号
    sname varchar(20)        -- 名称
);

-- 课程表
create table if not exists t_course (
    cid int primary key,    -- 编号
    cname varchar(20)        -- 名称
);

-- 关联表 保存课程和学生之间的关系
create table if not exists t_student_course (
    cid int,
    sid int,
    constraint pk_cid_sid primary key(cid, sid), -- 联合主键，多列之间定义主键
    constraint fk_student foreign key(sid) references t_student(sid), -- 学生表外键sid，映射到t_student的sid字段
    constraint fk_cou foreign key(cid) references t_course(cid) -- 课程表外键cid，映射到t_course的cid字段
);

【重点】创建表时，约束外键名称不能重复。如上，pk_cid_sid、fk_student、fk_cou 在整个数据库中是不能重复的，所以定义名称时尽量规范。否则建表失败。

创建成功后如下图。

【重点注意】在创建关联表时，必须添加 **联合主键**（即多列之间定义主键），在关联表中单列的 cid 和 sid 都是可以重复的，但是将两个列看成一个整体的话，是不能重复的。如下，当创建相同的 “2-1（java-张三）” 时就会报重复键错误，这就是联合主键的作用。

to be continue…