MySQL——表的约束

目的：保证数据的合法性，从业务逻辑角度保证数据的正确性
数据类型也算是一种约束

1 空属性

null：字段为空，表示不存在（未填写）
not null：不为空

// 说明null != 0
mysql> select * from tnull;
+--------+------+
| name   | age  |
+--------+------+
| 张三   | NULL |
| 李四   |    0 |
+--------+------+

设置字段不能为空的约束：在创建表时数据类型后加not null

mysql> create table if not exists tnotnull(
name varchar(16) not null, 
age int not null
)engine=innodb default charset=utf8;

// null一列都显示为NO，即不能为NULL
mysql> desc tnotnull;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(16) | NO   |     | NULL    |       |
| age   | int(11)     | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

此时向tnotnull插入时只能同时插入两列信息

2 默认值

关键字：default
default和not null同时设置时，not null不起作用

mysql> create table tdefault( 
name varchar(16) not null, 
age int default 18, 
sex char(1) default '男' 
)engine=innodb default charset=utf8;

// age和sex的default列有值
mysql> desc tdefault;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(16) | NO   |     | NULL    |       |
| age   | int(11)     | YES  |     | 18      |       |
| sex   | char(1)     | YES  |     | 男      |       |
+-------+-------------+------+-----+---------+-------+

 //只插入姓名，年龄和性别也不是空，而是默认值
mysql> insert into tdefault (name) values ('张三');
mysql> select * from tdefault;
+--------+------+------+
| name   | age  | sex  |
+--------+------+------+
| 张三   |   18 | 男   |
+--------+------+------+

3 列描述

关键字：comment
作用：给字段添加一个描述，可以用来解释字段含义

mysql> create table if not exists tcomment(
name varchar(16) not null, 
age int not null comment '用户的年龄' 
)engine=innodbdefault charset=utf8;

// show create table tablename查看建表时的详细信息，\G调整显示的格式
mysql> show create table tcomment \G
*************************** 1. row ***************************
       Table: tcomment
Create Table: CREATE TABLE `tcomment` (
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL COMMENT '用户的年龄'
) ENGINE=InnoDB DEFAULT CHARSET=utf8

4 zerofill

和数据类型后面括号里的数字有关

mysql> create table if not exists tzerofill(
a int(5) unsigned default 0, 
b int(5) unsigned zerofill default 0
)engine=innodb default charset=utf8;
// 可以看到设置了zerofill之后默认值和插入值的不同：显示结果位数不够会用0填充
// 同时说明int(5)括号内的5指的是显示宽度为5
mysql> desc tzerofill;
+-------+--------------------------+------+-----+---------+-------+
| Field | Type                     | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+-------+
| a     | int(5) unsigned          | YES  |     | 0       |       |
| b     | int(5) unsigned zerofill | YES  |     | 00000   |       |
+-------+--------------------------+------+-----+---------+-------+
mysql> insert into tzerofill (a,b) values (10,10);
mysql> insert into tzerofill (a,b) values (10,123456);
mysql> select * from tzerofill;
+------+--------+
| a    | b      |
+------+--------+
|   10 |  00010 |
|   10 | 123456 |
+------+--------+

5 主键

关键字：primary key
功能：表中特定的唯一标识，来表示唯一性，可以根据主键来提取唯一的记录

mysql> create table if not exists tpkey( 
id int unsigned primary key comment '用户id，主键', 
name varchar(16) not null 
)engine=innodb default charset=utf8;
// 可以看到设置主键的id自动设置成不能为空了
mysql> desc tpkey;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | NO   | PRI | NULL    |       |
| name  | varchar(16)      | NO   |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
mysql> insert into tpkey (id,name) values (10,'张三');
mysql> insert into tpkey (id,name) values (10,'李四');//插入失败，主键重复 ERROR 1062 (23000): Duplicate entry '10' for key 'PRIMARY'
mysql> insert into tpkey (id,name) values (20,'张三');
mysql> select * from tpkey;
+----+--------+
| id | name   |
+----+--------+
| 10 | 张三   |
| 20 | 张三   |
+----+--------+

复合主键

主键的一种，复合主键的所有插入选项都相同，才称为主键冲突
primary key(column1,column2);

6 自增长

auto_increment：当对应的字段，不给值，会自动的被系统触发，系统会从当前字段中已经有的最大值+1操作，得到一个新的不同的值。通常和主键搭配使用，作为逻辑主键，一张表只能有一个自增长
设置的同时需要指定primary key或unique

7 唯一键

unique，不可重复，可以为null（null：没有字段）
当没有指定主键时，unique + not null = primary key
主键更多的是标识唯一性的。而唯一键更多的是保证在业务上，不要和别的信息出现重复

mysql> create table tunique( id int primary key, sno char(8) unique not null);
mysql> desc tunique;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
| sno   | char(8) | NO   | UNI | NULL    |       |
+-------+---------+------+-----+---------+-------+

mysql> create table tunique2(sno char(8) unique not null);
mysql> desc tunique2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| sno   | char(8) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+

mysql> create table tunique3( id int unique not null, sno char(8) unique not null);
mysql> desc tunique3;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
| sno   | char(8) | NO   | UNI | NULL    |       |
+-------+---------+------+-----+---------+-------+

mysql> create table tunique4( id int unique not null, sno char(8) primary key);
mysql> desc tunique4;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | UNI | NULL    |       |
| sno   | char(8) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+

8 外键

foreign key，定义主表和从表的关系，外键约束主要定义在从表上，主表则必须是有主键约束或unique约束。当定义外键后，要求外键列数据必须在主表的主键列存在或为null
含义：从表需要通过外键依附于主表

// references后填写主表的表名和列名
foreign key columnname references tablename(columnname);

外键：
外键是用来形成表和表关系的字段，两张表通过外键产生了逻辑上的关联
外键约束：
mysql在操作上要维护外键产生的逻辑关系的正确性，完整性，强约束两表之间的关系