系统学习------约束条件

约束条件

• 定义：防止不规范的数据存放在数据库中，DBMS自动按照一定的约束条件对数据进行检测，确保数据库中存储的数据正确有效 。

• 约束条件：

  • 非空约束
  • 唯一约束
  • 主键
  • 外键

非空约束

NOT NULL：不可空
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    |       |  
+‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+  
row in set (0.00 sec) 

#不能向id列插入空元素。   
mysql> insert into t12 values (null);  
ERROR 1048 (23000): Column 'id' cannot be null 
mysql> insert into t12 values (1);  
Query OK, 1 row affected (0.01 sec)

唯一约束

• 约束，指定某列或者几列组合不能重复
  方法一：

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'

联合唯一：当唯一约束为多个字段的时候，必须多个字段同时满足不能重复条件，允许其中一个重复。

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  Warnings: 0 

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

上面要求host和port联合唯一，1、2字段的端口都是80但host不同，所以是允许的。

PRIMARY KEY主键

主键：PRIMARY KEY : 唯一约束 + not null

补充：
步长:auto_increment_increment，起始偏移量：auto_increment_offset

#临时会话有效；如果想要永久配置可以写入到配置文件中；通过global也可以完成全局会有有效。

set auto_increment_increment=2
set auto_increment_offset=2;

create table t7(id int PRIMARY key auto_increment,name char(4));
insert into t7(name) values('lisi'),('zhangsan'),('wangwu');
select * from t7;
drop table t7;

• increment和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=utf8 

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

如果auto_increment_offset的值大于auto_increment_increment的值，则auto_increment_offset的 值会被忽略 ，这相当于第一步步子就迈大了，扯着了蛋 比如：设置auto_increment_offset=3，auto_increment_increment=2

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

FOREIGN KEY外键

外键：FOREIGN key： 表示其他表中的一条记录
为什么有外键：1. 节省表的存储空间 2. 表之间关系通过外
示例三：员工信息表中的部分字段，重复并且浪费资源

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    |       |  
+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+  
rows in set (0.00 sec) 

mysql> alter table departments modify dep_id int(4) unique;  
Query OK, 0 rows affected (0.01 sec)  Records: 0  Duplicates: 0  Warnings: 0 
mysql> desc departments;                                                                               
+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+  
| Field    | Type        | Null | Key | Default | Extra | 
+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+  
| dep_id   | int(4)      | YES  | UNI | NULL    |       |  
| dep_name | varchar(11) | YES  |     | NULL    |       |  
+‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐+‐‐‐‐‐+‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐+  
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)

注意： 当设置字段为unique唯一字段时，设置该字段为外键成功 ，否则会失败。