约束：主键、非空、唯一、外键

2023-12-22 19:01:51

一、概念

对表中的数据进行限定，保证数据的正确性、有效性和完整性。

二、分类

1）、主键约束：primary key

2）、非空约束：not null

3）、唯一约束：unique

4）、外键约束：foreign key

三、非空约束

not null，某一列的值不能为null

1、创建表时添加约束

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

2、创建表完后，添加非空约束

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

3、删除name的非空约束

ALTER TABLE stu MODIFY NAME VARCHAR(20);

四、唯一约束

unique，某一列的值不能重复

注意：唯一约束可以有NULL值，但是只能有一条记录为null。

1、在创建表时，添加唯一约束

CREATE TABLE stu(
    id INT,
    phone_number VARCHAR(20) UNIQUE -- 手机号
);

2、删除唯一约束

ALTER TABLE stu DROP INDEX phone_number;

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

ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;

五、主键约束

注意：

（1）、含义：非空且唯一

（2）、一张表只能有一个字段为主键

（3）、主键就是表中记录的唯一标识

1、在创建表时，添加主键约束

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

2、删除主键

-- 错误 alter table stu modify id int ;
ALTER TABLE stu DROP PRIMARY KEY;

3、创建完表后，添加主键

ALTER TABLE stu MODIFY id INT PRIMARY KEY;

4、自动增长

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

在创建表时，添加主键约束，并且完成主键自增长

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

删除自动增长

ALTER TABLE stu MODIFY id INT;

添加自动增长

ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

六、外键约束

1、为什么要使用外键约束？

# 创建部门表
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');
# 创建员工表
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
gender CHAR(1), -- 性别
salary DOUBLE, -- 工资
join_date DATE, -- 入职日期
dept_id INT
);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);

emp表如下：

部门表如下：

此时如果我们删除了开发部，

DELETE FROM dept WHERE id = 1;

虽然删除了开发部，但是这是不合适的，因为孙悟空和蜘蛛精都属于开发部，此时开发部还有人，故不应该删除开发部，如果删除的话，数据就不完整不正确了。

我们使用外键约束可以解决这个问题。让emp表的dept_id关联dept表的主键id即可。

我们添加外键

TRUNCATE TABLE emp;
ALTER TABLE emp ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dept_id) REFERENCES dept(id);

INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);

注意：要先删除数据才能添加外键。

此时删除开发部

此时，如果你想在emp表中添加一条记录而且dept_id不存在，如dept_id为4，则无法添加，因为有外键约束。

注意：外键可以为null，但不能为不存在的外键值。

2、foreign key,让表于表产生关系，从而保证数据的正确性。

1、在创建表时，可以添加外键

语法

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

2、删除外键

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

3、创建表之后，添加外键

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

七、级联操作

分类：

1）、级联更新：ON UPDATE CASCADE
2）、级联删除：ON DELETE CASCADE

1、为什么要添加级联操作？

现在两个表emp和dept，如下所示：

如果我们想把开发部的id改为4，则提示如下：

我们也可以将emp表中id为1的dept_id先改为null，再修改dept表中开发部的id为4，再将dept_id为null的记录改为dept_id为4.

UPDATE emp SET dept_id = NULL WHERE dept_id = 1;
UPDATE emp SET dept_id = 4 WHERE dept_id IS NULL;

但是这个操作很麻烦，那能不能当我们修改了dept表的主键id的值，然后emp表的相关的dept_id自动修改了呢？用级联操作可以解决。

我们先删除外键

ALTER TABLE emp DROP FOREIGN KEY emp_dept_fk;

2、级联更新

添加外键时添加级联操作

语法

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

此时，如果修改dept表的id，那么emp表的dept_id自动修改。

3、级联删除

如果在dept表中删除id为1的开发部，那么会自动删除emp表中dept_id为1的数据。

注意：级联删除要慎用，级联删除很危险，有可能删除很多数据。

码农公寓