MySQL学习笔记之约束，创建约束、修改约束、删除约束

一、约束的含义与分类

• 含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确性和可靠性

• 分类：六大约束

  NOT NULL： 非空，用于保证该字段的值不能为空，比如姓名、学号
  DEFAULT: 默认，用于保证该字段有默认值
  PRIMARY KEY： 主键，用于保证该字段具有唯一性，并且非空
  UNIQUE： 唯一，用于保证该字段是唯一的，但是可以为空
  CHECK： 检查，【mysql中不支持】，用于检查值
  FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值。一般是在从表中添加外键约束，用于引用主表中某列的值，如学生表的专业编号，员工表的部门编号

• 添加约束的时机：

  ①创建表时
  ②修改表时

  插入数据后不可再添加

• 约束的添加分类：

  ①列级约束
  六大约束的语法条件都支持，但外键约束没有效果
  ②表级约束
  除了非空、默认，其他的都支持

• 主键和唯一的对比：

	保证唯一性	是否允许为空	一个表中可以有多少个	是否允许组合
主键	√	不允许	至多1个主键	允许，但不推荐
唯一	√	允许	可以有多个唯一	允许，但不推荐

注意：

• 唯一约束虽然可以为空，但是最多仅能有一个null，如下案例3

• 主键和唯一的组合表示，将多个列组合起来行使主键或唯一的功能，如下案例4

• 外键：

  • 要求在从表设置外键关系
  • 从表的外键列的类型和主表的观念列的类型要求一致或兼容，名称无要求
  • 主表的关联列必须是一个key（一般是主键或唯一）如案例2所示
  • 插入数据时，先插入主表，再插入从表；删除数据时，先删除从表，再删除主表
  • 另外：同一个字段后可以在合理的情况下加多个约束

二、创建表时添加约束

• 语法：

CREATE TABLE 表名(
	字段名  字段类型  列级约束,	--列级约束放在字段定义的后面
	字段名  字段类型,
	表级约束			         --表级约束放在所有字段定义的后面
);

2.1添加列级约束

• 语法：

直接在字段名和类型后面追加 约束类型即可
仅支持默认、 非空、 唯一主键

案例1

CREATE TABLE major(
	id INT PRIMARY KEY,
	majorName VARCHAR(15)
);

CREATE DATABASE stu;
CREATE TABLE stuinfo(
	id INT PRIMARY KEY,#主键约束
	stuName VARCHAR(20) NOT NULL,#非空约束
	gender CHAR(1) CHECK(gender = '男' OR gender = '女'),#检查约束
	seatId INT UNIQUE,#唯一约束
	age INT DEFAULT 18,#默认约束
	majorId INT REFERENCES major(id)#外键约束
);
DESC stuinfo;

查看表中的所有索引，包括主键、唯一、外键

SHOW INDEX FROM stuinfo;

2.2 表级约束

• 语法：

• 在各个字段的最下面
  [constraint 自定义的约束名] 约束类型(字段名)

案例2

DROP TABLE major;
CREATE TABLE major(
	ide INT PRIMARY KEY,  #这个必须是主键，然后从表才可以外键引用他
	majorName VARCHAR(10)
);

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	gender CHAR(1),
	stuName VARCHAR(20),
	seat INT,
	age INT,
	majorId INT,
	--在末尾添加表级约束
	PRIMARY KEY(id),
	UNIQUE(seat),
	CHECK(gender = '男' OR gender = '女'),
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorId) REFERENCES major(ide)
);

SHOW INDEX FROM stuinfo;

2.3 通用的写法：

CREATE TABLE IF NOT EXISTS stuinfo(
	id INT PRIMARY KEY,
	stuName VARCHAR(20) NOT NULL,
	sex CHAR(1),
	age INT DEFAULT 18,
	seat INT UNIQUE,
	majorid INT,
	--仅仅只是把外键放在表级约束
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(ide)
);

案例3

DROP TABLE IF EXISTS major;
CREATE TABLE major(
	id INT PRIMARY KEY,
	majorName VARCHAR(20)
);

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT PRIMARY KEY,
	seat INT UNIQUE
);

INSERT INTO stuinfo VALUES(1,NULL),VALUES(2,NULL);#尝试插入2个null，出错！
INSERT INTO stuinfo VALUES(1,NULL),(2,12);#唯一约束，最多只有一个null，该语句正常通过

SELECT * FROM stuinfo;
DESC stuinfo;

案例4

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT ,
	stuName VARCHAR(20),
	seat1 INT,
	seat2 INT2,
	PRIMARY KEY(id,stuName), --主键也可以组合使用
	UNIQUE(seat1,seat2)		--唯一键也可以组合使用
);

DESC stuinfo;
SHOW INDEX FROM stuinfo;

INSERT INTO stuinfo VALUES
(1,'jhon',1,1),
(2,'jhon',1,2),
(1,'mike',2,1),
(2,'mike',2,2); --如上所示，不管怎么组合，只要组合不重复，就可以插入

三、修改表时添加约束

• 语法：

  列级约束
  	alter table 表名 modify column 字段名 字段类型 新约束;
  表级约束
  	alter table 表名 add 约束类型(字段名);
  	alter table 表名 add 【constraint 约束别名】约束类型(字段名) 【references 主表（关联列字段名）】
  

在修改表时添加约束，如下所示

3.1.添加非空约束

ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;

3.2.添加默认约束

ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;

3.3添加主键,主键因为可以分别在列级和表级添加，因此有以下两种形式

ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY; #列级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);	      #表级约束

3.4添加唯一,唯一也有列级和表级两种形式的

ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
ALTER TABLE stuinfo ADD UNIQUE(seat); 

3.5添加外键,表级约束

ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(id) REFERENCES major(id);

四、修改表时删除约束

4.1删除非空约束，通过修改的方式删除

ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20);

4.2删除默认约束

ALTER TABLE stuinfo MODIFY COLUMN age INT;

4.3.删除主键，因为主键只有一个，所以可以直接删除Drop

注意：删除主键时通常需要先删除所关联的外键

ALTER TABLE stuinfo MODIFY COLUMN id INT;
ALTER TABLE stuinfo DROP PRIMARY KEY;

4.4 删除唯一约束

ALTER TABLE stuinfo MODIFY COLUMN seat INT;

注意：删除唯一约束是Drop Index seat 而不是Drop unique seat，其中seat是某个字段名

ALTER TABLE stuinfo DROP INDEX seat_3;

4.5 删除外键约束

ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;
DESC stuinfo;
SHOW INDEX FROM stuinfo;