mysql基础_约束
介绍
约束对应的英语单词:constraint,在创建表的时候,我们可以给表中的字段加上一些约束,来保证这个表中数据的完整性、有效性。
约束的作用就是为了保证:表中的数据有效。
类型
非空约束:not null
唯一性约束:unique
主键约束:primary key (简称PK)
外键约束:foreign key(简称FK)
检查约束:check(mysql不支持,oracle支持)
主要介绍
介绍重点的几个约束
非空约束
受到非空约束的字段不能为null.
例如:
drop table if exists user;
create table user(
id int,
name varchar(255) not null
);
insert into user(id,name) values(1,'zhangsan');
insert into user(id,name) values(2,'lisi');
insert into user(id) values(3); //这一行name没有值,设计表的时候也没有默认值,会报下面的错
ERROR 1364 (HY000): Field 'name' doesn't have a default value
唯一性约束
唯一性约束unique约束的字段不能重复,但是可以为NULL。
列级约束
drop table if exists user;
create table user(
id int,
name varchar(255) unique,// 约束直接添加到列后面的,叫做列级约束
email varchar(255)
);
insert into user(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into user(id,name,email) values(2,'lisi','lisi@123.com');
insert into user(id,name,email) values(3,'wangwu','wangwu@123.com');
insert into user(id,name,email) values(4,'wangwu','wangwu@sina.com');//插入这行数据时,会报下面的错误,因为name字段已经设置为唯一。
ERROR 1062 (23000): Duplicate entry 'wangwu' for key 'name'
insert into user(id) values(4);
执行上面的脚本后,表结构如下:
id | name | |
---|---|---|
1 | zhangsan | zhangsan@123.com |
2 | lisi | lisi@123.com |
3 | wangwu | wangwu@123.com |
4 | NULL | NULL |
name字段虽然被unique约束了,但是可以为NULL。
表级约束
如果需要两个或者多个字段联合起来具有唯一性,就需要用到表级唯一约束。
例如:
drop table if exists user;
create table user(
id int,
name varchar(255),
email varchar(255),
unique(name,email) // 约束没有添加在列的后面,这种约束被称为表级约束。
);
insert into user(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into user(id,name,email) values(2,'zhangsan','zhangsan@sina.com');
insert into user(id,name,email) values(3,'zhangsan','zhangsan@sina.com');
ERROR 1062 (23000): Duplicate entry 'zhangsan-zhangsan@sina.com' for key 'name'
unique 和not null联合
drop table if exists user;
create table user(
id int,
name varchar(255) not null unique
);
表结构如下:
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
id | int(11) | YES | NULL | ||
name | varchar(255) | NO | PRI | NULL |
在mysql当中,如果一个字段同时被not null和unique约束的话,该字段自动变成主键字段(下面就讲)。
插入数据时:
insert into user(id,name) values(1,'zhangsan');
insert into user(id,name) values(2,'zhangsan'); //error:name不能重复
insert into user(id) values(2); //error:name不能为NULL
主键约束
主键值是每一行记录的唯一标识,类似身份证号码。任何一张表都应该有主键,没有主键,表无效
主键的特征:not null + unique(前面提到过)
单个字段添加主键
1.列级约束
drop table if exists user;
// 1个字段做主键,叫做:单一主键
create table user(
id int primary key, //列级约束
name varchar(255)
);
insert into user(id,name) values(1,'zhangsan');
insert into user(id,name) values(2,'lisi');
insert into user(id,name) values(2,'wangwu');//error:不能重复
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
insert into user(name) values('zhaoliu');//error:不能为NULL
ERROR 1364 (HY000): Field 'id' doesn't have a default value
2.表级约束
drop table if exists user;
create table user(
id int,
name varchar(255),
primary key(id) // 表级约束
);
insert into user(id,name) values(1,'zhangsan');
insert into user(id,name) values(1,'lisi');//error:不能重复
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
多个字段联合添加主键
id和name联合起来做主键:复合主键
drop table if exists user;
create table user(
id int,
name varchar(255),
email varchar(255),
primary key(id,name)
);
insert into user(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into user(id,name,email) values(1,'lisi','lisi@123.com');
insert into user(id,name,email) values(1,'lisi','lisi@123.com');//error:不能重复
ERROR 1062 (23000): Duplicate entry '1-lisi' for key 'PRIMARY'
在实际开发中不建议使用:复合主键。建议使用单一主键!因为主键值存在的意义就是这行记录的身份证号,只要意义达到即可,单一主键可以做到。复合主键比较复杂,不建议使用。
注意事项
1.不能添加两个主键
一张表中主键只能有一个,不能有两个,不然就会像下面的例子那样报错。
drop table if exists user;
create table user(
id int primary key,
name varchar(255) primary key
);
ERROR 1068 (42000): Multiple primary key defined
2.主键值建议使用:int,bigint,char等类型。不建议使用:varchar来做主键。主键值一般都是数字,
一般都是定长的!
3.分类
主键除了单一主键和复合主键之外,还可以分为自然主键和业务主键。
自然主键:主键值是一个自然数,和业务没关系。
业务主键:主键值和业务紧密关联,例如拿银行卡账号做主键值。这就是业务主键!
在实际开发中自然主键使用比较多,因为主键只要做到不重复就行,不需要有意义。业务主键不好,因
为主键一旦和业务挂钩,那么当业务发生变动的时候,可能会影响到主键值,所以业务主键不建议使
用。尽量使用自然主键。
4.主键自增(auto_increment)
drop table if exists user;
create table user(
id int primary key auto_increment, //auto_increment表示自增,从1开始,以1递增
name varchar(255)
);
insert into user(name) values('zhangsan');
insert into user(name) values('zhangsan');
insert into user(name) values('zhangsan');
insert into user(name) values('zhangsan');
insert into user(name) values('zhangsan');
id | name |
---|---|
1 | zhangsan |
2 | zhangsan |
3 | zhangsan |
4 | zhangsan |
5 | zhangsan |
外键约束
在MySQL中,外键约束用于确保两个表之间的数据一致性。外键约束是一种限制,它将一个表中的列与另一个表中的列相关联。具体来说,它要求在一个表中的某个列中的值必须在另一个表的某个列中存在。外键约束可以确保数据的完整性和一致性,防止数据被删除或修改时发生错误。
创建外键约束
1.创建主表及从表
CREATE TABLE user (
user_id INT PRIMARY KEY,
user_name VARCHAR(50),
user_email VARCHAR(50)
);
CREATE TABLE room (
room_id INT PRIMARY KEY,
room_date DATE,
user_id INT,
);
2.创建约束
ALTER TABLE room
ADD CONSTRAINT fk_room_user
FOREIGN KEY (user_id) REFERENCES user(user_id);
fk_room_user是外键约束的名称,FOREIGN KEY子句用于指定要添加外键约束的列,REFERENCES子句用于指定关联表和列。
使用外键约束
插入数据:当向"orders"表中插入数据时,如果在"user_id"列中插入一个不存在于"user"表中的值,则会引发外键约束错误。
INSERT INTO room (room_id, room_date, user_id)
VALUES (1, '2023-05-11', 10);
-- Error: Cannot add or update a child row: a foreign key constraint fails
更新数据:当更新"user"表中的"user_id"列中的值时,如果在"room"表中存在与该值匹配的"user_id"值,则会引发外键约束错误。
UPDATE user SET user_id = 2 WHERE user_id = 1;
-- Error: Cannot delete or update a parent row: a foreign key constraint fails