mysql中怎么实现一个完整性约束

2025-03-27 技术教程

这篇文章将为大家详细讲解有关mysql中怎么实现一个完整性约束，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。

约束条件作用：用于保证数据的完整性和一致性

主要分为

PRIMARY KEY (PK) #标识该字段为该表的主键，可以唯一的标识记录
FOREIGN KEY (FK) #标识该字段为该表的外键
NOT NULL #标识该字段不能为空
UNIQUE KEY (UK) #标识该字段的值是唯一的，
AUTO_INCREMENT #标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT #为该字段设置默认值
UNSIGNED #无符号
ZEROFILL #使用0填充

unique

在mysql中称为单列唯一

#例子1：createtabledepartment(idint,namechar(10)unique);mysql>insertintodepartmentvalues(1,'it'),(2,'it');ERROR1062(23000):Duplicateentry'it'forkey'name'#例子2：createtabledepartment(idintunique,namechar(10)unique);insertintodepartmentvalues(1,'it'),(2,'sale');#第二种创建unique的方式createtabledepartment(idint,namechar(10),unique(id),unique(name));insertintodepartmentvalues(1,'it'),(2,'sale');

联合唯一：只要两列记录，有一列不同，既符合联合唯一的约束

#创建services表mysql>createtableservices(->idint,->ipchar(15),->portint,->unique(id),->unique(ip,port)->);QueryOK,0rowsaffected(0.05sec)mysql>descservices;+-------+----------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-------+----------+------+-----+---------+-------+|id|int(11)|YES|UNI|NULL|||ip|char(15)|YES|MUL|NULL|||port|int(11)|YES||NULL||+-------+----------+------+-----+---------+-------+3rowsinset(0.01sec)#联合唯一，只要两列记录，有一列不同，既符合联合唯一的约束mysql>insertintoservicesvalues->(1,'192,168,11,23',80),->(2,'192,168,11,23',81),->(3,'192,168,11,25',80);QueryOK,3rowsaffected(0.01sec)Records:3Duplicates:0Warnings:0mysql>select*fromservices;+------+---------------+------+|id|ip|port|+------+---------------+------+|1|192,168,11,23|80||2|192,168,11,23|81||3|192,168,11,25|80|+------+---------------+------+3rowsinset(0.00sec)mysql>insertintoservicesvalues(4,'192,168,11,23',80);ERROR1062(23000):Duplicateentry'192,168,11,23-80'forkey'ip'

auto_increment

约束:约束的字段为自动增长，约束的字段必须同时被key约束

不指定id，则自动增长

#创建studentcreatetablestudent(idintprimarykeyauto_increment,namevarchar(20),sexenum('male','female')default'male');mysql>descstudent;+-------+-----------------------+------+-----+---------+----------------+|Field|Type|Null|Key|Default|Extra|+-------+-----------------------+------+-----+---------+----------------+|id|int(11)|NO|PRI|NULL|auto_increment||name|varchar(20)|YES||NULL|||sex|enum('male','female')|YES||male||+-------+-----------------------+------+-----+---------+----------------+rowsinset(0.17sec)#插入记录mysql>insertintostudent(name)values('老白'),('小白');QueryOK,2rowsaffected(0.01sec)Records:2Duplicates:0Warnings:0mysql>select*fromstudent;+----+--------+------+|id|name|sex|+----+--------+------+|1|老白|male||2|小白|male|+----+--------+------+rowsinset(0.00sec)

指定id的情况

mysql>insertintostudentvalues(4,'asb','female');QueryOK,1rowaffected(0.00sec)mysql>insertintostudentvalues(7,'wsb','female');QueryOK,1rowaffected(0.01sec)mysql>select*fromstudent;+----+--------+--------+|id|name|sex|+----+--------+--------+|1|老白|male||2|小白|male||4|asb|female||7|wsb|female|+----+--------+--------+rowsinset(0.00sec)#再次插入一条不指定id的记录，会在之前的最后一条记录继续增长mysql>insertintostudent(name)values('大白');QueryOK,1rowaffected(0.00sec)mysql>select*fromstudent;+----+--------+--------+|id|name|sex|+----+--------+--------+|1|老白|male||2|小白|male||4|asb|female||7|wsb|female||8|大白|male|+----+--------+--------+rowsinset(0.00sec)

对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长

mysql>deletefromstudent;QueryOK,5rowsaffected(0.00sec)mysql>select*fromstudent;Emptyset(0.00sec)mysql>select*fromstudent;Emptyset(0.00sec)mysql>insertintostudent(name)values('ysb');QueryOK,1rowaffected(0.01sec)mysql>select*fromstudent;+----+------+------+|id|name|sex|+----+------+------+|9|ysb|male|+----+------+------+rowinset(0.00sec)#应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它mysql>truncatestudent;QueryOK,0rowsaffected(0.03sec)mysql>insertintostudent(name)values('xiaobai');QueryOK,1rowaffected(0.00sec)mysql>select*fromstudent;+----+---------+------+|id|name|sex|+----+---------+------+|1|xiaobai|male|+----+---------+------+rowinset(0.00sec)mysql>auto_increment_increment和auto_increment_offset

查看可用的开头auto_inc的词

#步长auto_increment_increment,默认为1#起始的偏移量auto_increment_offset,默认是1#设置步长为会话设置，只在本次连接中有效setsessionauto_increment_increment=5;#全局设置步长都有效。setglobalauto_increment_increment=5;#设置起始偏移量setglobalauto_increment_offset=3;

强调：If the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored.
翻译：如果auto_increment_offset的值大于auto_increment_increment的值，则auto_increment_offset的值会被忽略

设置完起始偏移量和步长之后，再次执行show variables like'auto_inc%';

发现跟之前一样，必须先exit,再登录才有效。

mysql>showvariableslike'auto_inc%';+--------------------------+-------+|Variable_name|Value|+--------------------------+-------+|auto_increment_increment|5||auto_increment_offset|3|+--------------------------+-------+rowsinset(0.00sec)#因为之前有一条记录id=1mysql>select*fromstudent;+----+---------+------+|id|name|sex|+----+---------+------+|1|xiaobai|male|+----+---------+------+rowinset(0.00sec)#下次插入的时候，从起始位置3开始，每次插入记录id+5mysql>insertintostudent(name)values('ma1'),('ma2'),('ma3');QueryOK,3rowsaffected(0.00sec)Records:3Duplicates:0Warnings:0mysql>select*fromstudent;+----+---------+------+|id|name|sex|+----+---------+------+|1|xiaobai|male||3|ma1|male||8|ma2|male||13|ma3|male|+----+---------+------+

清空表区分delete和truncate的区别：

delete from t1; #如果有自增id，新增的数据，仍然是以删除前的最后一样作为起始。

truncate table t1;数据量大，删除速度比上一条快，且直接从零开始。

foreign key

理解foreign key

如上图如果一个公司有很多员工，每个员工都对应一个部门，在填表的时候就会重复写这些部门，太冗余了

我们可以将它们分离

此时有两张表，一张是employee表,简称emp表（关联表，也就从表）。一张是department表，简称dep表（被关联表，也叫主表）。

#1.创建表时先创建被关联表，再创建关联表#先创建被关联表（dep表）createtabledep(idintprimarykey,namevarchar(20)notnull,descripevarchar(20)notnull);#再创建关联表（emp表）createtableemp(idintprimarykey,namevarchar(20)notnull,ageintnotnull,dep_idint,constraintfk_depforeignkey(dep_id)referencesdep(id)//创建约束);#2.插入记录时，先往被关联表中插入记录，再往关联表中插入记录insertintodepvalues(1,'IT','IT技术有限部门'),(2,'销售部','销售部门'),(3,'财务部','花钱太多部门');insertintoempvalues(1,'zhangsan',18,1),(2,'lisi',19,1),(3,'egon',20,2),(4,'yuanhao',40,3),(5,'alex',18,2);

3.删除表

#按道理来说，删除了部门表中的某个部门，员工表的有关联的记录相继删除。mysql>deletefromdepwhereid=3;ERROR1451(23000):Cannotdeleteorupdateaparentrow:aforeignkeyconstraintfails(`db5`.`emp`,CONSTRAINT`fk_name`FOREIGNKEY(`dep_id`)REFERENCES`dep`(`id`))#但是先删除员工表的记录之后，再删除当前部门就没有任何问题mysql>deletefromempwheredep_id=3;QueryOK,1rowaffected(0.00sec)mysql>select*fromemp;+----+----------+-----+--------+|id|name|age|dep_id|+----+----------+-----+--------+|1|zhangsan|18|1||2|lisi|18|1||3|egon|20|2||5|alex|18|2|+----+----------+-----+--------+4rowsinset(0.00sec)mysql>deletefromdepwhereid=3;QueryOK,1rowaffected(0.00sec)mysql>select*fromdep;+----+-----------+----------------------+|id|name|descripe|+----+-----------+----------------------+|1|IT|IT技术有限部门||2|销售部|销售部门|+----+-----------+----------------------+2rowsinset(0.00sec)

上面的删除表记录的操作比较繁琐，按道理讲，裁掉一个部门，该部门的员工也会被裁掉。其实呢，在建表的时候还有个很重要的内容，叫同步删除，同步更新

on delete cascade #同步删除
on update cascade #同步更新

createtableemp(idintprimarykey,namevarchar(20)notnull,ageintnotnull,dep_idint,constraintfk_depforeignkey(dep_id)referencesdep(id)ondeletecascade#同步删除onupdatecascade#同步更新);

#再去删被关联表(dep)的记录，关联表(emp)中的记录也跟着删除mysql>deletefromdepwhereid=3;QueryOK,1rowaffected(0.00sec)mysql>select*fromdep;+----+-----------+----------------------+|id|name|descripe|+----+-----------+----------------------+|1|IT|IT技术有限部门||2|销售部|销售部门|+----+-----------+----------------------+2rowsinset(0.00sec)mysql>select*fromemp;+----+----------+-----+--------+|id|name|age|dep_id|+----+----------+-----+--------+|1|zhangsan|18|1||2|lisi|19|1||3|egon|20|2||5|alex|18|2|+----+----------+-----+--------+4rowsinset(0.00sec)#再去更改被关联表(dep)的记录，关联表(emp)中的记录也跟着更改mysql>updatedepsetid=222whereid=2;QueryOK,1rowaffected(0.02sec)Rowsmatched:1Changed:1Warnings:0#赶紧去查看一下两张表是否都被删除了，是否都被更改了mysql>select*fromdep;+-----+-----------+----------------------+|id|name|descripe|+-----+-----------+----------------------+|1|IT|IT技术有限部门||222|销售部|销售部门|+-----+-----------+----------------------+2rowsinset(0.00sec)mysql>select*fromemp;+----+----------+-----+--------+|id|name|age|dep_id|+----+----------+-----+--------+|1|zhangsan|18|1||2|lisi|19|1||3|egon|20|222||5|alex|18|222|+----+----------+-----+--------+4rowsinset(0.00sec)

关于mysql中怎么实现一个完整性约束就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。