MySQL中常见约束与实践

MySQL中列属性(主键、唯一键和自增等)使用实践

常见约束说明

约束类型	说明
NOT NULL	非空，用于保证该字段的值不能为空
DEFAULT	默认，用于保证该字段有默认值
UNIQUE	唯一，用于保证该字段的值具有唯一性，可以为空
CHECK	检查约束【mysql中不支持】
PRIMARY KEY	主键，用于保证该字段的值具有唯一性，并且非空
FOREIGN KEY	外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值。 在从表添加外键约束，用于引用主表中某列的值。

MySQL不支持check约束，但可以使用check约束，而没有任何效果。唯一约束，允许出现多个空值：NULL。

约束位置说明

位置	支持的约束类型	是否可以起约束名
列级约束： 列的后面	六大约束语法语法都支持，但外键没有效果	不可以
表级约束： 所有列的下面	默认和非空不支持，其他支持	可以（主键没有效果）

【1】常见约束类型

约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、唯一性。可以在创建表或者修改表时添加/修改约束。

修改表时添加约束语法

#添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;

#添加表级约束
alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;

① 主键约束 primary key

主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值。 每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别创建。

创建表时添加主键约束

#列级约束
CREATE TABLE emp4(
id INT AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(20));

#表级模式
CREATE TABLE emp5(
id INT NOT NULL AUTO_INCREMENT,
NAME VARCHAR(20),
pwd VARCHAR(15),
CONSTRAINT emp5_id_pk PRIMARY KEY(id));

#组合约束
CREATE TABLE emp6(
id INT NOT NULL,
NAME VARCHAR(20),
pwd VARCHAR(15),
CONSTRAINT emp7_pk PRIMARY KEY(NAME,pwd)
);

添加主键约束

ALTER TABLE stuinfo ADD PRIMARY KEY(id);

ALTER TABLE emp2 ADD CONSTRAINT my_emp_id_pk PRIMARY KEY(id);

删除主键约束

ALTER TABLE emp5 DROP PRIMARY KEY;

修改主键约束

ALTER TABLE emp5
MODIFY id INT PRIMARY KEY;

MySQL的主键名总是PRIMARY，当创建主键约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。

② 外键约束 foreign key

外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系 ，同一个表可以有多个外键约束。

从表的外键值必须在主表中能找到或者为空。当主表的记录被从表参照时，主表的记录将不允许删除。如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据。还有一种就是级联删除子表数据。

创建外键约束

CREATE TABLE dept(
dept_id INT AUTO_INCREMENT PRIMARY KEY,
dept_name VARCHAR(20)
);

CREATE TABLE emp(
emp_id INT AUTO_INCREMENT PRIMARY KEY,
last_name VARCHAR(15),
dept_id INT,
CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id)
);

创建多列外键组合，必须使用表级约束:

CREATE TABLE classes(
id INT,
NAME VARCHAR(20),
number INT,
PRIMARY KEY(NAME,number));

CREATE TABLE student(
id INT AUTO_INCREMENT PRIMARY KEY,
classes_name VARCHAR(20),
classes_number INT,
FOREIGN KEY(classes_name,classes_number) REFERENCES classes(NAME,number));

删除外键约束

ALTER TABLE emp DROP FOREIGN KEY emp_dept_id_fk;

增加外键约束

ALTER TABLE emp2 ADD CONSTRAINT fk_emp2_dept2 
FOREIGN KEY(dept_id) REFERENCES dept2(id);

注意：外键约束的参照列，在主表中引用的只能是主键或唯一键约束的列。

关键字说明

–FOREIGN KEY: 在表级指定子表中的列
–REFERENCES: 标示在父表中的列
–ON DELETE CASCADE(级联删除): 当父表中的列被删除时，子表中相对应的列也被删除
–ON DELETE SET NULL(级联置空): 子表中相应的列置空

③ 唯一约束 unique

唯一约束是指定 table 的列或列组合不能重复，保证数据的唯一性。 唯一约束不允许出现重复的值，但是可以为多个 null 。 同一个表可以有多个唯一约束，多个列组合的约束。在创建唯一约束时，如果不给唯一约束名称，就默认和列名相同。 唯一约束不仅可以在一个表内创建，而且可以同时多表创建组合唯一约束。

MySQL会给唯一约束的列上默认创建一个唯一索引。

创建表时创建唯一约束：

CREATE TABLE USER(id INT NOT NULL,
NAME VARCHAR(25),
PASSWORD VARCHAR(16),
#使用表级约束语法
CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD));

创建表后添加约束

#表级约束
ALTER TABLE USER ADD UNIQUE(NAME,PASSWORD);

ALTER TABLE USER ADD CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD);

#列级约束
ALTER TABLE USER MODIFY NAME VARCHAR(20) UNIQUE;

删除约束：

ALTER TABLE USER DROP INDEX uk_name_pwd;

④ 非空约束 not null 与 默认值 default

非空约束用于确保当前列的值不为空值，非空约束只能出现在表对象的列上。 Null 类型特征： 所有的类型的值 都可以是 null ，包括 int 、 float 等数据类型

创建非空约束

CREATE TABLE emp(id INT(10) NOT NULL,
NAME VARCHAR(20) NOT NULL DEFAULT 'abc',
sex CHAR NULL);

增加not null 约束：

#非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20)  NOT NULL;

#默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;

取消not null 约束：

ALTER TABLE emp MODIFY sex VARCHAR(30) NULL;

取消not null 约束，增加默认值：

ALTER TABLE emp MODIFY NAME VARCHAR(15) DEFAULT 'abc' NULL;

⑤ 创建表时设置约束

在列后面设置

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

在表字段下面设置

CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT,
	
	CONSTRAINT pk PRIMARY KEY(id),#主键
	CONSTRAINT uq UNIQUE(seat),#唯一键
	CONSTRAINT ck CHECK(gender ='男' OR gender  = '女'),#检查
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键
);

⑥ check约束

MySQL可以使用check约束，但check约束对数据验证没有任何作用,添加数据时，没有任何错误或警告。

CREATE TABLE temp(
id INT AUTO_INCREMENT,
NAME VARCHAR(20),
age INT CHECK(age > 20),
PRIMARY KEY(id));

【2】删除约束

① 删除非空约束-modify

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

② 删除默认约束-modify

ALTER TABLE stuinfo MODIFY COLUMN age INT ;

③ 删除主键-drop

ALTER TABLE stuinfo DROP PRIMARY KEY;

④ 删除唯一-drop

ALTER TABLE stuinfo DROP INDEX seat;

⑤ 删除外键-drop

ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;