mysql学习-表的约束

约束

• 概念: 对表中的数据进行限定,从而保证数据的正确性,有效性和完整性.
• 分类:
  • 主键约束: primary key.
  • 非空约束: not null.
  • 唯一约束: unique.
  • 外键约束: foreign key.

非空约束: not null,值不能为空

添加约束方法1—在db1数据库创建表时添加约束:

USE db1;
CREATE TABLE IF NOT EXISTS stu(
	id INT,
	NAME VARCHAR(20) NOT NULL -- 表示name不能为空

添加约束方法2—创建表结束后,添加非空约束

alter table stu change name name varchar(20) not null;

删除name的非空约束:

alter table stu change name name varchar(20);

唯一约束: unique,值不能重复

• 注意: 在mysql中,当列添加了唯一约束时,我们添加该列的值为null时,是不会报错的,即唯一约束的列可以有多个null.

创建表时添加唯一约束

CREATE TABLE IF NOT EXISTS stu(
	id INT UNIQUE,
	NAME VARCHAR(20) NOT NULL,
	phone VARCHAR(20) UNIQUE
);

创建表后添加唯一约束

ALTER TABLE stu MODIFY phone VARCHAR(20) UNIQUE;
ALTER TABLE stu CHANGE phone phone VARCHAR(20) UNIQUE;

删除唯一约束,这个和删除非空约束不一样

ALTER TABLE stu DROP INDEX phone;

主键约束

• 注意:
  • 主键约束表示该字段非空且唯一.
  • 一张表只能有一个字段为主键.
  • 其实主键就是表中每条记录的唯一标示,例如每个人的身份证号.

创建stu表时给id添加主键约束

CREATE TABLE IF NOT EXISTS stu(
	id INT PRIMARY KEY AUTO_INCREMENT, 
	NAME VARCHAR(20)
); 

创建表以后添加主键同时设置自动增长

ALTER TABLE stu CHANGE id id INT PRIMARY KEY AUTO_INCREMENT;

删除stu表中的id主键约束,不是删除这一列哈,只是删除它的约束(这个和唯一约束一样,删除方式和其它不大一样).

-- 先删除自动增长
ALTER TABLE stu MODIFY id INT;
-- 再删除主键约束
ALTER TABLE stu DROP PRIMARY KEY;

但是这里需要注意: 设置了自动增长的主键约束的列或者字段都是不能直接进行修改操作的,我们只能删除主键那一列的所有数据不能只删除主键约束,或者我们可以先删除自动增长,再删除主键约束,再进行修改操作.

自动增长

• 注意:
  • 自动增长要和主键一起用.
  • 如果主键是数值类型的,使用auto_increment可以完成值的自动增长.
  • 自动增长只跟上一行的值有关系,比如上一行我添加一个元素,给定id为525,则下一行id自动增长为526.

创建stu表时给id添加主键约束同时使得id自增长

CREATE TABLE IF NOT EXISTS stu(
	id INT PRIMARY KEY AUTO_INCREMENT, 
	NAME VARCHAR(20)
);

创建表以后添加主键同时设置自动增长

ALTER TABLE stu CHANGE id id INT PRIMARY KEY AUTO_INCREMENT;

删除stu表中的id自动增长,不是删除这一列哈,只是删除它的约束(这个和唯一约束不一样).

ALTER TABLE stu MODIFY id INT;

外键约束

• 外键主要是为了让表与表之间产生关系,保证数据的正确性.

创建emp表同时赋值数据,我们发现这样子会很冗余,也就是说部门名字和部门位置会有很多重复的.

-- 创建emp表
CREATE TABLE emp(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(30) NOT NULL,
	age INT NOT NULL,
	dep_name VARCHAR(20),-- 部门名称
	dep_location VARCHAR(20)
);
为了解决这个冗余的问题,我们进行表的拆分,然后两个表关联起来就好了,
即将一个表用来存放员工信息,一个表用来存放部门信息.

创建部门和员工表

-- 创建部门表
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(30) NOT NULL,
	age INT NOT NULL,
	dep_id INT,-- 对应部门表的主键id
);

在创建表时添加外键

语法: create table 表名(
	字段名 字段类型,
	字段名 字段类型,
	...
	外键字段名 字段类型,
	constraint 外键名称 foreign key(外键列名) reference 主表名称(主表字段名称)
	**千万注意这里constraint是另外一个语句,所以前面的要加逗号**.
);
-- 创建员工表
	CREATE TABLE employee(
		id INT PRIMARY KEY AUTO_INCREMENT,
		NAME VARCHAR(30) NOT NULL,
		age INT NOT NULL,
		dep_id INT,-- 对应部门表的主键id
		constraint emp_dep foreign key(dep_id) reference department(id)-- 将部门表的id作为员工表dep_id的外键约束
	);

• 当创建成功外键后,我们去删除部门表中的某个部门会报错,因为员工表有这个部门的人存在,当我们去员工表添加不存在的部门的时候也会报错,这在没有外键约束的时候是没有的功能.

删除外键

alter table 要添加外键的表名 drop foreign key 外键名;
-- 删除emmployee表中的外键约束
alter table employee drop foreign key emp_dep;

在创建表之后创建外键

alter table 要添加外键的表名 add constraint 自定义外键名 foreign key(要添加外键约束的字段名) reference 外键约束所在列表(外键约束字段)
-- 给employee的dep_id添加外键约束
ALTER TABLE employee ADD CONSTRAINT emp_dep FOREIGN KEY(dep_id) REFERENCES department(id);
此时要保证employee中所有员工的部门标号在department中都是存在的,不然该语句不会更新成功.

##外键约束_级联操作

• 例如在上述例子中,我们希望当我们更改部门表中的id字段的时候,员工表自动更新为相应的id,如果没有级联操作,部门表的id字段修改会报错.

创建表之后添加外键的时候设置级联更新

ALTER TABLE employee ADD CONSTRAINT emp_dep FOREIGN KEY(dep_id) REFERENCES department(id) ON UPDATE CASCADE;

创建表之后添加外键的时候设置级联更新以及级联删除

已有外键要先删除外键
ALTER TABLE employee ADD CONSTRAINT emp_dep FOREIGN KEY(dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE;
此时删除部门表中的相关部门就会删除员工表中的该部门的所有数据,所以级联删除慎用!!!!!!