MySQL基础篇之六：约束

MySQL约束

概述

• 概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据
• 目的：保证数据库中数据的正确性、有效性和完整性
• 分类：

主键约束 PRIMARY KEY

• 主键约束用于表示数据表中的每一行记录，并保证该标识具有唯一性。在创建数据表时，可以在要定义主键的列后面添加PRIMARY KEY关键字来定义主键约束。

CREATE TABLE mytable (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    email VARCHAR(50) NOT NULL UNIQUE
);

以上代码中，id列被定义为主键，确保每一行记录都有唯一的标识。

唯一约束 UNIQUE

• 唯一约束用于保证某些列中的值不重复。在创建数据表时，可以在要添加唯一约束的列后面添加UNIQUE关键字来定义唯一约束。

CREATE TABLE mytable (
    id INT NOT NULL,
    name VARCHAR(20) NOT NULL,
    email VARCHAR(50) NOT NULL UNIQUE,
    PRIMARY KEY (id)
);

以上代码中，email列被定义为唯一约束，确保每个邮件地址只能在表中出现一次。

检查约束 CHECK

• 检查约束用于限制某些列中的取值范围。在创建数据表时，可以在要添加检查约束的列后面添加CHECK关键字来定义检查约束。

CREATE TABLE mytable (
    id INT NOT NULL PRIMARY KEY,
    age INT CHECK (age > 18),
    name VARCHAR(20) NOT NULL
);

以上代码中，age列被定义为检查约束，确保其值大于18。

非空约束 NOT NULL

• 确保在插入或更新数据时必须为列指定一个非控制，可以在字段列后面用NOT NULL定义。

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  department VARCHAR(50),
  salary DECIMAL(10,2) NOT NULL
);

以上代码中，name和salary列被定义为非空约束，确保其值在添加的过程中不能为空。

默认约束 DEFAULT

• 在插入数据时如果没有指定该列的值，则会使用默认值，我们用DEFAULT定义约束。

CREATE TABLE messages (
  id INT PRIMARY KEY,
  sender VARCHAR(50),
  receiver VARCHAR(50),
  subject VARCHAR(100),
  body TEXT,
  sent_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

以上代码中，sent_at字段被定义为默认约束，如果插入时字段的值为空，则使用CURRENT_TIMESTAMP默认值。

自增约束 AUTO_INCREMENT

• 用于创建一个自增的整数列，每次插入新数据时自动加1，我们用AUTO_INCREMENT字段控制约束。

CREATE TABLE posts (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(100),
  content TEXT,
  created_at DATETIME
);

以上代码中，id字段被定义为自增约束，我们在插入数据的时候不用定义id的值，他的值默认会+1；

外键约束 FOREIGN KEY

• 外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

例如：现在有一张员工表，员工表中dept_id字段表示当前员工属于哪个部门，关联部门表的主键，那么此时我们的dept_id就是当前员工表的外键

在这两张表的关联中，我们把部门表称之为父表，把员工表称之为子表

• 目前上述的两张表，仅有逻辑上的关联，但是在数据库层面，并未建立外键关联。所以是无法保证数据的一致性和完整性的。

比如，我在部门表中删除了id为1的研发部，但是此时员工表中的dept_id为1的员工并没有被关联性删除。

• 添加外键语法：

  #创建表的过程中添加外键：
  create table 表名{
  	字段名,数据类型,
  	...
  	[constranint] [外键名称] foreign key(外键字段名) references 主表(主表列名)
  };
  
  #表创建完成后给字段添加外键：
  alter table 表名 add constranint 外键名称 foreign key(外键字段名) refernces 主表(主表列名);
  

  例如：给emp表的dept_id添加外键，关联部门表的主键id

  #添加外键
  alter table emp add constraint fk_emp_dept_id foreign key(dept_id) references dept(id);
  

此时我们再选择删除部门表的id为1的部门时，就会报错。

• 删除外键语法

alter table 表名 drop foreign key 外键名称;

例：删除刚刚给emp表中dept_id添加的外键

alter table emp drop foreign key fk_emp_dept_id;

• 删除/更新行为

  • 语法：

    alter table 表名 add constraint 外键名称 foreign key (外键字段) references 主表名(主表字段名称) on update cascade on delete cascade;
    

其中，NO ACTION和RESTRICT是MySQL的默认行为

#为emp表和dept表建立外键约束，并指定删除和更新行为是cascade
alter table emp 
add constraint fk_emp_dept_id 
foreign key (dept_id) 
references dept(id)
on UPDATE cascade on delete cascade ;