MySQL之约束条件
表完整性约束
介绍：约束条件与数据类型的宽度一样，都是可选参数
作用：用于保证数据的完整性和一致性
主要分为：
DEFAULT 为该字段设置默认值
NOT NULL 标识该字段不能为空
UNIQUE KEY (UK) 标识该字段的值是唯一的
AUTO_INCREMENT 标识该字段的值自动增长（整数类型，而且为主键）
PRIMARY KEY (PK) 标识该字段为该表的主键，可以唯一的标识记录
FOREIGN KEY (FK) 标识该字段为该表的外键
UNSIGNED 无符号
ZEROFILL 使用0填充
not null 非空
是否可空，null表示空，非字符串 not null - 不可空 null - 可空
示例：
create table t0(id int); #id字段默认可以插入空
desc t0;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
insert into t0 values(); #可以插入空
create table t0_0(id int not null); #设置字段id不为空
desc t0_0;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
+-------+---------+------+-----+---------+-------+
insert into t0_0 values(); #不能插入空
ERROR 1364 (HY000): Field 'id' doesn't have a default value
default默认值
默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值 create table tb1( nid int not null defalut 2, num int not null )
注意：设置id字段有默认值后，则无论id字段是null还是not null，都可以插入空，插入空默认填入default指定的默认值。
示例：
create table t1(
id int,
name char(16)
);
desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(16) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
insert into t1(name,id) values ('xiao',1);
select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | xiao |
+------+------+
# 默认值使用
create table t2(
id int,
name char(16),
gender enum('male','female','others') default 'male'
);
desc t2;
+--------+--------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(16) | YES | | NULL | |
| gender | enum('male','female','others') | YES | | male | |
+--------+--------------------------------+------+-----+---------+-------+
insert into t2(id,name) values(1,'xiao');
insert into t2 values(2,'quan','female');
select * from t2;
+------+------+--------+
| id | name | gender |
+------+------+--------+
| 1 | xiao | male |
| 2 | quan | female |
+------+------+--------+
unique唯一
# 单列唯一
create table t3(
id int unique,
name char(16)
);
desc t3;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | UNI | NULL | |
| name | char(16) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
insert into t3 values(1,'xiao'),(1,'quan');
# ERROR 1062 (23000): Duplicate entry '1' for key 'id'
insert into t3 values(1,'xiao'),(2,'quan');
select * from t3;
+------+------+
| id | name |
+------+------+
| 1 | xiao |
| 2 | quan |
+------+------+
# 联合唯一
ip 和 port
单个都可以重复，但是加在一起必须是唯一的
create table t4(
id int,
ip char(16),
port int,
unique(ip,port)
);
insert into t4 value(1,'127.0.0.1',8080);
insert into t4 value(2,'127.0.0.1',8081);
insert into t4 value(3,'127.0.0.2',8080);
select * from t4;
+------+-----------+------+
| id | ip | port |
+------+-----------+------+
| 1 | 127.0.0.1 | 8080 |
| 2 | 127.0.0.1 | 8081 |
| 3 | 127.0.0.2 | 8080 |
+------+-----------+------+
insert into t4 value(4,'127.0.0.1',8080);
# ERROR 1062 (23000): Duplicate entry '127.0.0.1-8080' for key 'ip'
not null+unique组合使用
create table t1(id int not null unique);
desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
+-------+---------+------+-----+---------+-------+
primary key主键
从约束角度看primary key字段的值不为空且唯一，那我们直接使用not null+unique不就可以了吗，要它干什么？
主键primary key是innodb存储引擎组织数据的依据，innodb称之为索引组织表，一张表中必须有且只有一个主键。
1. 单单从约束效果上来看primary key等价于not null + unique
非空且唯一！！！
create table t5(id int primary key);
desc t5;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
insert into t5 values(null);
# ERROR 1048 (23000): Column 'id' cannot be null
insert into t5 values(1),(1);
# ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
2. 它除了有约束效果之外，它还是Innodb存储引擎组织数据的依据，Innodb存储引擎在创建表的时候必须要有primary key
因为它类似于书的目录，能够帮助提示查询效率并且也是建表的依据
# 1.一张表中有且只有一个主键，如果你没有设置主键，那么会从上往下搜索直到遇到一个非空且唯一的字段将它自动升级为主键。
create table t6(
id int,
name char(16),
age int not null unique,
addr char(32) not null unique
);
desc t6;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(16) | YES | | NULL | |
| age | int(11) | NO | PRI | NULL | |
| addr | char(32) | NO | UNI | NULL | |
+-------+----------+------+-----+---------+-------+
# 2.如果表中没有主键也没有其他任何的非空且唯一字段，那么Innodb会采用自己内部提供的一个隐藏字段作为主键，隐藏意味着你无法使用到它，就无法提示查询速度。
# 3.一张表中通常都应该有一个主键字段，并且通常将id/uid/sid字段作为主键。
# 单个字段主键
create table t7(
id int primary key,
name char(16)
);
# 联合主键(多个字段联合起来作为表的主键，本质还是一个主键)
create table t8(
ip char(16),
port int,
primary key(ip,port)
);
desc t8;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| ip | char(16) | NO | PRI | NULL | |
| port | int(11) | NO | PRI | NULL | |
+-------+----------+------+-----+---------+-------+
本质上还是一个主键
总结：也就意味着，以后我们在创建表的时候id字段一定要加上primary key
auto_increment自增
约束字段为自动增长，被约束的字段必须同时被key约束
# 当编号特别多的时候，人为的去维护太麻烦
create table t9(
id int primary key auto_increment,
name char(16)
);
insert into t9(name) values('xiao'),('quan'),('zheng');
select * from t9;
+----+-------+
| id | name | id自增
+----+-------+
| 1 | xiao |
| 2 | quan |
| 3 | zheng |
+----+-------+
# 注意:auto_increment 通常都是加在主键上的，不能给普通字段加
create table t10(
id int primary key auto_increment,
name char(16),
cid int auto_increment
);
# ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
# 结论:以后再创建表的id(数据的唯一标识id、uid、sid)字段的时候
id int primary key auto_increment
补充
delete from 在删除表中数据的时候，主键的自增不会停止
truncate t1 清空表数据并重置主键
-- 语法
ALTER TABLE 表名 AUTO_INCREMENT = 1;
例子：再次修改自增键
ALTER TABLE t8 AUTO_INCREMENT = 1;
如何查看当前表的约束条件
- 给约束条件添加名字
show databases;
use information_schema;
show tables;
desc table_constraints;
- table_constraints 该表专门存储约束信息
- 查看某张表存在哪些约束条件？
select constraint_name from table_constraints where table_name='表名';
外键
下一篇笔记详解外键
浙公网安备 33010602011771号