表完整性约束

介绍

认识：约束条件和数据类型宽度一样，都是可选类型

作用：用于保证数据的完整性、一致性

有哪几种约束：

primary key (PK)：标识该字段为该表的主键，可以唯一标识的记录
foreign key (FK)：标识该字段为该表的外键
not null：标识该字段不能为空，必须赋值
unique key (UK)：标识该字段的值是唯一的
auto_increment：标识该字段的值自动增长(整数类型，而且为主键)
default：为该字段设置默认值，如果插入时不给该字段设置值，此字段使用默认值。

unsigned：表示无符号
zerofill：表示使用0填充

例子：
sex enum('male','female') not null default 'male'  不允许为空，默认是male
age int unsigned NOT NULL default 20 必须为正值（无符号） 不允许为空 默认是20

not null 和 default

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

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

mysql> create table t1(
    -> id int not null default 2, 
    -> name char(18) not null);
mysql> insert into t1 values(1,'游帅');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+----+--------+
| id | name   |
+----+--------+
|  1 | 游帅   |
+----+--------+
1 row in set (0.00 sec)

练习：

往表中插入数据的时候  可以指定字段进行插入 不需要全部都插
insert into table1(name,id) values('egon',2);

mysql> create table student(
    -> name varchar(10) not null,
    -> age tinyint unsigned not null,
    -> sex enum('male', 'female'),
    -> hobby set('a', 'b', 'c', 'd') default 'a,b');
Query OK, 0 rows affected (0.02 sec)
===================================================================
mysql> insert into student values('游爹',18,'male','a,c,b');
Query OK, 1 row affected (0.00 sec)
=================================================================
mysql> select * from student;
+--------+-----+------+-------+
| name   | age | sex  | hobby |
+--------+-----+------+-------+
| 游爹   |  18 | male | a,b,c |
+--------+-----+------+-------+
1 row in set (0.00 sec)

unique:唯一

单列唯一：限制某一个字段是唯一的

=====================方法1==========================
mysql> create table t1(
    -> name varchar(20) unique
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t1 values('游爹');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values('游爹');
ERROR 1062 (23000): Duplicate entry '游爹' for key 'name'
mysql> select * from t1;
+--------+
| name   |
+--------+
| 游爹   |
+--------+
1 row in set (0.00 sec)
=====================方法2==========================
方法二：
create table department2(
id int,
name varchar(20),
comment varchar(100),
constraint uk_name unique(name)
);

联合唯一：在语句的最后 用括号的形式 表示哪几个字段组合的结果是唯一的

mysql> create table server(
    -> id int primary key,
    -> ip varchar(15),
    -> port tinyint, 
    -> unique(ip,port)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into server values(1,'127.0.0.1',8080);
ERROR 1264 (22003): Out of range value for column 'port' at row 1
mysql> insert into server values(1,'127.0.0.1',10);
Query OK, 1 row affected (0.01 sec)

mysql> select * from server;
+----+-----------+------+
| id | ip        | port |
+----+-----------+------+
|  1 | 127.0.0.1 |   10 |
+----+-----------+------+
1 row in set (0.00 sec)

mysql> insert into server values(2,'127.0.0.1',10);
ERROR 1062 (23000): Duplicate entry '127.0.0.1-10' for key 'ip'

primary key

限制效果跟 not null + unique 组合效果一致 非空且唯一

单列做主键

==================方法一：not null+unique
create 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 key
create 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 key
create table department3(
id int,
name varchar(20),
comment varchar(100),
constraint pk_name primary key(id); #创建主键并为其命名pk_name

mysql> 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)

多列做主键

==================多列做主键================
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  Warnings: 0

mysql> insert into service values ('172.16.45.10','3306','nginx');
ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'

primary key也是innodb引擎查询必备的索引，索引你就把当成书的目录
innodb引擎在创建表的时候 必须要有一个主键
当你没有指定主键的时候
1.会将非空且唯一的字段自动升级成主键
2.当你的表中没有任何的约束条件  innodb会采用自己的内部默认的一个主键字段
    该主键字段你在查询时候是无法使用的
    查询数据的速度就会很慢
    类似于一页一页的翻书      
    create table t19(
        id int,
        name char(16),
        age int not null unique,
        addr char(16) not null unique
    );
            
主键字段到底设置给谁呢???
    通常每张表里面都应该有一个id字段
    并且应该将id设置为表的主键字段
联合主键：多个字段联合起来作为表的一个主键，本质还是一个主键!!!
!!!!!!!!!!!!!!!!!ps:innodb引擎中一张表有且只有一个主键!!!!!!!!!!!!!!!!!!!!!!!

auto_increment

主键字段应该具备自动递增的特点
每次添加数据  不需要用户手动输入
auto_increment  自动递增

create table t21(id int primary key auto_increment,name varchar(16));
create table t22(id int primary key,name varchar(16));

mysql> create table t1(
    -> id int auto_increment primary key,
    -> name varchar(100)
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> desc t1
    -> ;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> insert into t1 values(1, '游爹');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+----+--------+
| id | name   |
+----+--------+
|  1 | 游爹   |
+----+--------+
1 row in set (0.00 sec)