在MySQL里,“约束”指的是对表中数据的一种限制约束,它能够确保数据库中数据的准确性和有效性
比如有的数据是必填项,就像身份认证的时候,或者填注册信息的时候,手机号身份证这种就不能空着,所以就有了非空约束;又有的数据比如用户的唯一id,不能跟其他人的一样,所以就需要使用唯一约束等等。
在MySQL中主要有6种约束:主键约束、外键约束、唯一约束、检查约束、非空约束和默认值约束。
一. 主键约束——PRIMARY KEY
在建立数据表的时候,一般情况下,为了方便更快地查找表中的记录,都会要求在表中设置一个“主键”。”主键“是表里面的一个特殊字段,这个字段能够唯一标识该表中的每条信息。
主键分为“单字段主键”和“多字段联合主键”,并且在使用主键的时候需要注意以下几个点:
- 一个表只能定义一个主键;
- 主键值必须唯一标识表中的每一行,并且不能出现
null
的情况,即表中不能存在有相同主键的两行或两行以上数据,严格遵守唯一性原则; - 一个字段名只能在联合主键字段表中出现一次;
- 联合主键不能包含不必要的多余字段,以满足最小化原则。
1.在建表时设置主键约束
设置单字段主键
<字段名> <数据类型> PRIMARY KEY [默认值]
例:在数据库中创建学生信息数据表st_info,主键为st_id, SQL语句以及运行结果如下:
create table st_info(
st_id int(10) primary key,
name varchar(20),
class varchar(10),
gender varchar(4),
age int(2)
);
mysql> desc st_info;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| st_id | int | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| class | varchar(10) | YES | | NULL | |
| gender | varchar(4) | YES | | NULL | |
| age | int | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
[CONSTRAINT <约束名>] PRIMARY KEY [字段名]
例 在数据库中创建学生信息数据表st_info2
,主键为st_id
, SQL语句以及运行结果如下:
create table st_info2(
st_id int(10),
name varchar(20),
class varchar(10),
gender varchar(4),
age int(2),
primary key(st_id)
);
mysql> desc st_info2;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| st_id | int | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| class | varchar(10) | YES | | NULL | |
| gender | varchar(4) | YES | | NULL | |
| age | int | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
设置联合主键
比如在学生信息表中,可以设置st_id跟name来作为联合主键,语法格式以及SQL语句实现为:
PRIMARY KEY [字段1,字段2,…,字段n]
create table st_info3(
st_id int(10),
name varchar(20),
class varchar(10),
gender varchar(4),
age int(2),
primary key(st_id,name)
);
mysql> desc st_info3;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| st_id | int | NO | PRI | NULL | |
| name | varchar(20) | NO | PRI | NULL | |
| class | varchar(10) | YES | | NULL | |
| gender | varchar(4) | YES | | NULL | |
| age | int | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
需要注意的是,在设置联合主键的时候,不能在每个字段名后面直接声明主键约束。
2. 在修改表的时候添加主键约束
如果在创建表的时候没有设置主键约束,还可以在修改表时进行添加,但是设置成主键约束的字段不允许有空值。语法格式:
ALTER TABLE <数据表名> ADD PRIMARY KEY(<字段名>);
mysql> desc st_info4;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| st_id | int | NO | | NULL | |
| name | varchar(20) | NO | | NULL | |
| class | varchar(10) | YES | | NULL | |
| gender | varchar(4) | YES | | NULL | |
| age | int | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
将st_id
与name
设置成主键:
mysql> alter table st_info4 add primary key(st_id,name);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc st_info4;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| st_id | int | NO | PRI | NULL | |
| name | varchar(20) | NO | PRI | NULL | |
| class | varchar(10) | YES | | NULL | |
| gender | varchar(4) | YES | | NULL | |
| age | int | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
3. 删除主键约束
mysql> alter table st_info4 drop primary key;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc st_info4;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| st_id | int | NO | | NULL | |
| name | varchar(20) | NO | | NULL | |
| class | varchar(10) | YES | | NULL | |
| gender | varchar(4) | YES | | NULL | |
| age | int | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
4. 【补充】 MySQL设置主键自增长 :
在MySQL里,当主键定义为自增长后,主键的值就不需要自己再输入数据了,而是由数据库系统根据定义自动赋值,每增加一条记录,主键就会自动根据设置的步长进行增长。
在MySQL中自增长的关键字是AUTO_INCREMENT
,语法格式为:
字段名 数据类型 AUTO_INCREMENT
例 创建学生信息表st_info5
,指定st_id
字段自增,
create table st_info5(
st_id int(10) primary key auto_increment,
name varchar(20) not null,
class varchar(10),
gender varchar(4),
age int(2)
);
insert into st_info5(name,class,gender,age)
values('王思瑶','管理学1班','女',18),
('王绮梦','管理学1班','女',18),
('李若彤','管理学1班','男',18);
mysql> select * from st_info5;
+-------+--------+-----------+--------+------+
| st_id | name | class | gender | age |
+-------+--------+-----------+--------+------+
| 1 | 王思瑶 | 管理学1班 | 女 | 18 |
| 2 | 王绮梦 | 管理学1班 | 女 | 18 |
| 3 | 李若彤 | 管理学1班 | 男 | 18 |
+-------+--------+-----------+--------+------+
3 rows in set (0.01 sec)
但是在设置学号的时候不一定是从1开始的,比如要从2000150001开始,则需要在建表之后设置自增的起始数据:
create table st_info6(
st_id int(10) primary key auto_increment,
name varchar(20) not null,
class varchar(10),
gender varchar(4),
age int(2)
)auto_increment=2000150001;
insert into st_info6(name,class,gender,age)
values('王思瑶','管理学1班','女',18),
('王绮梦','管理学1班','女',18),
('李若彤','管理学1班','男',18);
mysql> select * from st_info6;
+------------+--------+-----------+--------+------+
| st_id | name | class | gender | age |
+------------+--------+-----------+--------+------+
| 2000150001 | 王思瑶 | 管理学1班 | 女 | 18 |
| 2000150002 | 王绮梦 | 管理学1班 | 女 | 18 |
| 2000150003 | 李若彤 | 管理学1班 | 男 | 18 |
+------------+--------+-----------+--------+------+
3 rows in set (0.00 sec)
值得注意的地方是,如果在插入某一行数据产生了报错,则最终自增字段可能会出现不连续的情况。
二. 外键约束——FOREIGN KEY
外键约束是表的一个特殊字段,普遍会和主键约束一起使用,用来确保数据的一致性。
对于两个具有关联关系的表来说,相关联字段中主键所在的表就是主表(父表),外键所在的表就是从表(子表)。
所以外键就是用来建立主表与从表的关联关系,为两个表的数据建立连接,约束两个表中数据的一致性和完整性。
- 主表必须已经存在于数据库中,或者是当前正在创建的表。如果是后一种情况,则主表与从表是同一个表,这样的表称做自参照表,这种结构称做自参照完整性;
- 必须为主表定义主键;
- 主键不能包含空值,但允许在外键中出现空值;
- 在主表的表名后面指定列名或列名的组合,这个列或列的组合必须是主表的主键或候选键;
- 外键中列的数目必须和主表的主键中列的数目相同;
- 外键中列的数据类型必须和主表主键中对应列的数据类型相同。
1.在创建表时设置外键约束
在建表语句中,可以加入关键字FOREIGN KEY
来指定外键,用REFERENCES
来连接与主表的关系语法格式:
CONSTRAINT <约束名>
FOREIGN KEY <外键名>(字段名1,字段名2...)
REFERENCES <主表名>(主键字段名)
例:
mysql> desc st_info5;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| st_id | int | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| class | varchar(10) | YES | | NULL | |
| gender | varchar(4) | YES | | NULL | |
| age | int | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------+
② 创建tb_1
数据表,并在表上创建外键约束,使其中course_id
作为外键关联到表st_info5
的主键st_id
:
create table tb_1(
course_id int(8) not null auto_increment,
course_name varchar(25) not null,
constraint course_Choosing
foreign key fk_course(course_id)
references st_info5(st_id)
)auto_increment=20015001;
mysql> desc tb_1;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| course_id | int | NO | MUL | NULL | auto_increment |
| course_name | varchar(25) | NO | | NULL | |
+-------------+-------------+------+-----+---------+----------------+
上面语句执行成功之后,在表tb_1
中添加了course_Choosing
的约束名称,以及外键名称为fk_course
的course_id
字段,依赖于表st_info5
的主键st_id
。
2. 在修改表时添加外键约束
alter table tb_1
add
constraint course_Choosing
foreign key fk_course(course_id)
references st_info5(st_id);
这里需要注意的是,从表的外键关联的必须是主表的主键,且主键和外键的数据类型必须一致。例如两者都是int型或者都是char型数据。
3. 删除外键约束
当一个表中不需要外键约束时,就需要从表中将其删除。外键一旦删除,就会解除主表和从表间的关联关系。
删除的语法格式:
ALTER TABLE <表名>
DROP
FOREIGN KEY <外键约束名>;
例:
alter table tb_1
drop
foreign key fk_course;
三. 唯一约束——UNIQUE KEY
唯一约束就是指所有记录中字段的值不能重复出现,比如给’id’字段加上唯一约束之后,每条记录的id值都是唯一的,不能出现重复的情况。
唯一约束与主键约束有一个相似的地方,就是它们都能够确保列的唯一性。与主键约束不同的是,唯一约束在一个表中可以有多个,并且设置唯一约束的列是允许有空值的,虽然只能有一个空值。
例如,在用户信息表中,要避免表中的用户名重名,就可以把用户名列设置为唯一约束。
1.在创建表时设置唯一约束
唯一约束可以在创建表的时候直接进行设置,通常设置在除了主键以外的其他列上,语法格式如下:
<字段名> <数据类型> UNIQUE
例 创建图书馆所有书本明细表book_info ,设定书本bk_id为主键,书本ISBN为唯一键,SQL语句如下:
create table book_info(
bk_id int primary key,
ISBN varchar(17) unique,
author varchar(50),
Publisher varchar(20)
);
mysql> desc book_info;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| bk_id | int | NO | PRI | NULL | |
| ISBN | varchar(17) | YES | UNI | NULL | |
| author | varchar(50) | YES | | NULL | |
| Publisher | varchar(20) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
2. 在修改表时添加唯一约束
语法格式:
ALTER TABLE <数据表名>
ADD
CONSTRAINT <唯一约束名> UNIQUE(<列名>);
例 将book_info表中author字段设置为唯一约束:
alter table book_info
add
constraint uni_author unique(author);
mysql> desc book_info;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| bk_id | int | NO | PRI | NULL | |
| ISBN | varchar(17) | YES | UNI | NULL | |
| author | varchar(50) | YES | UNI | NULL | |
| Publisher | varchar(20) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
3. 删除唯一约束
语法格式:
ALTER TABLE <表名>
DROP INDEX <唯一约束名>;
例 删除book_info表中的唯一约束uni_author;
alter table book_info
drop index uni_author;
mysql> desc book_info;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| bk_id | int | NO | PRI | NULL | |
| ISBN | varchar(17) | YES | UNI | NULL | |
| author | varchar(50) | YES | | NULL | |
| Publisher | varchar(20) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
四. 检查约束——CHECK
例如,学生信息表中的年龄字段是不能有负数的,并且数值范围也是有限制的。如果是大学生,正常年龄一般应该在 18~30 岁之间。在设置字段的检查约束时需要根据实际情况进行设置,这样能够减少无效数据的输入。
1.在创建表时设置检查约束
语法格式:
CHECK(<约束条件>)
例 在persons表创建时id_p列创建check约束,约束条件为id_p列数据必须只包含大于0的整数:
create table persons(
id_p int not null,
lastname varchar(255) not null,
firstname varchar(255),
address varchar(255),
city varchar(255),
check (id_p>0)
);
mysql> desc persons;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id_p | int | NO | | NULL | |
| lastname | varchar(255) | NO | | NULL | |
| firstname | varchar(255) | YES | | NULL | |
| address | varchar(255) | YES | | NULL | |
| city | varchar(255) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
2. 在修改表时添加检查约束
语法格式:
ALTER TABLE <表名>
ADD
CONSTRAINT <检查约束名> CHECK(<检查约束>)
create table persons(
id_p int not null,
lastname varchar(255) not null,
firstname varchar(255),
address varchar(255),
city varchar(255),
constraint chk_person check (id_p>0 and city='sandnes')
);
alter table persons
add
constraint chk_ct check(city='上海');
mysql> desc persons;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id_p | int | NO | | NULL | |
| lastname | varchar(255) | NO | | NULL | |
| firstname | varchar(255) | YES | | NULL | |
| address | varchar(255) | YES | | NULL | |
| city | varchar(255) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
3. 删除检查约束
语法格式:
ALTER TABLE <表名>
DROP CONSTRAINT <检查约束名>;
alter table persons
drop constraint chk_ct;
五. 非空约束——NOT NULL
非空约束是用来约束表中的字段不能为空。在表中某个列的定义后加上关键字NOT NULL作为限定词,来约束该列的取值不能为空。
例如,在学生信息表中,如果不添加学生姓名,那么这条记录是无效的,这种情况下就可以为用户名字段设置非空约束。
1.在创建表时设置非空约束
语法格式:
CREATE TABLE <表名>(
...
<字段名> <数据类型> NOT NULL)
...
);
例 创建学生信息表st_info, 指定st_id为主键,学生姓名不能为空:
create table st_info(
st_id int(10) primary key,
name varchar(20) not null,
class varchar(10),
gender varchar(4),
age int(2)
);
mysql> desc st_info;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| st_id | int | NO | PRI | NULL | |
| name | varchar(20) | NO | | NULL | |
| class | varchar(10) | YES | | NULL | |
| gender | varchar(4) | YES | | NULL | |
| age | int | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
2. 在修改表时添加非空约束:
如果在创建表的时候忘记了为字段设置非空约束,可以通过修改表进行非空约束的添加。
语法格式:
ALTER TABLE <表名>
CHANGE COLUMN <字段名>
<字段名> <数据类型> NOT NULL;
例 修改学生信息表st_info中class字段,设置成非空约束:
mysql> alter table st_info
-> change column class
-> class varchar(20) not null;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc st_info;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| st_id | int | NO | PRI | NULL | |
| name | varchar(20) | NO | | NULL | |
| class | varchar(20) | NO | | NULL | |
| gender | varchar(4) | YES | | NULL | |
| age | int | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
3. 删除非空约束
可以在修改语句中对非空约束进行删除。
语法格式:
ALTER TABLE <数据表名>
CHANGE COLUMN <字段名>
<字段名> <数据类型> NULL;
例 修改学生信息表st_info,将class字段的非空约束删除:
mysql> alter table st_info
-> change column class
-> class varchar(20) null;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc st_info;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| st_id | int | NO | PRI | NULL | |
| name | varchar(20) | NO | | NULL | |
| class | varchar(20) | YES | | NULL | |
| gender | varchar(4) | YES | | NULL | |
| age | int | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
六. 默认值约束——DEFAULT
默认值约束是当数据表中某个字段不输入值的时候,自动为其添加一个已经设置好的值。
例如,在注册学生信息时,如果不输入学生的性别,那么会默认设置一个性别或者输入一个“未知”。
默认值约束一般是用在已经设置了非空约束的列,这样能够防止数据表在录入数据时出现错误。
1.在创建表时设置默认值约束
语法格式:
CREATE TABLE <表名>(
...
<字段名> <数据类型> DEFAULT <默认值>
...
);
例 创建学生信息表st_info,设置年龄age默认值为18:
create table st_info(
st_id int(10) primary key,
name varchar(20) not null,
class varchar(10),
gender varchar(4),
age int(2) default 18
);
mysql> desc st_info;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| st_id | int | NO | PRI | NULL | |
| name | varchar(20) | NO | | NULL | |
| class | varchar(10) | YES | | NULL | |
| gender | varchar(4) | YES | | NULL | |
| age | int | YES | | 18 | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
2. 在修改表时添加默认值约束
语法格式:
ALTER TABLE <数据表名>
CHANGE COLUMN <字段名>
<字段名> <数据类型> DEFAULT <默认值>;
例 修改学生信息表st_info,将性别设置默认值为“保密”:
mysql> alter table st_info
-> change column gender
-> gender varchar(4) default '保密';
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc st_info;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| st_id | int | NO | PRI | NULL | |
| name | varchar(20) | NO | | NULL | |
| class | varchar(10) | YES | | NULL | |
| gender | varchar(4) | YES | | 保密 | |
| age | int | YES | | 18 | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
需要注意的是,这里的gender字段为字符串型数据,需要加上引号。
3. 删除默认值
删除默认值即为将默认值设置为空(null)
语法格式:
ALTER TABLE <数据表名>
CHANGE COLUMN <字段名>
<字段名> <数据类型> DEFAULT NULL;
例 修改学生信息表st_info, 将gender性别字段的默认值删除掉:
mysql> alter table st_info
-> change column gender
-> gender varchar(4) default null;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc st_info;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| st_id | int | NO | PRI | NULL | |
| name | varchar(20) | NO | | NULL | |
| class | varchar(10) | YES | | NULL | |
| gender | varchar(4) | YES | | NULL | |
| age | int | YES | | 18 | |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
以上6个约束都很重要,并且有的可以联合使用,并不是都是单独使用的。作为基础知识点,会让你的数据库更加规范,从而提高工作效率。
原文地址:https://blog.csdn.net/qq_57570052/article/details/132153848
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。
如若转载,请注明出处:http://www.7code.cn/show_44378.html
如若内容造成侵权/违法违规/事实不符,请联系代码007邮箱:suwngjj01@126.com进行投诉反馈,一经查实,立即删除!