mysql常见的建表选项和约束

create table选项

指定列选项：default

1. 当插入一个新行到表中并且没有给该列明确赋值时，如果定义了列的默认值，将自动得到默认值 ；如果没有，则为null。
2. sex char(1) default ‘m’
3. 当然，也可以在INSERT和UPDATE语句中使用DEFAULT关键字显式地给列赋默认值：
4. 函数default(column)可以得到一个列的默认值：select default(sex) from people;

comment

用来给列加注释，最多255个字符，注释会保存到数据字典中

id int not null primary key

comment '学号'

从数据字典中查询注释信息

select column_name,column_comment

from information_schema.columns

where table_name=’stu_comment’

在CREATE TABLES语句中的表选项

engine:指定表使用的存储引擎

1. 存储引擎：决定了数据如何存储以及如何访问，还有事务如何处理
2. show engines 用来查询所有支持的存储引擎

CREATE TABLE约束

作用：可以为列定义约束

1. 约束主要是防止非法数据进入表中，保证数据的正确性和一致性，统称为数据完整性
2. 约束也可以防止一个表被删除

MySQL的约束保存在information_schema.table_constraints中，可以通过该表查询约束信息

常见的约束类型

1. not null非空，指定某列不为空（注意区分空和空格的关系）
2. unique：唯一约束，指定某列和几列组合的数据不能重复
3. primary key:主键约束，指定某列的数据不能重复，唯一
4. foreign key:外键，指定该列记录属于主表中的一条记录，参照另一条记录
5. check：检查，指定一个表达式，用于检验指定数据

primary key = not null + unique

1. 主键是用来唯一标识表中的每一行，类型一般为整型或者字符串
2. 具有主键约束的列不允许有null值，不允许有重复值
3. 每个表最多只允许一个主键，可定义联合主键，主键名总是PRIMARY

联合主键

用几个列来进行唯一标识一行

 constraint pk_id_name primary_key(id,name)

constraint可以给键进行重命名，但是在数据字典中，主键名还是显示primary

foreign key外键约束

参照完整性约束，保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或者是两个表的两个字段之间的参照关系

注意：

具有外键约束的列的值不能随便给，必须满足外键所引用的主键的取值

一张表中可以定义多个外键

外键列默认可以给null值

父子表

外键所在的表叫做子表，从表

外键所引用的主键所在的表叫做父表，主表

 constraint emp_deptid_fk foreign_key(deptid)
 references dept(deptid)

外键的删除规则

当删除父表中的行时，如果子表中有依赖被删除的父行的子行存在，那么就不允许删除，并抛出异常（默认对外键使用on delete restrict或on delete no action选项）

在定义外键约束时，可以通过使用on delete cascade或on delete set null来改变外加的默认删除规则

on delete cascade：级联删除，当删除父表中的行时，如果子表中有依赖于被删除父行的子行存在，那么联通子行一起删除，相当于rm -f

on delete set null：当删除父表中的行时，如果子表中有依赖于被删除的父行的子行存在，那么不删除，而是将子行的外键设置为null

外键引用定义

 reference_definition:
     REFERENCES tbl_name (index_col_name,...)
     [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
     [ON DELETE reference_option]
     [ON UPDATE reference_option]
 reference_option:
     RESTRICT | CASCADE | SET NULL | NO ACTION

check约束

MySQl中可以使用check约束，但是check约束对数据验证没有任何作用。

Oracle中国可以使用check约束，有相应作用

 mysql> create table test_ck(
     ->   id int check(id>0)
     -> );

 mysql> insert into test_ck values(-100);

 mysql> select * from test_ck;
 +------+
 | id   |
 +------+
 | -100 |
 +------+

也可以使用ENUM和set来变通地实现check约束，ENUM只能选一个值，SET可以选择多个值

AUTO_INCREMENT

1. 自增
2. 缺点：当删除上一条时会在删除的上一条的基础上加1，不会覆盖原来删除的编号
3. 列的数值自动增长，列的类型只能是整数类型
4. 通常给主键添加自增长约束