mysql 表的完整性约束

mysql 表的完整性约束

阅读目录

• 　　概览
• 　　not null
• 　　unique
• 　　primary key
• 　　foreign key

概览

　　为了防止不符合规范的数据进入数据库，在用户对数据进行插入、修改、删除等操作时，DBMS自动按照一定的约束条件对数据进行监测，使不符合规范的数据不能进入数据库，以确保数据库中存储的数据正确、有效、相容。

　　约束条件与数据类型的宽度一样，都是可选参数，主要分为以下几种：

# NOT NULL ：非空约束，指定某列不能为空； # UNIQUE : 唯一约束，指定某列或者几列组合不能重复# PRIMARY KEY ：主键，指定该列的值可以唯一地标识该列记录# FOREIGN KEY ：外键，指定该行记录从属于主表中的一条记录，主要用于参照完整性

NOT NULL

是否可空，null表示空，非字符串
not null - 不可空
null - 可空

not null示例

mysql> create table t12 (id int not null);Query OK, 0 rows affected (0.02 sec)mysql> select * from t12;Empty set (0.00 sec)mysql> desc t12;+-------+---------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id| int(11) | NO| | NULL||+-------+---------+------+-----+---------+-------+1 row in set (0.00 sec)#不能向id列插入空元素。 mysql> insert into t12 values (null);ERROR 1048 (23000): Column 'id' cannot be nullmysql> insert into t12 values (1);Query OK, 1 row affected (0.01 sec)

DEFAULT

我们约束某一列不为空，如果这一列中经常有重复的内容，就需要我们频繁的插入，这样会给我们的操作带来新的负担，于是就出现了默认值的概念。

默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值

not null + default 示例

mysql> create table t13 (id1 int not null,id2 int not null default 222);Query OK, 0 rows affected (0.01 sec)mysql> desc t13;+-------+---------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id1| int(11) | NO| | NULL||| id2| int(11) | NO| | 222 ||+-------+---------+------+-----+---------+-------+2 rows in set (0.01 sec)# 只向id1字段添加值，会发现id2字段会使用默认值填充mysql> insert into t13 (id1) values (111);Query OK, 1 row affected (0.00 sec)mysql> select * from t13;+-----+-----+| id1 | id2 |+-----+-----+| 111 | 222 |+-----+-----+1 row in set (0.00 sec)# id1字段不能为空，所以不能单独向id2字段填充值；mysql> insert into t13 (id2) values (223);ERROR 1364 (HY000): Field 'id1' doesn't have a default value# 向id1，id2中分别填充数据，id2的填充数据会覆盖默认值mysql> insert into t13 (id1,id2) values (112,223);Query OK, 1 row affected (0.00 sec)mysql> select * from t13;+-----+-----+| id1 | id2 |+-----+-----+| 111 | 222 || 112 | 223 |+-----+-----+2 rows in set (0.00 sec)

not null不生效

设置严格模式：不支持对not null字段插入null值不支持对自增长字段插入”值不支持text字段有默认值直接在mysql中生效(重启失效):mysql>set sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";配置文件添加(永久失效)：sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

UNIQUE

唯一约束，指定某列或者几列组合不能重复

unique示例

方法一：create table department1(id int,name varchar(20) unique,comment varchar(100));方法二：create table department2(id int,name varchar(20),comment varchar(100),unique(name));mysql> insert into department1 values(1,'IT','技术');Query OK, 1 row affected (0.00 sec)mysql> insert into department1 values(1,'IT','技术');ERROR 1062 (23000): Duplicate entry 'IT' for key 'name'

not null 和unique的结合

mysql> create table t1(id int not null unique);Query OK, 0 rows affected (0.02 sec)mysql> desc t1;+-------+---------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id| int(11) | NO| PRI | NULL||+-------+---------+------+-----+---------+-------+1 row in set (0.00 sec)

联合唯一

create table service(id int primary key auto_increment,name varchar(20),host varchar(15) not null,port int not null,unique(host,port) #联合唯一);mysql> insert into service values-> (1,'nginx','192.168.0.10',80),-> (2,'haproxy','192.168.0.20',80),-> (3,'mysql','192.168.0.30',3306)-> ;Query OK, 3 rows affected (0.01 sec)Records: 3  Duplicates: 0  Waings: 0mysql> insert into service(name,host,port) values('nginx','192.168.0.10',80);ERROR 1062 (23000): Duplicate entry '192.168.0.10-80' for key 'host'

PRIMARY KEY

主键为了保证表中的每一条数据的该字段都是表格中的唯一值。换言之，它是用来独一无二地确认一个表格中的每一行数据。
主键可以包含一个字段或多个字段。当主键包含多个栏位时，称为组合键 (Composite Key),也可以叫联合主键。
主键可以在建置新表格时设定 (运用 CREATE TABLE 语句)，或是以改变现有的表格架构方式设定 (运用 ALTER TABLE)。
主键必须唯一，主键值非空；可以是单一字段，也可以是多字段组合。

1.单字段主键

单字段主键

============单列做主键===============#方法一：not null+uniquecreate table department1(id int not null unique, #主键name varchar(20) not null unique,comment varchar(100));mysql> desc department1;+---------+--------------+------+-----+---------+-------+| Field| Type | Null | Key | Default | Extra |+---------+--------------+------+-----+---------+-------+| id  | int(11)  | NO| PRI | NULL||| name| varchar(20)  | NO| UNI | NULL||| comment | varchar(100) | YES  | | NULL||+---------+--------------+------+-----+---------+-------+rows in set (0.01 sec)#方法二：在某一个字段后用primary keycreate table department2(id int primary key, #主键name varchar(20),comment varchar(100));mysql> desc department2;+---------+--------------+------+-----+---------+-------+| Field| Type | Null | Key | Default | Extra |+---------+--------------+------+-----+---------+-------+| id  | int(11)  | NO| PRI | NULL||| name| varchar(20)  | YES  | | NULL||| comment | varchar(100) | YES  | | NULL||+---------+--------------+------+-----+---------+-------+rows in set (0.00 sec)#方法三：在所有字段后单独定义primary keycreate table department3(id int,name varchar(20),comment varchar(100),primary key(id); #创建主键并为其命名pk_namemysql> desc department3;+---------+--------------+------+-----+---------+-------+| Field| Type | Null | Key | Default | Extra |+---------+--------------+------+-----+---------+-------+| id  | int(11)  | NO| PRI | NULL||| name| varchar(20)  | YES  | | NULL||| comment | varchar(100) | YES  | | NULL||+---------+--------------+------+-----+---------+-------+rows in set (0.01 sec)# 方法四：给已经建成的表添加主键约束mysql> create table department4(-> id int,-> name varchar(20),-> comment varchar(100));Query OK, 0 rows affected (0.01 sec)mysql> desc department4;+---------+--------------+------+-----+---------+-------+| Field| Type | Null | Key | Default | Extra |+---------+--------------+------+-----+---------+-------+| id  | int(11)  | YES  | | NULL||| name| varchar(20)  | YES  | | NULL||| comment | varchar(100) | YES  | | NULL||+---------+--------------+------+-----+---------+-------+3 rows in set (0.01 sec)mysql> alter table department4 modify id int primary key;Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Waings: 0mysql> desc department4;+---------+--------------+------+-----+---------+-------+| Field| Type | Null | Key | Default | Extra |+---------+--------------+------+-----+---------+-------+| id  | int(11)  | NO| PRI | NULL||| name| varchar(20)  | YES  | | NULL||| comment | varchar(100) | YES  | | NULL||+---------+--------------+------+-----+---------+-------+3 rows in set (0.01 sec)

2.多字段主键

多字段主键

==================多列做主键================create table service(ip varchar(15),port char(5),service_name varchar(10) not null,primary key(ip,port));mysql> desc service;+--------------+-------------+------+-----+---------+-------+| Field| Type| Null | Key | Default | Extra |+--------------+-------------+------+-----+---------+-------+| ip| varchar(15) | NO| PRI | NULL||| port | char(5) | NO| PRI | NULL||| service_name | varchar(10) | NO| | NULL||+--------------+-------------+------+-----+---------+-------+3 rows in set (0.00 sec)mysql> insert into service values-> ('172.16.45.10','3306','mysqld'),-> ('172.16.45.11','3306','mariadb')-> ;Query OK, 2 rows affected (0.00 sec)Records: 2  Duplicates: 0  Waings: 0mysql> insert into service values ('172.16.45.10','3306','nginx');ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'

AUTO_INCREMENT

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

设置auto_increment

#不指定id，则自动增长create table student(id int primary key auto_increment,name varchar(20),sex enum('male','female') default 'male');mysql> desc student;+-------+-----------------------+------+-----+---------+----------------+| 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||+-------+-----------------------+------+-----+---------+----------------+mysql> insert into student(name) values-> ('egon'),-> ('alex')-> ;mysql> select * from student;+----+------+------+| id | name | sex  |+----+------+------+|  1 | egon | male ||  2 | alex | male |+----+------+------+#也可以指定idmysql> insert into student values(4,'asb','female');Query OK, 1 row affected (0.00 sec)mysql> insert into student values(7,'wsb','female');Query OK, 1 row affected (0.00 sec)mysql> select * from student;+----+------+--------+| id | name | sex|+----+------+--------+|  1 | egon | male||  2 | alex | male||  4 | asb  | female ||  7 | wsb  | female |+----+------+--------+#对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长mysql> delete from student;Query OK, 4 rows affected (0.00 sec)mysql> select * from student;Empty set (0.00 sec)mysql> insert into student(name) values('ysb');mysql> select * from student;+----+------+------+| id | name | sex  |+----+------+------+|  8 | ysb  | male |+----+------+------+#应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它mysql> truncate student;Query OK, 0 rows affected (0.01 sec)mysql> insert into student(name) values('egon');Query OK, 1 row affected (0.01 sec)mysql> select * from student;+----+------+------+| id | name | sex  |+----+------+------+|  1 | egon | male |+----+------+------+row in set (0.00 sec)

了解知识

offset偏移量

#在创建完表后，修改自增字段的起始值mysql> create table student(-> id int primary key auto_increment,-> name varchar(20),-> sex enum('male','female') default 'male'-> );mysql> alter table student auto_increment=3;mysql> show create table student;.......ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mysql> insert into student(name) values('egon');Query OK, 1 row affected (0.01 sec)mysql> select * from student;+----+------+------+| id | name | sex  |+----+------+------+|  3 | egon | male |+----+------+------+row in set (0.00 sec)mysql> show create table student;.......ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8#也可以创建表时指定auto_increment的初始值，注意初始值的设置为表选项，应该放到括号外create table student(id int primary key auto_increment,name varchar(20),sex enum('male','female') default 'male')auto_increment=3;#设置步长sqlserver：自增步长基于表级别create table t1（id int。。。）engine=innodb,auto_increment=2 步长=2 default charset=utf8mysql自增的步长：show session variables like 'auto_inc%';#基于会话级别set session auth_increment_increment=2 #修改会话级别的步长#基于全局级别的set global auth_increment_increment=2 #修改全局级别的步长（所有会话都生效）#！！！注意了注意了注意了！！！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的值会被忽略 ，这相当于第一步步子就迈大了，扯着了蛋比如：设置auto_increment_offset=3，auto_increment_increment=2mysql> set global auto_increment_increment=5;Query OK, 0 rows affected (0.00 sec)mysql> set global auto_increment_offset=3;Query OK, 0 rows affected (0.00 sec)mysql> show variables like 'auto_incre%'; #需要退出重新登录+--------------------------+-------+| Variable_name| Value |+--------------------------+-------+| auto_increment_increment | 1 || auto_increment_offset| 1 |+--------------------------+-------+create table student(id int primary key auto_increment,name varchar(20),sex enum('male','female') default 'male');mysql> insert into student(name) values('egon1'),('egon2'),('egon3');mysql> select * from student;+----+-------+------+| id | name  | sex  |+----+-------+------+|  3 | egon1 | male ||  8 | egon2 | male || 13 | egon3 | male |+----+-------+------+步长:auto_increment_increment,起始偏移量:auto_increment_offset

FOREIKEY

多表 ：

假设我们要描述所有公司的员工，需要描述的属性有这些 ： 工号 姓名 部门

公司有3个部门，但是有1个亿的员工，那意味着部门这个字段需要重复存储，部门名字越长，越浪费

解决方法： 我们完全可以定义一个部门表 然后让员工信息表关联该表，如何关联，即foreign key

创造外键的条件

mysql> create table departments (dep_id int(4),dep_name varchar(11));Query OK, 0 rows affected (0.02 sec)mysql> desc departments;+----------+-------------+------+-----+---------+-------+| Field| Type| Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| dep_id| int(4)  | YES  | | NULL||| dep_name | varchar(11) | YES  | | NULL||+----------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)# 创建外键不成功mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id));ERROR 1215 (HY000): Cannot add foreign key # 设置dep_id非空，仍然不能成功创建外键mysql> alter table departments modify dep_id int(4) not null;Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Waings: 0mysql> desc departments;+----------+-------------+------+-----+---------+-------+| Field| Type| Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| dep_id| int(4)  | NO| | NULL||| dep_name | varchar(11) | YES  | | NULL||+----------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id));ERROR 1215 (HY000): Cannot add foreign key constraint# 当设置字段为unique唯一字段时，设置该字段为外键成功mysql> alter table departments modify dep_id int(4) unique;Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Waings: 0mysql> desc departments;+----------+-------------+------+-----+---------+-------+| Field| Type| Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| dep_id| int(4)  | YES  | UNI | NULL||| dep_name | varchar(11) | YES  | | NULL||+----------+-------------+------+-----+---------+-------+2 rows in set (0.01 sec)mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id));Query OK, 0 rows affected (0.02 sec)

外键操作示例

#表类型必须是innodb存储引擎，且被关联的字段，即references指定的另外一个表的字段，必须保证唯一create table department(id int primary key,name varchar(20) not null)engine=innodb;#dpt_id外键，关联父表（department主键id），同步更新，同步删除create table employee(id int primary key,name varchar(20) not null,dpt_id int,foreign key(dpt_id)references department(id)on delete cascade  # 级连删除on update cascade # 级连更新)engine=innodb;#先往父表department中插入记录insert into department values(1,'教质部'),(2,'技术部'),(3,'人力资源部');#再往子表employee中插入记录insert into employee values(1,'yuan',1),(2,'nezha',2),(3,'egon',2),(4,'alex',2),(5,'wusir',3),(6,'李沁洋',3),(7,'皮卡丘',3),(8,'程咬金',3),(9,'程咬银',3);#删父表department，子表employee中对应的记录跟着删mysql> delete from department where id=2;Query OK, 1 row affected (0.00 sec)mysql> select * from employee;+----+-----------+--------+| id | name  | dpt_id |+----+-----------+--------+|  1 | yuan  |  1 ||  5 | wusir |  3 ||  6 | 李沁洋|  3 ||  7 | 皮卡丘|  3 ||  8 | 程咬金|  3 ||  9 | 程咬银|  3 |+----+-----------+--------+6 rows in set (0.00 sec)#更新父表department，子表employee中对应的记录跟着改mysql> update department set id=2 where id=3;Query OK, 1 row affected (0.01 sec)Rows matched: 1  Changed: 1  Waings: 0mysql> select * from employee;+----+-----------+--------+| id | name  | dpt_id |+----+-----------+--------+|  1 | yuan  |  1 ||  5 | wusir |  2 ||  6 | 李沁洋|  2 ||  7 | 皮卡丘|  2 ||  8 | 程咬金|  2 ||  9 | 程咬银|  2 |+----+-----------+--------+6 rows in set (0.00 sec)

on delete(了解)

 . cascade方式在父表上update/delete记录时，同步update/delete掉子表的匹配记录. set null方式在父表上update/delete记录时，将子表上匹配记录的列设为null要注意子表的外键列不能为not null . No action方式如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作 . Restrict方式同no action, 都是立即检查外键约束. Set default方式父表有变更时,子表将外键列设置成一个默认的值 但Innodb不能识别