SQL约束

约束的作用: 对表中的数据进行进一步的限制，从而保证数据的正确性、有效性、完整性，违反约束的不正确数据将无法插入到表中。

1 主键约束

特点：不可重复、唯一、非空

作用：用来表示数据库中的每一条记录

添加主键约束

-- 方式1 创建一个带主键的表
CREATE TABLE emp2(
    -- 设置主键 唯一 非空
    eid INT PRIMARY KEY,
    ename VARCHAR(20),
    sex CHAR(1)
);

-- 方式2 创建一个带主键的表
CREATE TABLE emp2(
    eid INT ,
    ename VARCHAR(20),
    sex CHAR(1),
    -- 指定主键为 eid字段
    PRIMARY KEY(eid)
);

-- 方式3 创建一个带主键的表
CREATE TABLE emp2(
    eid INT ,
    ename VARCHAR(20),
    sex CHAR(1)
)
-- 创建的时候不指定主键,然后通过 DDL语句进行设置
ALTER TABLE emp2 ADD PRIMARY KEY(eid);

哪些字段可以作为主键 ?

• 通常针对业务去设计主键，每张表都设计一个主键id
• 主键是给数据库和程序使用的，跟最终的客户无关，所以主键没有意义没有关系，只要能够保证不重复就好
  • 比如：身份证就可以作为主键

删除主键约束

-- 使用DDL语句 删除表中的主键
ALTER TABLE emp2 DROP PRIMARY KEY;

主键的自增

• 主键如果让我们自己添加很有可能重复，我们通常希望在每次插入新记录时，数据库自动生成主键字段的值

-- 创建主键自增的表,自定义自增起始值
-- 默认的 AUTO_INCREMENT 的开始值是 1
CREATE TABLE emp2(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20),
    sex CHAR(1)
)AUTO_INCREMENT=100;

DELETE和TRUNCATE对自增长的影响

• DELETE：只是删除表中所有数据，对自增没有影响
• TRUNCATE：将整个表删除掉然后创建一个新的表，自增的主键重新从1开始

2 非空约束

特点：某一列不允许为空

添加非空约束

-- 非空约束
CREATE TABLE emp2(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    -- 添加非空约束, ename字段不能为空
    ename VARCHAR(20) NOT NULL,
    sex CHAR(1)
);

3 唯一约束

特点： 表中的某一列的值不能重复( 对null不做唯一的判断 )

添加唯一约束

CREATE TABLE emp3(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    -- 为ename字段添加唯一约束
    ename VARCHAR(20) UNIQUE,
    sex CHAR(1)
);

主键约束与唯一约束的区别：

• 主键约束 唯一且不能够为空
• 唯一约束 唯一但是可以为空
• 一个表中只能有一个主键 , 但是可以有多个唯一约束

4 外键约束

添加外键约束

设计两张表来说明：

-- 创建部门表
-- 一方,主表
CREATE TABLE department(
    id INT PRIMARY KEY AUTO_INCREMENT,
    dep_name VARCHAR(30),
    dep_location VARCHAR(30)
);

-- 创建员工表
-- 多方 ,从表
CREATE TABLE employee(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20),
    age INT,
    dept_id INT
);

-- 方式1 新建表时添加外键
CREATE TABLE employee(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20),
    age INT,
    dept_id INT,
    -- 添加外键约束
    CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id)
);

-- 方式2 已有表添加外键
-- 可以省略外键名称, 系统会自动生成一个
ALTER TABLE employee ADD FOREIGN KEY (dept_id) REFERENCES department (id);

外键约束的注意事项

• 从表外键类型必须与主表主键类型一致，否则创建失败

• 添加数据时，应该先添加主表中的数据

  -- 添加一个新的部门
  INSERT INTO department(dep_name,dep_location) VALUES('市场部','北京');
  -- 添加一个属于市场部的员工
  INSERT INTO employee(ename,age,dept_id) VALUES('老胡',24,3);
  

• 删除数据时，应该先删除从表中的数据

  -- 删除数据时 应该先删除从表中的数据
  -- 报错 Cannot delete or update a parent row: a foreign key constraint fails
  -- 报错原因 不能删除主表的这条数据,因为在从表中有对这条数据的引用
  DELETE FROM department WHERE id = 3;
  

  -- 先删除从表的关联数据
  DELETE FROM employee WHERE dept_id = 3;
  -- 再删除主表的数据
  DELETE FROM department WHERE id = 3;
  

级联删除操作

• 如果想实现删除主表数据的同时，也删除掉从表数据，可以使用级联删除操作

  -- 重新创建添加级联操作
  CREATE TABLE employee(
      eid INT PRIMARY KEY AUTO_INCREMENT,
      ename VARCHAR(20),
      age INT,
      dept_id INT,
      
      CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id)
      -- 添加级联删除
      ON DELETE CASCADE
  );