MySQL-表的约束
表的约束
约束条件都是可选的，主要有四类：
# NOT NULL ：非空约束，指定某列不能为空；
# UNIQUE : 唯一约束，指定某列或者几列组合不能重复
# PRIMARY KEY ：主键，指定该列的值可以唯一地标识该列记录
# FOREIGN KEY ：外键，指定该行记录从属于主表中的一条记录，主要用于参照完整性
NULL和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 | |
+-------+---------+------+-----+---------+-------+
1 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)
DEFAULT
默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值，常常和not null配合使用
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
唯一约束，指定某列或者几列组合不能重复，两种方法设置
方法一：
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)
);
not null 和 unique组合达到主键的效果
联合唯一
create table service(
id int primary key auto_increment,
name varchar(20),
host varchar(15) not null,
port int not null,
unique(host,port) #联合唯一
);
PRIMARY KEY
主键保证表中的每一条数据的该字段都是表格中的唯一值。
换言之，它是用来独一无二地确认一个表格中的每一行数据。
主键可以在建置新表格时设定 (运用 CREATE TABLE 语句)，
或是以改变现有的表格架构方式设定 (运用 ALTER TABLE)。
主键必须唯一，主键值非空；可以是单一字段，也可以是多字段组合。
单字段主键
#方法一：not null+unique
create table department1(
id int not null unique, #主键
name varchar(20) not null unique,
comment varchar(100)
);
#方法二：在某一个字段后用primary key
create table department2(
id int primary key, #主键
name varchar(20),
comment varchar(100)
);
#方法三：在所有字段后单独定义primary key
create table department3(
id int,
name varchar(20),
comment varchar(100),
primary key(id); #创建主键并为其命名pk_name
# 方法四：给已经建成的表添加主键约束
alter table department4 modify id int primary key;
多字段主键(联合主键)
create table service(
ip varchar(15),
port char(5),
service_name varchar(10) not null,
primary key(ip,port)
);
AUTO_INCREMENT
约束字段为自动增长，被约束的字段必须同时被key约束
#不指定id的时候，则自动增长
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);
对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长
应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它
指定自增起始值
#在创建完表后，修改自增字段的起始值
alter table student auto_increment=3;
#也可以创建表时指定auto_increment的初始值，注意初始值的设置为表选项，应该放到括号外
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
)auto_increment=3;
offset：偏移，步长
步长:auto_increment_increment,
起始偏移量:auto_increment_offset
auto_increment_offset的值不能大于auto_increment_increment的值
show session variables like 'auto_inc%';
#基于会话级别
set session auth_increment_increment=2 #修改会话级别的步长
#基于全局级别的
set global auth_increment_increment=2 #修改全局级别的步长（所有会话都生效）
FOREIKEY
#表类型必须是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;
ondelete类型
. cascade方式
在父表上update/delete记录时，同步update/delete掉子表的匹配记录
. set null方式
在父表上update/delete记录时，将子表上匹配记录的列设为null
要注意子表的外键列不能为not null
. No action方式
如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作
. Restrict方式
同no action, 都是立即检查外键约束
. Set default方式
父表有变更时,子表将外键列设置成一个默认的值 但Innodb不能识别
