MySQL数据库如何添加和删除约束？如何查看约束名？

#MySQL数据库如何添加和删除约束？如何查看约束名？| 来源: 网络整理| 查看: 265

MySQL中有6种常见的约束：主键约束（primary key）、外键约束（foreign key）、非空约束（not null）、唯一性约束（unique）、默认值约束（default）以及自增约束（auto_increment），以下是添加和删除这几种约束的一些方法。

ALTER语句添加外键约束时 CONSTRAINT [约束名] 可以省略，如果没有用CONSTRAINT设置约束名，系统会自动生成一个约束名

我们可以使用 SHOW INDEX IN 表名；来查看使用CONSTRAINT设置的【约束名】

也可使用 SHOW CREATE TABLE 表名来查看系统自动生成的约束名

mysql> show create table stu; +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | stu | CREATE TABLE `stu` ( `s_id` int(10) NOT NULL, `name` varchar(20) DEFAULT NULL, `sex` char(2) DEFAULT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`s_id`), UNIQUE KEY `s_id` (`s_id`), CONSTRAINT `stu_ibfk_1` FOREIGN KEY (`s_id`) REFERENCES `tea` (`t_id`), CONSTRAINT `stu_ibfk_2` FOREIGN KEY (`s_id`) REFERENCES `tea` (`t_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.06 sec) mysql> -- 表中CONSTRAINT后面（stu_ibfk_1、stu_ibfk_2）的就是系统自动生成的外键约束名 1. 主键约束（primary key）

------添加主键约束

建表时直接添加 CREATE TABLE stu ( -- PRIMARY KEY s_id INT(10) PRIMARY KEY ... ); -- 或 CREATE TABLE stu ( s_id INT(10), CONSTRAINT pk_id PRIMARY KEY ( s_id ) ... ); -- CONSTRAINT [约束名] 可以省略，如果没有用 CONSTRAINT 设置约束名系统会自动生成一个约束名. 通过ALTER语句添加 / 修改 -- 语法：ALTER TABLE 表名 ADD [CONSTRAINT [约束名]] PRIMARY KEY (字段名); -- CONSTRAINT [约束名]可以省略 ALTER TABLE stu ADD CONSTRAINT pk_id PRIMARY KEY ( s_id ); -- 通过修改列定义来添加主键，修改约束一般是先删掉原有的后重新添加. -- 语法：ALTER TABLE 表名 MODIFY 字段名数据类型 PRIMARY KEY; ALTER TABLE stu MODIFY s_id INT(10) PRIMARY KEY;

主键约束相当于(唯一约束+非空约束) 一张表中最多有一个主键约束,如果设置多个主键,就会出现如下提示： Multiple primary key defined

------删除主键约束

-- 语法：ALTER TABLE 表名 DROP PRIMARY KEY； ALTER TABLE stu DROP PRIMARY KEY; -- 若出现以下错误：> 1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key -- 说明此列是自动增长列，列的属性带有AUTO_INCREMENT，无法直接删除 -- 那么要先将这个列的自动增长属性删除，才可以删除主键（间接删除） ALTER TABLE stu MODIFY s_id INT(10); ALTER TABLE stu DROP PRIMARY KEY; 2. 外键约束（foreign key）

------添加外键约束

创建从表时直接添加 CREATE TABLE stu ( s_id INT(10), CONSTRAINT pk_id PRIMARY KEY ( s_id ) CONSTRAINT fk_stu_id FOREIGN KEY(s_id) REFERENCES tea(t_id)#添加外键约束 ... ); -- CONSTRAINT [约束名] 可以省略，如果没有用 CONSTRAINT 设置约束名系统会自动生成一个约束名. 通过ALTER语句给从表添加 / 修改 -- 语法：ALTER TABLE 从表名 ADD [CONSTRAINT [约束名]] FOREIGN KEY (外键列名) references 主表名 (主键列名); ALTER TABLE stu ADD FOREIGN KEY(s_id) REFERENCES tea(t_id); -- 同理，CONSTRAINT [约束名] 可以省略.

------删除外键约束

-- 语法：ALTER TABLE 从表名 DROP FOREIGN KEY 约束名; ALTER TABLE stu DROP FOREIGN KEY fk_stu_id; -- 约束名可通过语法： SHOW INDEX IN 表名 ; 来查看 -- 系统自动生成的约束名通过语法：SHOW CREATE TABLE 表名 ; 来查看 3. 非空约束（not null）

------添加非空约束

建表时直接添加 CREATE TABLE stu ( -- NOT NULL s_id INT ( 10 ) NOT NULL, name VARCHAR ( 20 ) NOT NULL ); 通过ALTER语句添加 / 修改 -- 语法：ALTER TABLE 表名 MODIFY 字段名数据类型 NOT NULL; ALTER TABLE stu MODIFY s_id INT(10) NOT NULL; -- 语法：ALTER TABLE 表名 CHANGE 旧字段名新字段名数据类型 NOT NULL; ALTER TABLE stu CHANGE s_id s_id INT(10) NOT NULL; -- 当不需要重命名字段时，写两个相同的字段名是必要的.

------删除非空约束

-- 语法：ALTER TABLE 表名 MODIFY 字段数据类型; ALTER TABLE stu MODIFY id INT(10); -- 语法：ALTER TABLE 表名 CHANGE 旧字段名新字段名数据类型; ALTER TABLE stu CHANGE id id INT(10); -- 即直接修改字段，不加 NOT NULL 即可. 4. 唯一性约束（unique）

------添加唯一性约束

建表时直接添加 CREATE TABLE stu( -- UNIQUE s_id INT(10) UNIQUE ... ); -- 或 CREATE TABLE stu( s_id INT(10), s_name VARCHAR(20), CONSTRAINT s_un UNIQUE(s_id,s_name)#复合约束 ); -- 或 CREATE TABLE stu( s_id INT(10), s_name VARCHAR(20), UNIQUE KEY(s_id,s_name) ); 通过ALTER语句添加 / 修改 -- 语法：ALTER TABLE 表名 ADD [CONSTRAINT [约束名]] UNIQUE [INDEX|KEY] (字段名); ALTER TABLE stu ADD CONSTRAINT s_un UNIQUE (s_id); -- 如果没有用 CONSTRAINT 设置约束名系统会自动生成一个约束名，UNIQUE约束的约束名默认是字段名. -- 同理也可用MODIFY COLUMN及CHANGE COLUMN方法添加： -- 语法：ALTER TABLE 表名 MODIFY 字段名数据类型 UNIQUE; ALTER TABLE stu MODIFY s_id INT(10) UNIQUE; -- 语法：ALTER TABLE 表名 CHANGE 旧字段名新字段名数据类型 UNIQUE; ALTER TABLE stu CHANGE s_id s_id INT(10) UNIQUE;

------删除唯一性约束

-- 语法：ALTER TABLE 表名 DROP [INDEX | KEY] 约束名 ; ALTER TABLE DROP INDEX s_un; -- 约束名可通过语法： SHOW INDEX IN 表名 ; 来查看（UNIQUE约束名默认是字段名） 5. 默认值约束（default)

------添加默认值约束

建表时直接添加 CREATE TABLE stu( -- DEFAULT s_id INT(10) DEFAULT 3 ... ); 通过ALTER语句添加 / 修改 -- 语法：ALTER TABLE 表名 MODIFY 字段名数据类型 DEFAULT 默认值; ALTER TABLE stu MODIFY s_id INT(10) DEFAULT 3; -- 语法：ALTER TABLE 表名 CHANGE 字段名字段名数据类型 DEFAULT 默认值; ALTER TABLE stu CHANGE s_id s_id INT(10) DEFAULT 3;

------删除默认值约束

-- ALTER TABLE 表名 MODIFY | CHANGE 字段名 [字段名] 数据类型 DEFAULT NULL; ALTER TABLE stu MODIFY s_id INT(10) DEFAULT NULL; ALTER TABLE stu CHANGE s_id s_id INT(10) DEFAULT NULL; -- ALTER TABLE 表名 MODIFY | CHANGE 字段名 [字段名] 数据类型; ALTER TABLE stu MODIFY s_id INT(10); ALTER TABLE stu CHANGE s_id s_id INT(10); 6. 自增约束（auto_increment)

------添加自增约束

建表时直接添加 CREATE TABLE stu( -- 其他约束 AUTO_INCREMENT s_id INT(10) PRIMARY KEY AUTO_INCREMENT, ... ); 通过ALTER语句添加 / 修改 -- ALTER TABLE 表名 MODIFY | CHANGE 字段名 [字段名] 数据类型 AUTO_INCREMENT; ALTER TABLE stu MODIFY s_id INT(10) AUTO_INCREMENT; ALTER TABLE stu CHANGE s_id s_id INT(10) AUTO_INCREMENT;

------删除自增约束

-- ALTER TABLE 表名 MODIFY | CHANGE 字段名 [字段名] 数据类型 ; ALTER TABLE stu MODIFY s_id INT(10); ALTER TABLE stu CHANGE s_id s_id INT(10);

一张表只能有一个自增长列，并且该列必须定义了约束(可以是主键约束,也可以是唯一约束,也可以是外键约束,但是不可以是非空和检查约束) 自增长一般配合主键使用，并且只能在数字类型中使用

【本文地址】

公司简介

联系我们