一、约束的含义与分类

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

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;