MYSQL 如何查看约束 mysql约束名是什么

文章目录

• 1.约束的概念及分类
• 2.几种约束类型

• （1）主键约束
• （2）主键自增约束
• （3）唯一约束
• （4）非空约束
• （5）外键约束
• （6）外键级联更新和外键级联删除

1.约束的概念及分类

• 约束的概念
  约束是对表中的数据进行限定，保证数据的正确性、完整性和有效性等。
• 约束的种类

约束	说明
PRIMARY KEY	主键约束
PRIMARY KEY AUTO_INCREMENT	主键、自动增长
UNIQUE	唯一约束
NOT NULL	非空约束
FOREIGN KEY	外键约束
FOREIGN KEY ON UPDATE CASCADE	外键级联更新
FOREIGN KEY ON DELETE CASCADE	外键级联删除

2.几种约束类型

（1）主键约束

• 特点：
  1.主键约束=唯一约束+非空约束
  2.一张表只能有一列设为主键
  3.主键用于表中数据的唯一标识
• 添加主键约束的方法

-- 标准语法
CREATE TABLE 表名(
	列名 数据类型 PRIMARY KEY,
    列名 数据类型,
    ...
);

-- 创建student表
CREATE TABLE student(
	id INT PRIMARY KEY,
	name varchar(30),
	age INT
);
-- 查询student表
SELECT * FROM student;
DESC student; -- 查询表结构

查询表结构显示如下：

KEY处，id显示PRI，表示主键。

• 插入正常数据和不符合规则的数据

-- 插入数据
INSERT INTO student VALUES(1, 'wzh', 23);
INSERT INTO student VALUES(1, 'hzw', 13);-- 错误！因为id是主键 唯一的 不能添加

显示如下:

-- 主键默认非空，不能添加null的数据
INSERT INTO student VALUES (NULL,'aaa',20);

添加主键为空的数据显示如下：

• 删除主键约束

-- 标准语法
ALTER TABLE 表名 DROP PRIMARY KEY;

-- 删除student表的主键约束
ALTER TABLE student DROP PRIMARY KEY

-- 查询student表结构
DESC student;

显示如下：

Key处的主键标识已经删除了。

此时id的Null标识为NO，表示id不能为空。此时插入id为空的数据会报错。如果不想指定id，让其自动设定id呢，看下面主键自增约束。

• 建表后单独添加主键
  如果删除后想再给student表添加主键约束：

-- 标准语法
ALTER TABLE 表名 MODIFY 列名 数据类型 PRIMARY KEY;

ALTER TABLE student MODIFY id INT PRIMARY KEY;

（2）主键自增约束

主键自增约束与主键约束的不同之处在于主键可以为空，为空时自动设置值，值自小到大增长，且是唯一的。

-- 标准语法
CREATE TABLE 表名(
	列名 数据类型 PRIMARY KEY AUTO_INCREMENT,
    列名 数据类型,
    ...
);

-- 创建stu表，给id添加 主键自增约束
CREATE TABLE stu(
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(30),
	age INT
);
DESC stu;-- extra栏显示auto_increment

查询表结构显示如下：

-- 添加数据
INSERT INTO stu VALUES (1,'aa',10),(2,'bb',11);
-- 添加null值，会自动增长
INSERT INTO student2 VALUES (NULL),(NULL);

-- 查询stu表
SELECT * FROM stu;

此时再插入id号为4的数据试试：

报错了，因为主键自增id必须是唯一的。若此时插入id为7的数据是成功的，再在后面插入id为NULL的数据时id会从8开始。

• 删除主键自增约束

-- 标准语法
ALTER TABLE 表名 MODIFY 列名 数据类型;

-- 删除自动增长
ALTER TABLE stu MODIFY id INT;

-- stu表结构
DESC stu;

这时查询表结构可以看到Extra栏的主键自增约束标识已经没了：

• 如果此时再想添加主键自动增长约束：

-- 标准语法
ALTER TABLE 表名 MODIFY 列名 数据类型 AUTO_INCREMENT;

-- 添加主键自增约束
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

（3）唯一约束

唯一约束顾名思义，此列的值必须是唯一的，例如身份证号，每个人的身份证号必须是唯一的。

-- 唯一约束
CREATE TABLE stu2(
	id INT PRIMARY KEY AUTO_INCREMENT, -- id为 主键自增约束
	name VARCHAR(30),
	idNum VARCHAR(20) UNIQUE -- idNum为 唯一约束
);
DESC stu2;-- key栏显示UNI

显示如下：

可以看到Key栏idNum对应的是UNI，表示唯一约束。

-- 插入数据
INSERT INTO stu2 VALUES(NULL,'wzh','370305'),(NULL,'txa','370283');

此时如果插入idNum相同的数据会报错，且此时id是主键自增约束，插入失败也会占用一个id号：

INSERT INTO stu2 VALUES(NULL, 'h', '370283');

报错如下：

• 删除唯一约束

-- 标准语法
ALTER TABLE 表名 DROP INDEX 列名;

-- 删除唯一约束
ALTER TABLE stu2 DROP INDEX idNum;
DESC stu2;

删除后：

• 重新添加唯一约束：

-- 标准语法
ALTER TABLE 表名 MODIFY 列名 数据类型 UNIQUE;

-- 添加唯一约束
ALTER TABLE stu2 MODIFY idNum VARCHAR(20) UNIQUE;

（4）非空约束

-- 标准语法
CREATE TABLE 表名(
	列名 数据类型 NOT NULL,
    列名 数据类型,
    ...
);

-- 创建stu3表
CREATE TABLE stu3(
	id INT PRIMARY KEY auto_increment,
	name VARCHAR(20) NOT NULL, -- 给姓名列添加非空约束
	age INT
);
DESC stu3;

显示如下：

添加数据：

-- 添加数据
INSERT INTO stu3 VALUES(NULL, '王一',23),(NULL,'李二',24);
-- 添加非空约束列为NULL的值，会报错
INSERT INTO stu3 VALUES (NULL,NULL,26);

报错如下：

• 删除非空约束

-- 标准语法
ALTER TABLE 表名 MODIFY 列名 数据类型;

-- 删除非空约束
ALTER TABLE stu3 MODIFY NAME VARCHAR(20);

• 再次添加非空约束

-- 标准语法
ALTER TABLE 表名 MODIFY 列名 数据类型 NOT NULL;

-- 添加非空约束
ALTER TABLE stu3 MODIFY NAME VARCHAR(20) NOT NULL;

（5）外键约束

外键约束一般用于几张表之间的联系，保证数据准确。例如，某商城项目，用户表中有甲乙丙用户，其各自产生了很多个订单存放在订单表中，此时如果想删除甲用户，但是甲用户还有很多订单，所以不能删除，只有当甲用户的订单列表已经清空时才能删除甲用户。

• 首先创建用户信息表并插入数据：

-- 创建用户信息表
CREATE TABLE users(
	id INT PRIMARY KEY AUTO_INCREMENT, -- 主键自增约束
	name VARCHAR(20) NOT NULL -- 非空约束
);
-- 插入数据
INSERT INTO users VALUES(NULL, '甲'), (NULL, '乙'), (NULL, '丙');

• 然后创建订单表并插入几条订单数据，甲、乙、丙分别对应users表中的id为1、2、3。

-- 创建用户订单表
CREATE TABLE orderForm(
	id INT PRIMARY KEY AUTO_INCREMENT,
	number VARCHAR(20) NOT NULL, --  订单编号
	uid INT, --  外键列
	CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES users(id) -- ou_fk1命名意思为orderForm和users为foreign key（外键）
);

--  添加订单数据，给甲、乙各添加两个订单
INSERT INTO orderForm VALUES(NULL,'goods001',1),(NULL,'goods002',1),(NULL,'goods003',2),(NULL,'goods004',2);

• 此时如果想删除users表中的乙用户，因为乙用户还在orderForm中有订单，所以删除失败：

DELETE FROM users WHERE name='乙';

• 由于丙用户没有订单，所以可以正常删除
• 添加一个订单，但是用户不存在，则失败

INSERT INTO orderForm VALUES(NULL,'goods010',4);

• 删除外键约束

-- 标准语法
ALTER TABLE 表名 DROP FOREIGN KEY 外键名;

-- 删除外键
ALTER TABLE orderForm DROP FOREIGN KEY ou_fk1;

• 再次添加外键约束

-- 标准语法
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);

-- 添加外键约束
ALTER TABLE orderForm ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES users(id);

（6）外键级联更新和外键级联删除

• 级联删除
  当想更新users表中的用户的id时，将其对应的订单表orderForm中的用户id也更新。
• 级联更新
  当想删除users表中的用户时，将其对应的订单表orderForm中数据也删除。

由于级联更新删除操作会导致一连串数据被改变，所以一般不使用。

-- 添加外键约束，同时添加级联更新  标准语法
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) ON UPDATE CASCADE;

-- 添加外键约束，同时添加级联删除  标准语法
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) ON DELETE CASCADE;

-- 添加外键约束，同时添加级联更新和级联删除  标准语法
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) ON UPDATE CASCADE ON DELETE CASCADE;



-- 添加外键约束，同时添加级联更新和级联删除
ALTER TABLE orderForm ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE;

-- 将乙用户的原id 2修改为 5，订单表中乙的订单信息的uid也随之被修改
UPDATE users SET id=5 WHERE id=2;

-- 将乙用户删除，订单表中该用户所有订单也随之删除
DELETE FROM users WHERE id=5;