MySQL数据库——约束介绍

目录

 

约束概念

约束分类

非空约束

创建表时添加非空约束

创建表后，添加非空约束

创建表后，删除非空约束

唯一约束

创建表时，添加唯一约束

在创建表后，删除唯一约束

在创建表后，添加唯一约束

主键约束

创建表时，添加主键约束

在创建表后，删除主键

在创建表后，添加主键

自动增长

创建表时指定主键自增长

删除自动增长

添加自动增长

外键约束

外键的特点

演示外键的由来

在创建表时，添加外键约束

创建表之后，删除外键

创建表之后，添加外键

级联更新与级联删除

添加级联操作

---

约束概念

对表中的数据进行限制，保证数据的正确性、有效性和完整性。一个表如果添加了约束，不正确的数据将无法插入到表中。约束在创建表的时候添加比较合适

约束分类

1. 主键约束：primary key
2. 非空约束：not null
3. 唯一约束：unique
4. 外键约束：foreign key

非空约束

因为某些列不能设置为NULL值，所以需要对列添加非空约束

创建表时添加非空约束

CREATE TABLE stu(
    id INT,
    NAME VARCHAR(20) NOT NULL -- name为非空
);

此时name这一列的值当插入数据的时候不能为空

创建表后，添加非空约束

ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;

ALTER TABLE 表名 MODIFY 字段名 列类型(长度) 非空约束;

如果在创建表时忘记添加非空约束，那么可以通过此语句添加

创建表后，删除非空约束

ALTER TABLE stu MODIFY NAME VARCHAR(20);

ALTER TABLE 表名 MODIFY 字段名 列类型(长度);

同样也可以通过alter语句将有非空约束的该为无非空约束

唯一约束

创建表时，添加唯一约束

CREATE TABLE stu (
      id INT,
      phone_number VARCHAR(20) UNIQUE
);

注意mysql中，唯一约束限定的列的值可以有多个null，也就是说如果一个字段添加了唯一约束，这个字段的值有多个null是没事的

在创建表后，删除唯一约束

ALTER TABLE stu DROP INDEX phone_number;

ALTER TABLE 表名 DROP INDEX 字段名;

在创建表后，添加唯一约束

ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;

ALTER TABLE 表名 MODIFY 字段名 字段类型(长度) 唯一约束;

如果表中的phone_number字段数据有重复值，这时候使用这个语句创建唯一约束会出错，因为数值重复了，除非把重复的字段值的那些行去掉，才能正常添加上去，这里稍微演示一下:

这里因为电话号码phone_number是有重复的1111，所以无法添加唯一约束phone_number，这时需要删除一条phone_number为1111的记录，即可执行ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;

主键约束

主键的特点：非空且唯一

注意：
            1. 含义：非空且唯一
            2. 一张表只能有一个字段为主键
            3. 主键就是表中记录的唯一标识

创建表时，添加主键约束

create table stu(
    id int primary key,-- 给id添加主键约束
    name varchar(20)
);

在创建表后，删除主键

ALTER TABLE stu DROP PRIMARY KEY;

ALTER TABLE 表名 DROP PRIMARY KEY;

在创建表后，添加主键

ALTER TABLE stu MODIFY id INT PRIMARY KEY;

ALTER TABLE 表名 MODIFY 字段名 字段类型 PRIMARY KEY;

自动增长

其实自动增长是一个独立的知识点，但是通常都是跟主键一起使用

关键字：auto_increment

概念：如果某一列是数值类型的，使用 auto_increment 可以来完成值的自动增长

创建表时指定主键自增长

create table stu(
    id int primary key auto_increment,-- 给id添加主键约束
    name varchar(20)
);

这时候如果我们执行insert into stu values (NULL,'刘备')，id会从1开始算，每次增加1，如果主键到4的时候，突然手动插入了一个主键为10，那么下一个主键的值就是11了

删除自动增长

ALTER TABLE stu MODIFY id INT;

ALTER TABLE 表名 MODIFY 字段名 字段类型;

这里仅仅是删除了自动增长的效果，并没有删除主键且唯一的特性

添加自动增长

ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

ALTER TABLE 表名 MODIFY 字段名 字段类型 AUTO_INCREMENT;

外键约束

外键约束是让表和表产生关系，从而保证数据的正确性

外键的特点

外键必须是另一表的主键的值(外键必须引用主键！)

外键可以重复

外键可以为空（NULL）

一张表中可以有多个外键！

演示外键的由来

例如向数据库插入如下数据：

CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30),
age INT,
dep_name VARCHAR(30),
dep_location VARCHAR(30)
);
-- 添加数据
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('张三', 20, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('李四', 21, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('王五', 20, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('老王', 20, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('大王', 22, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('小王', 18, '销售部', '深圳');

插入数据后的数据库数据展示：

数据的dep_name和dep_location字段显得很冗余，因为都是重复的数据

解决办法：拆分成两张表

一方，主表，部门表

create table department(
    id int primary key auto_increment,
    dep_name varchar(20),
    dep_location varchar(20)
);

多方，从表，员工表

CREATE TABLE employee(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    age INT,
    dep_id INT -- 外键对应主表的主键
)

添加两个部门

insert into department values(null, '研发部','广州'),(null, '销售部', '深圳');

添加员工,dep_id 表示员工所在的部门

INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);

结果如下：
部门表：

员工表：

但是这样仍然会有问题：当我们在 employee 的 dep_id 里面插入不存在的部门,数据依然可以添加.但是并没有对应的部门， 实际应用中不能出现这种情况。employee 的 dep_id 中的数据只能是 department 表中存在的 id

例如我们执行语句INSERT INTO employee (NAME, age, dep_id) VALUES ('王炸', 18, 6);，产生如下结果：

所以我们需要添加外键约束：

主表： 一方，用来约束别人的表

从表： 多方，被别人约束的表

在创建表时，添加外键约束

以上面的员工表emplyee为例子：

create table employee(
    id int primary key auto_increment,
    name varchar(20),
    age int,
    dep_id int, -- 外键对应主表的主键
    -- 创建外键约束
    constraint emp_depid_fk foreign key (dep_id) references department(id)
)

create table 表名(
    ....
    外键列
    constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
);

创建表之后，删除外键

ALTER TABLE employee DROP FOREIGN KEY emp_depid_fk;

 ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

创建表之后，添加外键

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);

ALTER TABLE employee ADD CONSTRAINT emp_depid_fk FOREIGN KEY (dep_id) REFERENCES department(id);

级联更新与级联删除

级联操作表示的是联动的关系，当主表的主键修改（更新或者删除）后，从表的外键也会随着主表的主键的改变（更新或者删除）而改变

从上表的具体实现则表示当我们删除（更新）部门表department中的主键记录，那么在员工表employee中以此主键为外键的记录也会被删除（更新）

添加级联操作

在添加外键的基础上添加级联更新和级联删除，这两种是可选的，可以添加任意一种，也可以两种都添加

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) [ON UPDATE CASCADE] [ON DELETE CASCADE];

级联更新：ON UPDATE CASCADE

级联删除：ON DELETE CASCADE