Mysql之约束类型讲解以及标识列

什么是约束？一种限制，用于限制表中列的数据，确保数据的准确性和可靠性

Mysql中约束类型：

①not null(非空):限制列中的数据不能为null

②check(检查):检查列中的数据是否符合要求，mysql中并不支持

③default(默认):若没有插入值则使用默认值

④primary key(主键):限制此列不能为null并且唯一，一般将id设置为主键列

⑤unique(唯一):限制此列唯一但可以为null

⑥foreign key(外键):用于限制两个表的关系，用于保证该字段的值必须来自于主表关联列的值

添加约束的时机：

1.创建表时

2.修改表时

约束添加的类型：

1.列级约束

语法：在列名和数据类型之后添加约束即可

CREATE TABLE IF NOT EXISTS major(
	id INT PRIMARY KEY,
	mName VARCHAR(20) NOT NULL
);
CREATE TABLE IF NOT EXISTS stuinfo(
	id INT PRIMARY KEY,#添加主键约束
	stuName VARCHAR(20) NOT NULL,#添加非空约束
	gender CHAR(1) CHECK(gender='男' OR gender='女'),#添加检查约束，mysql并不支持，但语法上不报错
	age INT DEFAULT 18,#添加默认约束
	seat INT UNIQUE,#添加唯一约束
	majorId INT REFERENCES major(id)#外键约束，列级约束使用外键并不生效
);

 

2.表级约束(not null、default并不支持)

说明：在各个字段的最下面

[constraint 别名] 约束类型(字段) [外键的引用]

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE IF NOT EXISTS stuinfo(
	id INT,
	stuName VARCHAR(20),
	gender CHAR(1),
	age INT ,
	seat INT ,
	majorId INT,
	CONSTRAINT pk PRIMARY KEY(id),
	CONSTRAINT uq UNIQUE(seat),
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorId) REFERENCES major(id)  
);

 说明：为主键约束起别名将不会生效

主键索引与唯一索引的对比：

                         唯一       为空     一个表中可以有多少个        是否允许组合

主键索引：        √             ×             表中只能有一个                允许，但不建议 

唯一索引：        √             √             表中可以有多个                允许，但不建议

外键约束的特点： 

1.要求在从表中设置外键

2.从表中的外键数据类型要与主表中的被引用的数据类型一致

3.只能有一个外键约束并且引用的是主表中的一个key

4.先添加主表数据，再添加从表数据。删除从表数据，再删除主表数据

DROP TABLE IF EXISTS major;
CREATE TABLE IF NOT EXISTS major(
	id INT,
	mName VARCHAR(20) NOT NULL,
	seat INT UNIQUE
);
DROP TABLE IF EXISTS t_test;
CREATE TABLE t_test(
	id INT,
	seatId INT,
	CONSTRAINT fk FOREIGN KEY(seatId) REFERENCES major(seat)
);

约束类型的通用写法： 

CREATE TABLE IF NOT EXISTS stuinfo(
	id INT PRIMARY KEY,
	stuName VARCHAR(20) NOT NULL,
	gender CHAR(1) CHECK(gender='男' OR gender='女'),
	age INT DEFAULT 18,
	seat INT UNIQUE,
	majorId INT,
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorId) REFERENCES major(id) 
);

添加约束类型：

1.添加列级约束类型：

语法：alter table tName modify column 列名 数据类型 约束类型;

2.添加表级约束类型：

语法：alter table tName add [constraint 别名] 约束类型(列名) [外键的引用];

①添加非空约束

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

②添加列级主键约束 

ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;

③添加表级主键约束 

ALTER TABLE stuinfo ADD PRIMARY KEY(id);

 ④添加列级唯一约束

ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;

 ⑤添加表级唯一约束

ALTER TABLE stuinfo ADD UNIQUE(seat);

⑥添加表级外键约束

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

删除约束类型：

1.删除列级约束：

语法：alter table tName modify column 列名 数据类型;

ALTER TABLE stuinfo MODIFY COLUMN stuName VARCHAR(20);

 2.删除表级约束:

①删除表级主键约束：

ALTER TABLE stuinfo DROP PRIMARY KEY;

②删除表级唯一约束：

ALTER TABLE stuinfo DROP INDEX seat;

③删除表级外键约束：

ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

标识列：不需要手动的插入值，使用系统默认提供的值

语法：在约束后面添加auto_increment

CREATE TABLE t_test(
	id INT PRIMARY KEY AUTO_INCREMENT,
	tName VARCHAR(20) NOT NULL
);
INSERT INTO t_test VALUES(NULL,'John');

特点：

1.标识列必须是主键列吗？不一定，只要是key就行

CREATE TABLE t_test(
	id INT PRIMARY KEY ,
	tName VARCHAR(20) NOT NULL,
	seat INT AUTO_INCREMENT
);

 2.一个表中可以有几个标识列？只能有一个

CREATE TABLE t_test(
	id INT PRIMARY KEY AUTO_INCREMENT,
	tName VARCHAR(20) NOT NULL,
	seat INT AUTO_INCREMENT
);

 3.标识列只能是数值类型的列

CREATE TABLE t_test(
	id INT PRIMARY KEY,
	tName VARCHAR(20) NOT NULL AUTO_INCREMENT,
);

 4.可以手动设置自增长的类型，设置自增长的初始值mysql并不支持

SHOW VARIABLES LIKE '%auto_increment%';

 

SET auto_increment_increment=5;#设置增长步长

INSERT INTO t_test VALUES(NULL,'Lily');

 

修改表时设置标识列：

ALTER TABLE tName modify column 列名 数据类型 约束 auto_increment;