MySQL 之 约束 （主键、唯一、非空、自增、外键）

最新推荐文章于 2023-03-18 19:34:05 发布
最新推荐文章于 2023-03-18 19:34:05 发布
阅读量8.6k 收藏 58
点赞数 6
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_43688472/article/details/105976118
版权

MySQL 之 约束 （主键、唯一、非空、自增、外键）

目的：使得数据更准确，更完整。
约束的分类：
1、键约束
（1）主键约束
（2）唯一键约束
（3）外键约束
2、非空约束
3、默认值约束
4、自增约束

主键约束

（一）概述
1、关键字 ：primary key
2、特点：增加主键约束的列（字段）的值必须是非空 + 唯一的，一个表只有一个主键约束
3、作用：保证表中不会出现两条无法区分的记录
4、要求：每一张表都必须有主键约束
5、分类
单列主键约束
复合主键约束
（二）使用主键约束

1、创建主键约束
（1）在建表时指定主键约束
create table 【数据库名.】表名称(
	字段1 数据类型 primary key,
	字段2 数据类型,
	....
);

create table 【数据库名.】表名称(
	字段1 数据类型,
	字段2 数据类型,
	....,
	primary key(字段1)
);

例如：
create table dept(
	id int primary key,
	name varchar(20),
	description varchar(100)
);
或
create table dept(
	id int ,
	name varchar(20),
	description varchar(100),
	primary key(id)
);
mysql> desc dept;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| id          | int(11)      | NO   | PRI | NULL    |       |
| name        | varchar(20)  | YES  |     | NULL    |       |
| description | varchar(100) | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+

insert into dept values(1,'财务部','发钱的');
insert into dept values(1,'财务部','发钱的');

mysql> insert into dept values(1,'财务部','发钱的');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

（2）建表后指定主键约束
create table dept(
	id int,
	name varchar(20),
	description varchar(100)
);
修改表结构：
alter table dept add primary key(id);

2、删除主键约束
修改表结构
alter table 表名称 drop primary key;

例如：
alter table dept primary key;

3、复合主键
（1）在建表时指定主键约束
create table 【数据库.】表名称(
	字段1 数据类型,
	字段2 数据类型,
	字段3 数据类型,
	...,
	primary key(字段列表)
);
说明：复合主键不能在列后面加，需要单独指定

（2）建表后指定主键约束
alter table 【数据库.】表名称 add primary key(字段列表);

例如：
create table stu(
	sid int primary key,  #学号
	sname varchar(20) 	#姓名
);
create table course(
	cid int primary key,		#课程编号
	cname varchar(20)		#课程名称
);
create table score(
	sid int,		#学号
	cid int,		#课程编号
	score int		#对应的成绩
);

insert into stu values(1,'张三'),(2,'李四');
insert into course values(1001,'java'),(1002,'mysql');
insert into score values(1,1001,89),(1,1002,90),(2,1001,56),(2,1002,69);

mysql> select * from stu;
+-----+-------+
| sid | sname |
+-----+-------+
|   1 | 张三      |
|   2 | 李四      |
+-----+-------+
2 rows in set (0.00 sec)

mysql> select * from course;
+------+-------+
| cid  | cname |
+------+-------+
| 1001 | java  |
| 1002 | mysql |
+------+-------+
2 rows in set (0.00 sec)

mysql> select * from score;
+------+------+-------+
| sid  | cid  | score |
+------+------+-------+
|    1 | 1001 |    89 |
|    1 | 1002 |    90 |
|    2 | 1001 |    56 |
|    2 | 1002 |    69 |
+------+------+-------+
4 rows in set (0.00 sec)

alter table score add primary key(sid,cid);


create table score(
	sid int,		#学号
	cid int,		#课程编号
	score int,		#对应的成绩
	primary key(sid,cid)
);

变通成如下这种：
create table score(
	id int,			#没有业务意义，只是唯一标记一行
	sid int,		#学号
	cid int,		#课程编号
	score int,		#对应的成绩
	primary key(id)
);

唯一键约束

（一）概述
1、关键字：unique key
2、特点：指定了唯一键的列的值必须唯一，不能重复
3、作用：给主键以外的列，限定唯一性
4、唯一键分类
单列的唯一
复合唯一

唯一键和主键的区别：
（1）主键不能为空，唯一键可以为空
（2）主键约束，一个表只能有一个，而唯一键可以有很多个

（二）使用唯一键

1、如何创建/指定唯一键
（1）在建表时
create table 【数据库名.】表名称(
	字段1 数据类型  primary key,
	字段2 数据类型 【unique key】,
	字段3 数据类型 【unique key】,
	...
);
或
create table 【数据库名.】表名称(
	字段1 数据类型  primary key,
	字段2 数据类型 ,
	字段3 数据类型 ,
	...,
	unique key(字段2),  #分别唯一
	unique key(字段3)
);

create table 【数据库名.】表名称(
	字段1 数据类型  primary key,
	字段2 数据类型 ,
	字段3 数据类型 ,
	...,
	unique key(字段列表)  #复合唯一
);

create table emp(
	eid int primary key,  #员工编号
	ename varchar(20),   #姓名
	cardid varchar(18)  unique key,		#身份证号
	tel varchar(11) unique key
);

insert into emp values(1,'张三','123456789123456789','12345678912');
insert into emp values(2,'李四','123456789123456788','12345678912');

mysql> insert into emp values(2,'李四','123456789123456788','12345678912');
ERROR 1062 (23000): Duplicate entry '12345678912' for key 'tel'

（2）在建表后
修改表结构：
alter table 【数据库名.】表名称 add unique key(字段名);
alter table 【数据库名.】表名称 add unique key(字段列表);  #复合唯一

2、删除唯一键
修改表结构：
alter table 【数据库名.】表名称 drop index 索引名;

如果不知道索引名，可以通过如下的语句查询：
show index from 表名称;

alter table emp drop unique key;  #错误的
alter table emp drop unique key(cardid);  #错误的
alter table emp drop index cardid;

索引：index
作用：为了提高查询效率，而设置索引
我们的键约束（主键、唯一键、外键），都会自动创建索引。
因为既然你建立键约束，那么该列的值一定很关键，那么在实际中肯定经常用他们的值来查询。
因此，为了提高查询效率，会自动在这些列上增加索引。

非空和默认值约束

1、如何指定非空约束
（1）建表时
create table emp(
	eid int primary key,  #员工编号
	ename varchar(20) not null,   #姓名
	cardid varchar(18)  unique key not null ,		#身份证号
	tel varchar(11) unique key not null,
	gender char not null default '男'
);

insert into emp values(1,'张三','111','10086','女');
insert into emp values(2,'李四','111','女');  #错误的，原因是值的数量和列的数量不匹配
insert into emp(eid,ename,cardid,gender) values(2,'李四','111','女');   #错误的  因为tel设置非空，但是又没指定默认值
insert into emp(eid,ename,cardid,tel) values(2,'李四','222','10010'); 
insert into emp values(3,'王五','3333','10011',default); 

（2）建表后
create table 【数据库名.】表名称(
	字段1 数据类型 primary key,
	字段2 数据类型 【unique key】【not null】【default 默认值】,
	字段2 数据类型 【unique key】【not null】【default 默认值】,
	...
);

create table emp(
	eid int primary key,  #员工编号
	ename varchar(20) not null,   #姓名
	cardid varchar(18)  unique key  ,		#身份证号
	tel varchar(11) unique key ,
	gender char 
);

alter table emp modify cardid varchar(18) unique key  not null;
alter table emp modify tel varchar(11)   not null;
alter table emp modify gender char not null default '男';

2、如何去掉非空和默认值约束

alter table emp modify gender char ;

自增约束

1、关键字：auto_increment
2、特点：
（1）一个表只能有一个自增列
（2）自增列必须是整型的
（3）自增列必须是键列，例如：主键，唯一键

3、如何指定自增

create table emp(
	eid int primary key auto_increment,
	ename varchar(20) not null
);

insert into emp values(2,'张三');
insert into emp(ename)values('李四');
insert into emp values(0,'王五');
insert into emp values(null,'赵六');

外键约束（了解）

外键约束不是必须的，而且现在很多大的公司，数据量比较大时，不建议在数据库层面设计外键，
因为他觉得这样效率低，把这个数据的约束挪到代码层面去判断。

（一）概述
1、关键字：foreign key
2、特点：
（1）约束的是两张表的关系
需要两张表，或者一张表虚拟成两张表
（2）两张表分为主表（父表）和从表（子表）
外键的建立/指定是在从表（子表）上建立。
（3）被参考的表称为主表，主表的被参考列必须是主键或唯一键
（4）一个表可以有多个外键

（二）如何指定外键
1、在建表时指定外键
要求：

（1）建表的顺序
先建主表，再建从表
从表的语法：
create table 【数据库名.】表名称(
	字段1 数据类型  primary key,
	字段2 数据类型 【unique key】【not null】【default 默认值】,
	字段3 数据类型 【unique key】【not null】【default 默认值】,
	...,
	foreign key(从表的外键列) references 主表名(主表被参考的列名)
);
（2）删表的顺序
先删从表，再删除主表
（3）添加/修改从表数据
添加/修改从表记录时，引用主表的列的值必须是存在的。
例如：添加/修改员工表时，员工所在部门的值必须引用部门表的部门编号，保证该部门编号是存在的。
（4）删除/修改主表记录
A：默认情况下，如果主表的被参考列的值被引用，那么就不能轻易的被删除和修改。
例如：2号部门被员工引用了，那么这个2号部门就不能被删除，并且2这个编号值不能被修改。
	foreign key(从表的外键列) references 主表名(主表被参考的列名) 【on update restrict/no action】 【on delete restrict/no action】
B：如果在建立外键时，指定了“级联”策略，那么可以做到级联修改和删除
  foreign key(从表的外键列) references 主表名(主表被参考的列名) 【on update cascade】 【on delete cascade】
C:如果在建立外键时，指定了“置空”策略，那么可以做到主表的记录被修改或删除时，从表的对应字段变为NULL
   foreign key(从表的外键列) references 主表名(主表被参考的列名) 【on update set null】 【on delete set null】

#部门表：主表
create table dept(
	did int primary key,		#部门编号
	dname varchar(20) not null unique key,  #部门名称
	description varchar(100)				#部门简介
);
insert into dept values(1,'财务部','发钱的'),(2,'后勤部','发礼物的');

#职位表：主表
create table job(
	jid int primary key,		#职位编号
	title varchar(20) not null , #职位名称
	description varchar(100)	#职位简介
);
insert into job values(1,'会计','算钱的'),(2,'助理','修电脑的');

#员工表：从表
create table emp(
	eid int primary key,			#员工编号
	ename varchar(20) not null,  #员工姓名
	deptid int,    #所在的部门编号   deptid可以取名did
	jobid int,     #职位编号
	foreign key(deptid) references dept(did),
	foreign key(jobid) references job(jid) on update set null on delete set null
);

insert into emp values(1,'张三',1,1),(2,'李四',1,1),(3,'王五',2,2),(4,'赵六',2,2);

2、建表后指定外键
alter table 从表名称 add foreign key(从表的字段) references 主表名(主表被参考的列名);
墨卿风竹
关注
  • 6
    点赞
  • 踩
  • 58
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL之表的约束(主键、外键、唯一键、自增长、列描述、默认值、空属性)
mayanni_blogs的博客
07-19 260
1.表的约束 真正约束字段的是数据类型，但是数据类型约束很单一，需要一些格外的约束，更好的保证数据的合法性，比如有一个字段是身份证号，要求是唯一的。 表的约束有很多，主要学习以下几种： null/not null; default; comment; zerofill; primary key; auto_increment; unique key; 1.1空属性null/not null 1)n...
MySQL外键（详解）
begefefsef的博客
08-02 3377
因此我收集了一份《java开发全套学习资料》送给大家，初衷也很简单，就是希望帮助到想自学又不知道该从何学起的朋友，同时减轻大家的负担。我个人整理了我这几年软件测试生涯整理的一些技术资料，包含电子书，简历模块，各种工作模板，面试宝典，自学项目等。--------学生信息表中的id变化了，学生成绩表中的id也要随着发生改变。在上图上涉及的两个表中，学生信息表(stuInfo)为主表，学生。--------学生成绩表中不能出现学生信息表中没有的学号。--------先删除成绩表、后删除学生信息表。...
MySQL 之 约束 （主键、唯一、非空、自增、外键）.md
05-27
MySQL 之 约束 （主键、唯一、非空、自增、外键）
MYSQL的约束条件
weixin_52326703的博客
04-12 1415
一、主键约束 1.特点：不能添加重复的值，并且值不能为NULL。 2.分类： （1）单链主键 CONSTRAINT pk1 primary key(eid) （2）联合主键： 就是由一张表中多个字段组成的。 primary key（字段1，字段2，……，字段n） 3.修改表结构，添加主键 添加单列主键：alter table emp4 add primary key(eid); 添加多列主键：alter table emp5 add primary key(name,deptid); 4.删除主键 .
MySQL约束
m0_53644435的博客
02-21 946
一、主键约束（primary key）PK MySQL主键约束是一个列或者多个列的组合，其值能唯一地标识表中的每一行,方便尽快的找到某一行。主键约束相当于 唯一约束 + 非空约束 的组合，主键约束列不允许重复，也不允许出现空值。每个表最多只允许一个主键。主键约束的关键字是：primary key，当创建主键的约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。 1.添加单列主键 （1）定义字段的同时指定主键 语法格式： create table 表名( ... <字段名&
MySQL学习总结（非空约束、唯一约束、主键约束、默认约束、自动增长）
2202_75688394的博客
03-18 7967
使用Navicat Premiun 软件来学习MySQL的非空约束、唯一约束、主键约束、默认约束
sql数据库 主键、自增约束、唯一约束、非空约束、默认约束、外键约束
erfan_lang的博客
10-08 4130
目录primary key主建auto_increment自增约束如果说我们创建表的时候，忘记创建主键约束如何删除主键修改主键约束唯一约束删除唯一约束modify添加注意not null非空约束default默认约束foreigin_key 外键约束 primary key主建 唯一标识表中的每一条数据 不能重复，不能为空 create table if not exists stu( id int primary key, <------#主建 name varchar(20) );
「MySql高级查询与编程」练习：企业员工管理
热门推荐
杨明金的博客
11-13 5万+
题目：企业员工管理 一、语言和环境 1.实现语言：SQL。 2.开发环境：MySQL，navicat for mysql。 二、题目（100分）： 1、创建数据库及数据表： （1）创建数据库，名称为db_emp； （2）创建数据表t_emp（员工表）、t_dept（部门表）、t_salary（薪资表）。 表1 t_emp（员工表） 字段列名 含义 数据类型 长度 允许为空 约束 empId 员工编号 int - no 主键、自增长 empName 员工姓名 Varchar 30 No
MySQL语句之约束条件的设置
12-14
在MySQL数据库中，约束条件是用于确保数据表中数据完整性和准确性的规则。它们是数据库设计的重要组成部分，有助于保持数据的一致性和准确性。以下是对标题和描述中提及的几种约束条件的详细说明： 1. **主键约束**...
二、MySQL表约束和表关系学习ppt
06-11
3. **主键约束 (PRIMARY KEY)**：主键是表中用于唯一标识每条记录的字段，通常结合非空约束。一张表只能有一个主键，可以由一个或多个字段组成。添加主键约束使用`ALTER TABLE`语句，删除主键约束使用`DROP PRIMARY ...
MySQL之DDL语句
07-16
除了创建表，示例还展示了如何使用DDL语句对已有表进行操作，如删除主键约束、添加主键和唯一约束、删除约束、修改非空约束和默认值、添加和删除列以及修改表名。 例如： - 删除`user`表的主键约束： ```sql ...
MYSQL 建表约束
12-14
MySQL中的建表约束是数据库设计中的重要组成部分，用于确保数据的完整性和一致性。这些约束主要有以下几种类型： 1. **主键约束（Primary Key）**： 主键约束是用来唯一标识表中每一条记录的字段，它不允许重复...
每天学一点之MySQL中的约束和索引
qq_52370789的博客
03-14 735
因此应该在键列、或其他经常要查询、排序、按范围查找的列上建立索引，而对于在查询中很少使用和参考的列、修改非常频繁的列，值很少的列（例如性别只有男和女）等列上不应该创建索引。2、一张表可以设置多个唯一键约束，也可以设置联合唯一键，即多个字段设置一个唯一约束，但是不能使用"unique key"写在字段后设置，必须写在所有字段后，使用"unique key(字段,字段)"但是索引名是列名，不是外键的约束名。从表的外键列，在主表中引用的只能是键列（主键，唯一键，外键），推荐引用主表的主键。
MySQL-3
qq_42947060的博客
03-30 443
MySQL-3建表拓展表结构修改约束条件表关系一对一一对多多对多 建表拓展 表结构修改 基础语：alter table 表名 修改表名：rename to new_tb; 修改字段名：change field_name now_name data_type; 修改字段类型：modifiy field_name data_type; 添加字段：add field_name data_type; 删除...
MySQL基础篇常用约束总结下篇
weixin_44606952的博客
06-10 737
1. 自增列AUTO_INCREMENT 1.1 自增列概述 1.2 指定自增约束 1.3 删除自增约束 1.4 MySQL 8.0新特性—自增变量的持久化 2. 外键约束FOREIGN KEY 2.1 外键约束概述 2.2 添加外键约束 2.3 删除外键约束 3. CHECK 约束 3.1 CHECK 约束概述 3.2 约束使用 4. DEFAULT约束 4.1 DEFAULT约束概述 3.2 字段加默认值 4.3 删除默认值约束...
day 06 非空约束、唯一约束、主键约束、外键约束
冷烬亿下
03-31 2695
由于sql语句不区分大小写，为了书写方便，本文所有命令统一使用小写 往期内容 day 01 初识Mysql以及DDL数据定义语言 day 02 DML数据操作语言​​​​​​ day 03 DQL数据查询语言---初窥门径 day 04 DQL数据查询语言---单表查询---略有小成 day 05 DQL数据查询语言---连接查询---登堂入室 约束就是在创建表的时候，给表中的字段加上一些约束，来保证这个表中数据的完整性、有效性！！！ 约束的作用就是为了保证：表中的数据有...
使用sql语句在emp员工表中实现各种操作
张超的博客
12-04 9045
使用sql语句在emp员工表中实现各种操作 emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno所属部门编号) 工资 ＝ 薪金 ＋ 佣金 在emp 表中完成如下练习： 1、 在emp表中查询出所有记录的姓名、部门编号、薪水，并且列名要显示为中文。 2、 在emp表中查询出薪水大于1500的记录，并且按照薪水的降序排列。 3、 在emp表中查询出comm字段为空值的记录。 4、 查询出emp表中含有几个部门的记录。（
主键在非空约束
m0_65467786的博客
10-20 742
主键约束即primary key用于唯一的标识表中的每一行。被标识为主键的数据在表中是唯一的且其值不能为空。这点类似于我们每个人都有一个身份证号，并且这个身份证号是唯一的。设置主键约束(primary key)的第二·种方式。设置主键约束(primary key)的第一种方式。字段名 数据类型 primary key;字段名 数据类型 NOT NULL;
qt练习控件label控件-lineEdit控件样例代码
最新发布
07-30
qt练习控件label控件-lineEdit控件样例代码
用mysql56版本的sql语句创建一个部门信息表，字段1为部门编号，数据类型为int，非空约束，主键约束且自增；字段2为部门名称，数据类型为varchar，长度为20，非空约束。再创建一个员工信息表，字段1为编号，数据类型为int，非空约束，主键约束且自增；字段2为姓名，数据类型为varchar，长度为20，非空约束；字段3为性别，数据类型为varchar，长度为2，非空约束；字段4为联系电话，数据类型为char，长度为11，非空约束；字段5为月薪，数据类型为decimal，长度为8,2为小数点，非空约束，设置检查约束，大于0；字段6为入职日期，数据类型为date，非空约束，设置默认值，默认为系统日期；字段7为部门编号，数据类型为int，外键约束，参照部门信息表的部门编号。
06-08
以下是对应的SQL语句： 创建部门信息表： ```sql CREATE TABLE department ( department_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, department_name VARCHAR(20) NOT NULL ); ``` 创建员工信息表： ```sql CREATE TABLE employee ( employee_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, employee_name VARCHAR(20) NOT NULL, gender VARCHAR(2) NOT NULL, phone CHAR(11) NOT NULL, salary DECIMAL(8,2) NOT NULL CHECK (salary > 0), hire_date DATE NOT NULL DEFAULT CURRENT_DATE, department_id INT NOT NULL, FOREIGN KEY (department_id) REFERENCES department (department_id) ); ``` 这里使用了外键约束，将员工信息表中的部门编号字段设为外键，参照部门信息表中的部门编号字段。这样可以保证员工信息表中的部门编号只能引用部门信息表中存在的部门编号。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值