MySQL数据库如何添加和删除约束？如何查看约束名？-卡塔尔世界杯球场-1986年世界杯_世界杯年份

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

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

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

mysql> SHOW INDEX IN stu;

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| stu | 0 | PRIMARY | 1 | s_id | A | 0 | NULL | NULL | | BTREE | | |

| stu | 0 | s_un | 1 | s_id | A | 0 | NULL | NULL | | BTREE | | |

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

2 rows in set (0.00 sec)

mysql>

-- 表中 Key_name 的值（s_un）就是使用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);

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