mysql建表语约束范围_mysql 建表选项和约束

1.建表选项

指定列选项：

default：定义列的默认值

当插入一个新行到表中并且没有给该列明确赋值时，如果定义了列的默认值，将自动得到默认值 ；如果没有，则为null。

MariaDB [wxy]> create table bigcome (

-> id int not null primary key,

-> name varchar(128) not null,

-> sex varchar(2) default 'm'

-> );

在INSERT和UPDATE语句中使用DEFAULT关键字显式地给列赋默认值：

insert into bigcome values(1,'bigcome',default);

update bigcome set sex=default where id=1;

函数default(column)可以得到一个列的默认值：

select default(sex) from bigcome;

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

create table wxy ( id int not null primary key  comment '学号', name varchar(20) not null comment '名字');

select column_name,column_comment from information_schema.columns where table_name='wxy';

指定表选项：

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

存储引擎：决定了数据如何存储以及如何访问，还有事务如何处理

如果没有指定存储引擎，则使用默认的存储引擎

show engines;    #查询所有支持的存储引擎

create table pig(name varchar(2) not null) engine='INNODB';

auto_increment：决定当向表中插入第一行时，自增列得到的第一个值是多少

comment：给表添加注释

create table cat (name varchar(20)) comment '猫';

select table_name,table_comment from information_schema.tables where table_name='cat';

2.建表约束

作用：可以为列定义约束(constraint)

约束主要是防止非法数据进入到表中，确保数据的正确性和一致性(统称数据完整性)

约束也可以防止一个表被删除

MySQL中约束保存在information_schema.table_constraints

当使用create table语句、alter table语句时定义约束

not null：

非空约束，指定某列不为空

注意：非空约束只能出现在表对象的列上

unique：

唯一约束，指定某列和几列组合的数据不能重复

唯一约束是指定table的列或列组合不能重复，保证数据的唯一性，约束的列不允许有重复值

唯一约束不允许出现重复的值，但是可以为多个null

同一个表可以有多个唯一约束，多个列组合的约束

primary key：主键约束，指定某列的数据不能重复、唯一

用来唯一的标示表中的每一行(类型一般为整型或者字符串)

具有主键约束的列不允许有null值，并且不允许有重复值

每个表最多只允许一个主键(可定义联合主键)，主键名总是PRIMARY

create table dog ( id int, name varchar(20), constraint pk_id_name primary key (id,name));  #id与name不能同时重复

foreign key：外键，指定该列记录属于主表中的一条记录，参照另一条数据

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

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

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

外键列默认可以给null值

外键必须引用一个主键或者唯一键，引用的主键一般在另外一张表中，也可以是本表的主键(后者称为“自引用”)

父子表：

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

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

父子表是相对而言的，表a可以是表b的子表，但同时也可以是表c的父表

例子：

创建父表：

create table animal ( id int not null, name varchar(20) not null, constraint animal_name_pk primary key(name) );

insert into animal values(1,'dog'),(2,'cat'),(3,'pig');

创建子表：

create table pig(id int not null, name varchar(20) not null, type varchar(20) , constraint cat_animal_fk foreign key(type) references animal(name));

insert into pig values(1,'wxy','pig');

注意：插入子表type字段的变量必须是父表的name字段值

查看外键约束的信息：

select * from information_schema.table_constraints where table_name='pig';

外键的默认删除规则：

当删除父表中的行时，如果子表中有依赖于被删除父行的子行存在，那么就不允许删除，并抛出异常

ON DELETE CASCADE：级联删除

当删除父表中的行时，如果子表中有依赖于被删除父行的子行存在，那么连同子行一起删除(很危险！！！)

ON DELETE SET NULL：

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

create table www(id int not null, name varchar(20) , type varchar(20) , constraint www_animal_fk foreign key(type) references animal(name) on delete cascade);

check：检查，指定一个表达式，用于检验指定数据

MySQL可以使用check约束，但check约束对数据验证没有任何作用

ENUM(enumeration，枚举)和SET(集合)类型：变通的实现CHECK约束

ENUM，只能选一个值

SET，可以选多个值

ENUM和SET中的值都必须是字符串类型