MySQL-第三篇SQL语句基础（2）数据库约束

1、数据库约束。

    约束是在表上强制执行的数据校验规则，约束主要用于保证数据库里数据的完整性。

    MySQL使用information_schema数据库里的TABLE_CONSTRAINTS表来保存该数据库实例中的所有约束信息。

2、常见的数据库完整性约束：

   1》NOT NULL：非空约束。

   2》UNIQUE：唯一约束，指定某些列（一个或者多个）或者几列的组合不能重复。

   3》PRIMARY KEY：主键约束，指定某列的值可以唯一地标识该条记录。

   4》FOREIGN KEY：外键约束，指定该行记录从属于主表中的某一条记录（如某字段的字段值必须是主表某字段存在的值），主要用于保证参照完整性。

   5》CHECK：检查，指定一个布尔表达式，用于指定对应列的值必须满足该表达式。（MySQL不支持这种约束，即使MySQL语句中可以使用CHECK约束，但约束并不会起效）

3、约束也是数据库对象，被存储在数据字典（系统表）中。根据约束对数据列的限制，约束分为以下两类：

   1》单列约束：每个约束只约束一列。

   2》多列约束：每个约束可以约束多个数据列。

4、指定约束的两个时机：

   1》建表的同时为相应的数据列指定约束。

   2》建表后创建，以修改表的方式来增加约束。

   大部分约束都可以采用列级约束语法或者表级约束语法。

5、常见的5种约束详解

   1》not  null约束

      只能作为列级约束使用，只能使用列级约束语法定义。

      SQL中的null不区分大小写，所有数据类型都可以是null，空字符串不等于null，0不等于null，null也不等于null。

   2》unique约束

      指定某些列（一个或者多个）或者几列的组合不能重复（但可以出现多个null值，因为null也不等于null）。

      当为某列创建唯一约束时，MySQL会为该列相应地创建唯一索引。如果不给唯一约束起名，该唯一约束默认与列名相同。

      唯一约束既可以使用列级约束语法建立，也可以使用表级约束语法建立。如果需要为多列建组合唯一约束，或者需要为唯一约束指定约束名，则只能用表级约束语法。

      表级约束语法格式：该约束语法格式既可以放在create table语句中与列定义并列，也可以放在alter table语句中使用add关键字来添加约束。

[constraint 约束名] 约束定义

      例子：

--单列唯一约束
create table emp
(
  --使用列级约束
  e_id   varchar(255) unique,
  e_name varchar(255),
  e_pass varchar(255),
  --使用表级约束语法建立唯一约束
  unique (e_name),
  --使用表级约束语法建立唯一约束，而且指定约束名
  constraint un_emp_pass unique(e_pass)
);

create table emp
(
  --使用列级约束
  e_id   varchar(255) unique,
  e_name varchar(255),
  e_pass varchar(255),
  --使用表级约束语法建立唯一约束，指定两列组合不允许重复，而且指定约束名
  constraint un_emp_name_pass unique(e_name,e_pass)
);

  修改表结构的时候使用add关键字来增加唯一约束：

alter table emp add unique(e_name,e_pass);

  还可以在修改表时使用modify关键字，为单列采用列级语法来增加唯一约束：

alter table emp modify e_name varchar(255) unique;

   删除索引：删除unique约束，对于大部分数据库而言都是在alter table语句后使用“drop constraint 约束名”，但MySQL并不使用这种方式，而是使用“drop index 约束名”的方式来删除约束。

alter table emp drop index un_emp_name_pass;

   3》primary key约束

      主键约束，相当于非空约束和唯一约束的与。如果多列里包含的每一列都不能为空，但只要求这些列组合不能重复。

      每个表中最多允许有一个主键。建立主键约束时既可以使用列级约束语法，也可以使用表级约束语法。

      如果需要对多个字段建立组合主键约束，则只能使用表级约束语法。使用表级约束语法来建立约束时，可以为该约束指定约束名。但不管用户是否为该主键约束指定约束名，MySQL总是将所有的主键约束名为primary。（MySQL允许在建立主键约束时为该约束命名，但这个名字没有任何作用，主要是为了保持与标准SQL的兼容性）

      例子：

create table emp
(
  --使用列级主键约束
  e_id   varchar(255) primary key,
  e_name varchar(255),
  e_pass varchar(255),
);

create table emp
(
  e_id   varchar(255),
  e_name varchar(255),
  e_pass varchar(255),
  --使用表级主键约束。指定了主键约束名为pk_emp_id，但对MySQL无效。
  --MySQL数据库中该主键约束名依然是primary
  constraint pk_emp_id primary key(e_id)
);

create table emp
(
  e_id   varchar(255),
  e_name varchar(255),
  e_pass varchar(255),
  --使用表级主键约束创建多字段主键
  primary key(e_id,e_name)
);

   删除某表的主键约束：

alter table 表名 drop primary key;

   为某表增加主键约束，可以通过modify关键字修改列定义来增加主键约束，这将采用列级语法来增加主键约束；也可以通过add来增加主键约束，这将采用表级约束语法来增加主键约束。

alter table emp modify e_id varchar(255) primary key;

alter table emp add primary key(e_id,e_name);

    如果主键列（单字段的）是整型，则可指定该列具有自增长功能。指定自增长功能通常用于设置逻辑主键列，即该列的值没有具体物理意义，仅仅用于标识每行记录。MySQL使用auto_increment来设置自增长。

create table emp
(
  --使用列级主键约束。并且是自增长的。
  e_id   int auto_increment primary key,
  e_name varchar(255),
  e_pass varchar(255),
);

   4》foreign key约束

     外键约束主要用于保证一个或者两个数据表之间的参照完整性，外键是构建于一个表的两个字段之间（表内字段参照）或者两个表的两个字段之间（表间字段参照）的参照关系。

     外键确保了相关的两个字段的参照关系：子（从）表外键列的值必须在主键被参照列的值范围之内，或者为空。

     当主表的记录被从表记录参照时，主表记录不允许被删除，必须先把从表里参照该记录的所有记录全部删除后，才可以删除主表的该记录。还有一种方式，删除主表记录时级联删除从表中所有参照该记录的从表记录。

     从表外键参照的只能是主表主键列或者唯一键列，这样才可以保证从表记录可以准确定位到被参照的主表记录。同一个表内可以拥有多个外键。

     建立外键约束时，MySQL也会为该列建立索引。

     外键约束通常用于定义两个实体之间的一对一、一对多的关联关系。对于一对多的关联关系，通常在多的一端增加外键列。对于多对多的关联关系，则需要额外增加一个连接表来记录它们的关联关系。

     建立外键约束同样可以使用列级约束语法和表级约束语法。如果仅对单独的数据列建立外键约束，则使用列级约束语法即可；如果需要对多列组合创建外键约束或者需要为外键约束指定名字，则必须使用表级约束语法。

    采用列级约束语法创建外键约束直接使用references关键字，references指定该列参照哪个主表，以及参照主表的哪一列。但这种列级约束创建外键的方式并不会使外键生效。

create table teacher
(
  --使用列级主键约束
  t_id   int auto_increment primary key,
  t_name varchar(255),
  t_pass varchar(255),
);

create table student
(
  s_id   int auto_increment primary key,
  s_name varchar(255),
  s_pass varchar(255),
  --使用列级约束创建外键
  teacher_id int references teacher(t_id)
);

    如果要想使MySQL中的外键约束生效，则应使用表级约束语法。使用表级约束创建外键，需要使用foreign key来指定本表的外键列，并使用references来指定参照哪个主表，以及参照到主表的哪个数据列。

create table teacher
(
  --使用列级主键约束
  t_id   int auto_increment primary key,
  t_name varchar(255),
  t_pass varchar(255),
);

create table student
(
  s_id   int auto_increment primary key,
  s_name varchar(255),
  s_pass varchar(255),
  --使用表级约束创建外键
  teacher_id int,
  foreign key(teacher_id) references teacher(t_id)
);

    使用表级约束语法可以为外键约束指定约束名，如果创建外键约束时没有指定约束名，则MySQL会为该外键约束命名为table_name_ibfk_n，如上面的创建方式。其中table_name是从表的表名，而n是从1开始的整数。如果需要显式指定外键约束的名字，则使用constraint来指定名字。

create table teacher
(
  --使用列级主键约束
  t_id   int auto_increment primary key,
  t_name varchar(255),
  t_pass varchar(255),
);

create table student
(
  s_id   int auto_increment primary key,
  s_name varchar(255),
  s_pass varchar(255),
  --使用表级约束创建外键，并创建一个可生效的外键名
  teacher_id int,
  constraint stu_tea_fk_id foreign key(teacher_id) references teacher(t_id)
);

    如果创建多列组合的外键约束，则必须使用表级约束语法。

create table teacher
(
  e_id   int auto_increment,
  e_name varchar(255),
  e_pass varchar(255),
  --使用表级主键约束创建多字段主键
  primary key(e_name,e_pass)
);

create table student
(
  s_id   int auto_increment primary key,
  s_name varchar(255),
  s_pass varchar(255),
  --使用表级约束创建外键，并创建一个可生效的外键名
  teacher_name varchar(255),
  teacher_pass varchar(255),
  constraint stu_tea_fk_name_pass foreign key(teacher_name,teacher_pass) references teacher(t_name,t_pass)
);

   删除外键约束。语法非常简单，在alter table后增加“drop foreign key 约束名”即可。

alter table student drop foreign key student_ibfk_1;

   增加外键约束。通常使用add foreign key命令。

alter table student add foreign key(teacher_name,teacher_pass) references teacher(t_name,t_pass);

    外键约束不仅可以参照其他表（表间参照），还可以参照自身（表内参照），这种参照自身的关联被称作自关联。

create table person
(
  p_id   int auto_increment primary key,
  p_name varchar(255),
  p_fk_id int,
  constraint person_fk_id foreign key(p_fk_id) references person(p_id)
);

   如果想定义外键在发生主表被参考记录删除时，从表记录也会随之删除，则需要在建立外键约束后添加“on delete cascade”或者“on delete set null”，第一种是删除主表记录时，把参照该主表记录的从表记录全部级联删除；第二种是指定当删除主表记录时，把参照该主表记录的从表记录的外键设为null。

create table teacher
(
  --使用列级主键约束
  t_id   int auto_increment primary key,
  t_name varchar(255),
  t_pass varchar(255),
);

create table student
(
  s_id   int auto_increment primary key,
  s_name varchar(255),
  s_pass varchar(255),
  --使用表级约束创建外键，并创建一个可生效的外键名
  teacher_id int,
  constraint stu_tea_fk_id foreign key(teacher_id) references teacher(t_id) 
  on delete cascade     --或者on delete set null
);

   5》check约束

       MySQL支持在建表时指定check约束，但这个约束不会起任何作用。

      语法：

create table emp
(
  e_id   int primary key,
  e_name varchar(255),
  e_pass varchar(255),
  --建立check约束
  check(e_id>0)
);