SQL_约束

约束

​ *概念：对表中的数据进行限定，保证数据的正确性、有效性和完整性

*分类：

• 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,值不能重复

​ 1.创建表时，添加唯一约束

CREATE TABLE stu(
       id INT,
       phone_number VARCHAR(20) UNIQUE -- 添加了唯一约束
      
);

​ *注意MySQL中，唯一约束限定的列的值可以有多个null

​ 2.删除唯一约束

ALTER TABLE stu DROP INDEX phone_number;

​ 3.在创建表之后，添加唯一约束

ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;

主键约束：primary key

​ 1.注意：

• ​ （1）含有：非空且唯一
• ​ （2）一张表只能有一个字段为主键
• ​ （3）主键就是表中记录的唯一标识

​ 2.创建表时，添加主键约束

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

​ 3.删除主键

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

​ 4.创建完表后，添加主键

ALTER TABLE stu MODIFY id INT PRIMARY KEY;

​ 5.自动增长：

• （1）概念：如果某一列是数值类型的，使用 auto_increment 可以来完成值的自动增长
• （2）在创建表时，添加主键约束，并且完成主键自增长

 create table stu(
             id int primary key auto_increment, -- 给id添加主键约束,并完成主键自动增长
             name varchar(20)     
);

INSERT INTO stu VALUES(NULL,'ccc');
INSERT INTO stu VALUES(10,'ccc');
INSERT INTO stu VALUES(NULL,'ccc');

• （3）删除自动增长

ALTER TABLE stu MODIFY id INT;

• ​ （4）添加自动增长

ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

外键约束

外键约束 ：foreign key 让表与表产生关系，从而保证数据的正确性

​ 1.创建表时，添加外键
​ 语法：
​ create table 表名（
​ …
​ 外键列
​ constrant 外键名称 foreign key 外键列名称 references 主表名称（主表列名称)
​ ）；

​ 2.删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

​ 3.创建完表之后，添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名称(主表列名称);

CREATE TABLE emp( -- 创建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 ('老王',28,'销售部','深圳');
INSERT INTO emp (NAME,age,dep_name,dep_location) VALUES ('大王',22,'销售部','深圳');
INSERT INTO emp (NAME,age,dep_name,dep_location) VALUES ('小王',18,'销售部','深圳');
 
SELECT * FROM emp;
-- 数据有冗余

-- 表的拆分
-- 解决方案：分成两张表
-- 创建部门表（id,dep_name,dep_location)
-- 一方，主表
CREATE TABLE department(
     id INT PRIMARY KEY AUTO_INCREMENT,
     dep_name VARCHAR(20), -- 部门名称
     dep_location VARCHAR(20) -- 部门地址
);

-- 创建员工表（id,name,age,dep_id)
-- 多方，从表
CREATE TABLE employee(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(30),
    age INT,
    dep_id INT, -- 外键对应主表的主键
    CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES  department(id)
);
-- 添加2个部门
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 ('老王',28,2);
INSERT INTO employee(NAME,age,dep_id) VALUES ('大王',22,2);
INSERT INTO employee(NAME,age,dep_id) VALUES ('小王',18,2);

SELECT * FROM employee;

-- 删除外键
ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;

-- 添加外键
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id);

4.级联操作
​ （1）添加级联操作

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

（2）分类：

• 1.级联更新：ON UPDATE CASCADE
• ​ 2.级联删除：ON DELETE CASCADE

-- 更改部门id
UPDATE employee SET dep_id = NULL WHERE dep_id = 1;
UPDATE employee SET dep_id = 5 WHERE dep_id IS NULL;

-- 添加外键，设置级联更新
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY 
(dep_id) REFERENCES department(id) ON UPDATE CASCADE;

-- 添加外键，设置级联更新,设置级联删除
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY 
(dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE;