mysql 约束名_MySQL - 列的完整性约束

最新推荐文章于 2022-08-13 09:18:23 发布

悟无误

最新推荐文章于 2022-08-13 09:18:23 发布

阅读量434

点赞数

文章标签： mysql 约束名

本文链接：https://blog.csdn.net/weixin_29305313/article/details/113167641

版权

完整性约束条件是对字段进行限制，要求用户对该属性进行的操作符合特定的要求。如果不满足完整性约束条件，数据库系统将不再执行用户的操作。MySQL中基本的完整性约束条件如下表所示。下表是完整性约束条件:

1. 设置表字段的主键约束(PRIMARY KEY，PK)

主键是表的一个特殊字段，能唯一标识该表中的每条信息。主键和记录的关系，如同身份证和人的关系。主键用来标识每个记录，每个记录的主键值都不同。身份证用来表明人的身份，每个人都具有唯一的身份证号。设置表的主键是指在创建表时设置表的某个字段为该表的主键。

主键的主要目的是帮助数据库管理系统以最快的速度查找到表的某一条信息。主键必须满足的条件就是主键必须是唯一的，表中任意两条记录的主键字段的值不能相同，并且是非空值。主键可以是单一的字段，也可以是多个字段的组合。

1.1单字段主键

单字段主键的语法规则如下：

CREATE TABLE tablename(propName propType PRIMARY KEY, ......);

或CREATE TABLE tablename(propName propType, ...... ,PRIMARY KEY(propName));

创建表school，设置id字段为PK约束，再查看class表信息，SQL语句如下

1 mysql> create databaseschool;　　　　　　　　　　　　　　　　　　　　　　　　#创建数据库school2 mysql> useschool;　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　#选择数据库school3 mysql> create table class(id int PRIMARY KEY, name varchar(128), teacher varchar(64)); #创建表class4 mysql> descclass;　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　#查询表class 的定义, describe class 效果等同5 mysql> show create tableclass;　　　　　　　　　　　　　　　　　　　　　　　#同样可以实现查询表class 的定义6 mysql> insert into class VALUES(1,'测试文字一','test1');　　　　　　　　　#插入成功7 mysql> insert into class VALUES(1,'测试文字二','test2');　　　　　　　　　#因主键约束，插入失败

Mysql 支持给主键设置名字：

1 create table tablename(propName propType , ...... CONSTRAINTpk_name PRIMARY KEY(propName));

给id字段的PK约束设置一个名字，可以执行SQL语句CONSTRAINT。如下

1 mysql> create databaseschool;　　　　　　　　　　　　　　　　　　　　　　　　　#创建数据库school2 mysql> useschool;　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　#选择数据库school3 mysql> create table class(id int , name varchar(128), teacher varchar(64), CONSTRAINTid_pk PRIMARY KEY(id)); #创建表class4 mysql> descclass ;　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　#查询表class 的定义, describe class 效果等同5 mysql> show create tableclass ;　　　　　　　　　　　　　　　　　　　　　　　　#同样可以实现查询表class 的定义6 mysql> insert into class VALUES(1,'测试文字一','test1');　　　　　　　　　　　#插入成功7 mysql> insert into class VALUES(1,'测试文字二','test2');　　　　　　　　　　　#因主键约束，插入失败

1.2 多字段主键

主键是由多个属性组合而成时，在属性定义完之后统一设置主键。语法规则如下：

1 CREATE TABLEtablename2 (3 propName1 propType ,4 propName2 propType ,5 ......6 [CONSTRAINT pk_name]PRIMARY KEY(propName1, propName2)7 );

如下：

1 mysql> create databaseschool;　　　　　　　　　　　　　　　　　　　　　　　　　#创建数据库school2 mysql> useschool;　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　#选择数据库school3 mysql> create table class3(id int, name varchar(128), teacher varchar(64), CONSTRAINTid_pk PRIMARY KEY(id,name));　　#创建表class3,设置联合主键4 mysql> descclass3;　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　#查询表class3 的定义, describe class3 效果等同5 mysql> insert into class VALUES(1,'测试文字','test1');　　　　　　　　　　　#插入成功6 mysql> insert into class VALUES(1,'测试文字','test2');　　　　　　　　　　　#因联合主键约束，插入失败

2. 设置表字段的外键约束(FOREIGN KEY，FK)

外键是表的一个特殊字段，外键约束是为了保证多个表(通常为两个表)之间的参照完整性，即构建两个表的字段之间的参照关系。

设置外键约束的两个表之间具有父子关系，即子表中某个字段的取值范围由父表决定。例如，表示一个班级和学生关系，即每个班级有多个学生。首先应该有两个表：班级表和学生表，然后学生表有一个表示班级编号的字段no，其依赖于班级表的主键，这样字段no就是学生表的外键，通过该字段班级表和学生表建立了关系。

一个班级表，班级表主键 ID

名称

班主任

101

计算机科学技术一班

马老师

102

计算机科学技术二班

潘老师

一个学生表，每一行对应着一个班级，而一个班级对应着多个学生，所以班级表和学生表的关系就是“一对多” ，班级表为学生表的外键

姓名

班级ID

性别

张三

101

李四

102

王五

101

在具体设置FK约束时，设置FK约束的字段必须依赖于数据库中已经存在的父表的主键，同时外键可以为空(NULL)。

设置表中某字段的FK约束非常简单，可以在MySQL数据库管理系统中通过SQL语句FOREIGN KEY和REFERENCES来实现，其语法形式如下：

1 CREATE TABLEtablename_22 (3 propName1 propType ,4 propName2 propType ,5 ......6 [CONSTRAINT fk_name]FOREIGN KEY(propName1) REFERENCEStablename_1(table1_column)7 );

其中, tablename_2参数是要设置外键的表名，propName1参数是要设置外键的字段，tablename_1是父表的名称，table1_column 是父表中设置主键约束的字段名。

方式如下：

1 mysql> useschool;　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　#选择数据库school2 mysql> create table class(id int PRIMARY KEY, name varchar(128), teacher varchar(64)); #创建表class3 mysql> descclass;　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　#查询表class 的定义, describe class 效果等同4 mysql> create table student (id int PRIMARY KEY AUTO_INCREMENT, name varchar(64) NOT NULL, class_id int, sex enum('M','F'), FOREIGN KEY(class_id) REFERENCESclass(id)); #创建表student, class_id 为表 class 的 id 字段的外键5 mysql> insert into student(name, class_id, sex) values('张三', 101, 'M');　　　 #插入记录，主键自增长6 mysql> insert into student(name, sex) values('李四', 'F');　　　　　　　　　　　　 #插入记录，允许外键为空

3. 设置表字段的非空约束(NOT NULL, NK)

当数据库表中的某个字段上的内容不希望设置为NULL时，可以使用NK约束进行设置。NK约束在创建数据库表时为某些字段上加上“NOT NULL”约束条件，保证所有记录中的该字段都有值。如果在用户插入的记录中该字段为空值，那么数据库管理系统会报错。

设置表中某字段的NK约束非常简单，查看帮助文档可以发现，在MySQL数据库管理系统中是通过SQL语句NOT NULL来实现的，其语法形式如下：

1 CREATE TABLEtablename2 (3 propName propType NOT NULL, ......4 );

方式如下：

1 mysql> create databaseschool;　　　　　　 #创建数据库school2 mysql> useschool;　　　　　　　　　　　　　　#选择数据库school3 mysql> create table class(id int, name varchar(128) NOT NULL, teacher varchar(64));　　#创建表class 并且修饰其 name 不可为空4 mysql> descclass;　　　　　　　　　　　　　　#查询表class 的定义, describe class 效果等同5 mysql> show create table class;　　　　　　#同样可以实现查询表class 的定义

检验数据库school中的class表中字段classno是否被设置为NK约束，执行SQL语句 describe 或 desc 语句，具体如下:

4. 设置表字段唯一约束(UNIQUE，UK)

当数据库表中某个字段上的内容不允许重复时，可以使用UK约束进行设置。UK约束在创建数据库时为某些字段加上“UNIQUE”约束条件，保证所有记录中该字段上的值不重复。如果在用户插入的记录中该字段上的值与其他记录中该字段上的值重复，那么数据库管理系统会报错。但 unique 约束允许受约束的字段出现多个空值 NULL, 多个NULL不算重复。

设置表中某字段的UK约束非常简单，可以在MySQL数据库管理系统中通过SQL语句UNIQUE来实现，其语法形式如下：

1 CREATE TABLEtablename2 (3 propName propType UNIQUE, ......4 );

方式如下：

1 mysql> create databaseschool;　　　　　　　　　　　　 #创建数据库school2 mysql> useschool;　　　　　　　　　　　　　　　　　　　 #选择数据库school3 mysql> create table class(id int, name varchar(128) UNIQUE, teacher varchar(64)); #创建表class，name 不可重复4 mysql> descclass;　　　　　　　　　　　　　　　　　　　 #查询表class 的定义, describe class 效果等同5 mysql> show create table class;　　　　　　　　　　　　#同样可以实现查询表class 的定义

5. 设置表字段值自动增加(AUTO_INCREMENT)

AUTO_INCREMENT是 MySQL 唯一扩展的完整性约束，当向数据库表中插入新记录时，字段上的值会自动生成唯一的ID。在具体设置AUTO_INCREMENT约束时，一个数据库表中只能有一个字段使用该约束，该字段的数据类型必须是整数类型。由于设置AUTO_INCREMENT约束后的字段会生成唯一的ID，因此该字段也经常会同时设置成PK主键。

设置表中某字段值的自动增加约束非常简单，可以在MySQL数据库管理系统中通过SQL语句AUTO_INCREMENT来实现，其语法形式如下：

1 CREATE TABLEtablename2 (3 propName propType AUTO_INCREMENT, ......4 );

在上述语句中，tablename参数表示所要设置非空约束的字段名字，propName参数为属性名，propType为属性类型，propName字段要设置自动增加约束。默认情况下，字段propName的值从1开始增加，每增加一条记录，记录中该字段的值就会在前一条记录(或已存在的最大值(包括曾经存在的))的基础上加1。

方式如下：

注意:mysql8中, AUTO_INCREMENT 必须设为键 (主键、外键和唯一键均可)！否则会报错！

6. 设置表字段的默认值(DEFAULT)

当为数据库表中插入一条新记录时，如果没有为某个字段赋值，数据库系统就会自动为这个字段插入默认值。为了达到这种效果，可通过SQL语句关键字 DEFAULT来设置。

设置数据库表中某字段的默认值非常简单，可以在MySQL数据库管理系统中通过SQL语句 DEFAULT来实现，其语法形式如下：

1 CREATE TABLEtablename2 (3 propName propType DEFAULTdefaultvalue, ......　　　　　#defaultvalue 为默认的值，如果没有设置则默认为空4 );

7. 调整列的完整性约束

7.1 新增主键PK、外键 FK 和唯一键 UK

alter table [table_name] add constraint [constraint_name] [unique key| primary key|foreign key] ([column_name])

7.2 查询约束名和删除主键、外键或唯一

1. 通过如下命令查询键值的约束名:

show index 或 keys from 表名;

2. 执行如下命令删除:

主键：　　　　 alter table 表名 drop primary key;

外键或唯一键: 　　 alter table 表名 drop index 约束名;

7.3 修改主键PK、外键FK 和唯一键UK约束名

按照上述步骤先执行删除然后再新增即可！

7.4 修改默认值 DEFAULT、自增长和非空NK

使用如下语法重新定义列即可：

alter table 表名 modify 列名类定义;

1 mysql> create databaseschool;　　　　　　　　　　#创建数据库school2 mysql> useschool;　　　　　　　　　　　　　　　　 #选择数据库school3 mysql> create table class(id int PRIMARY KEY AUTO_INCREMENT, name varchar(128) UNIQUE, teacher varchar(64)); #创建表class,设置字段id为自增长4 mysql> alter table class modifyteacher varchar(64) DEFAULT ‘Test’ NOT NULL;　　　　　　　　　　　　　　　　　　　　　　　 #设置默认值和非空5 mysql> alter table class modifyteacher varchar(64);　　　　　　　　　　　#取消默认值和非空

===================================================================================================================