约束.....

亦凉 2023-09-29 10:16 41阅读 0赞

## 一、约束  ##

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAbHdqXzA3_size_20_color_FFFFFF_t_70_g_se_x_16][]

###  **例子演示：** ###

**注意：主键自增 只受unique(唯一)的影响 当因为被unique标识的字段在插入数据的时候出现错误时， 数据没有添加成功 但主键仍然自增**

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAbHdqXzA3_size_20_color_FFFFFF_t_70_g_se_x_16 1][]

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAbHdqXzA3_size_20_color_FFFFFF_t_70_g_se_x_16 2][]

create table user(
        id int primary key auto_increment comment 'ID唯一标识',
        name varchar(10) not null unique comment '姓名',
        age int check ( age >0 && age <=120 ) comment '年龄',
        status char(1) default '1' comment '状态',
        gender char(1) comment '性别'
    ) comment 'user表';
    
    drop table user;
    -- -------------------------- 插入数据测试 ---------------------------------
    insert into user( name, age, status, gender) values
    ('Tom1',90,'1','男'),('Tom2',65,'1','男'),('Tom3',36,'1','男'),('Tom4',69,'1','男'),
                                                        ('Tom5',90,'1','男'),('Tom6',65,'1','男');
    # 注意 因为unique唯一  插入表中出现错误的时候  id一样自增
    insert into user(name, age, gender) VALUES ('Tom6',38,'男');
    insert into user(name, age, gender) VALUES ('Tom7',29,'男');
    
    # 其他的出现错误id不自增
    insert into user(name, age, gender) values ('Tom8',0,'男');
    insert into user(name, age, gender) values ('Tom9',86,'男');

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAbHdqXzA3_size_20_color_FFFFFF_t_70_g_se_x_16 3][]

## 二、外键约束 ##

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAbHdqXzA3_size_20_color_FFFFFF_t_70_g_se_x_16 4][]

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAbHdqXzA3_size_20_color_FFFFFF_t_70_g_se_x_16 5][]

**准备表和数据：**

**部门表(父表)：**

create table dept(
        id int primary key auto_increment comment 'ID',
        name varchar(50) not null comment '部门名称'
    ) comment '部门表';
    
    insert into dept(id,name) values (1,'研发部'),(2,'市场部'),(3,'财务部'),(4,'销售部'),(5,'总经办');

**员工表(子表)：**

create table emp(
        id int primary key auto_increment comment 'ID',
        name varchar(50) not null comment '姓名',
        age int comment '年龄',
        job varchar(20) comment '职位',
        salary int comment '薪资',
        entrydate date comment '入职时间',
        managerid int comment '直属领导ID',
        dept_id int comment '部门ID'
    ) comment '员工表';
    
    insert into emp(id, name, age, job, salary, entrydate, managerid, dept_id) values
    (1,'金庸',66,'总裁',20000,'2000-01-01',null,5),(2,'张无忌',20,'项目经理',12500,'2005-12-05',1,1),
        (3,'杨逍',33,'开发',8400,'2000-11-03',2,1),(4,'韦一笑',48,'开发',11000,'2002-02-05',2,1),
            (5,'常遇春',43,'开发',10500,'2004-09-07',2,1),(6,'小昭',19,'程序员鼓励师',6600,'2000-10-12',2,1);

**两表之间添加外键 保证数据的一致性(删出主表的时候 会关联着子表 报错 不让删除)**

-- 添加外键
    alter table emp add constraint dept_id foreign key (dept_id) references dept(id);
    # 添加完外键之后 假设删除部门表(父表) 那么因为外键 父表和子表相关联着  所以不让删除
    delete from dept where id =1;

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAbHdqXzA3_size_20_color_FFFFFF_t_70_g_se_x_16 6][]

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAbHdqXzA3_size_20_color_FFFFFF_t_70_g_se_x_16 7][]

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAbHdqXzA3_size_20_color_FFFFFF_t_70_g_se_x_16 8][]

**两张表通过外键相互关联着,不让轻易删除一张表中的信息,删除的话就提示报错**

**想要删除表中的信息,就需要把外键删除 让两张表不关联，就能删掉（外键关联后耦合度较高）**

## **还有一种方法：外键的删除/更新行为：** ##

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAbHdqXzA3_size_20_color_FFFFFF_t_70_g_se_x_16 9][]

**代码演示如下：**

-- 外键的删除/更新行为
    
    -- 1 外键的删除行为
    alter table emp add constraint dept_id foreign key (dept_id) references dept(id) on update cascade on DELETE cascade ;
    -- 删除其中一张表(主表)的信息 观察子表的变化
    delete from dept where id =1;
    
    -- 2 外键的更新行为
    alter table emp add constraint dept_id foreign key (dept_id) references dept(id) on UPDATE set null on DELETE set null ;
    -- 更新父表的数据 观察子表的变化

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAbHdqXzA3_size_20_color_FFFFFF_t_70_g_se_x_16 10][]

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAbHdqXzA3_size_20_color_FFFFFF_t_70_g_se_x_16 11][]

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAbHdqXzA3_size_20_color_FFFFFF_t_70_g_se_x_16 12][]

[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAbHdqXzA3_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/3d908a5547ea4c139dfaf6bd0837a46f.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAbHdqXzA3,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAbHdqXzA3_size_20_color_FFFFFF_t_70_g_se_x_16 1]: https://img-blog.csdnimg.cn/a21f2e0979f546c38d9322a8ec7e2d6c.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAbHdqXzA3,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAbHdqXzA3_size_20_color_FFFFFF_t_70_g_se_x_16 2]: https://img-blog.csdnimg.cn/1b9d4b6a5bd84d5b91c5cccaeac960db.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAbHdqXzA3,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAbHdqXzA3_size_20_color_FFFFFF_t_70_g_se_x_16 3]: https://img-blog.csdnimg.cn/08b6e2201a2d402c94282acddc38374f.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAbHdqXzA3,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAbHdqXzA3_size_20_color_FFFFFF_t_70_g_se_x_16 4]: https://img-blog.csdnimg.cn/5ce16b44c0294dd9b940c49336f1a829.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAbHdqXzA3,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAbHdqXzA3_size_20_color_FFFFFF_t_70_g_se_x_16 5]: https://img-blog.csdnimg.cn/bd192df4410a4413b9a514f3531f2cf4.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAbHdqXzA3,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAbHdqXzA3_size_20_color_FFFFFF_t_70_g_se_x_16 6]: https://img-blog.csdnimg.cn/e6d6eb4940db4272811f58c67599acd5.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAbHdqXzA3,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAbHdqXzA3_size_20_color_FFFFFF_t_70_g_se_x_16 7]: https://img-blog.csdnimg.cn/e47b9f999f6e43229f2acc6c6ad5e2af.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAbHdqXzA3,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAbHdqXzA3_size_20_color_FFFFFF_t_70_g_se_x_16 8]: https://img-blog.csdnimg.cn/72f3fc47065148858f54a9639bc476a0.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAbHdqXzA3,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAbHdqXzA3_size_20_color_FFFFFF_t_70_g_se_x_16 9]: https://img-blog.csdnimg.cn/a26488c65d0b4c319292e0dc45f83ea1.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAbHdqXzA3,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAbHdqXzA3_size_20_color_FFFFFF_t_70_g_se_x_16 10]: https://img-blog.csdnimg.cn/fb6e015b1a474dcb9fbb9634a65115ef.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAbHdqXzA3,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAbHdqXzA3_size_20_color_FFFFFF_t_70_g_se_x_16 11]: https://img-blog.csdnimg.cn/e0085b3dd0434b78b52fba7a1b4992e7.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAbHdqXzA3,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAbHdqXzA3_size_20_color_FFFFFF_t_70_g_se_x_16 12]: https://img-blog.csdnimg.cn/d1b7085cebe44157826868ad0f1bff82.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAbHdqXzA3,size_20,color_FFFFFF,t_70,g_se,x_16