mysql之外键约束

关于数据的几点说明：

A、约束保证数据的完整性和一致性

B、约束分为表级约束和列级约束，两者之间的区别是约束针对字段的数目的多少来决定的；列级约束只针对一个数据列来约束，表级约束针对两个或者两个以上的数据建立的约束，列级约束既可以在列定义建立约束，也可以在列定义后声明，而表级约束只能在列定义后声明。列级约束使用较多，表级约束使用较少，而NOT NULL 和 DEFAULT这两种约束只有列级约束。 

C、约束的类型：

    NOT NULL(非空约束)、PRIMARY KEY(主键约束)、UNIQUE KEY(唯一约束)、DEFAULT(默认约束)、FOREIGN KEY(外键约束)

关于外键约束是为了保证数据的完整性，一致性，实现一对一或者一对多的关系。

外键约束的要求如下：

1、父表和子表必须使用相同的存储引擎，而且禁止使用临时表

2、数据表的存储引擎必须为InooDB

在my.ini的配置文件中，修改为如下配置

default-storage-engine=INNODB

3、外键列和参照列必须具有相似的数据类型；数字的长度或是否有符号位必须相同，而字符长度可以不同

在test数据库创建数据表

root@localhost test>CREATE TABLE province(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> pname VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.09 sec)

删除数据表如何实现呢？

DROP TABLE table_name

查看创建的数据表是否为InooDB类型

root@localhost test>show create table province;

root@localhosttest>create table  user(
    -> id smallint unsigned primary key auto_increment,
    -> username varchar(10),
    -> pid smallint unsigned,   用来存储省份的编号，作为参照表将参考父表rpovince中的 id 的字段，且数据类型要相似
    -> foreign key (pid) references province (id)   pid是外键，参照 province 中的 id 字段
    -> );
Query OK, 0 rows affected (0.09 sec)

现在有外键的这张表成为子表即是 user 表，父表示所参照的表 即是 provicne 表，外键列为  pid ,参照列为 id 列，那么我们没有为id 创建索引，但是因为我们指定了 id 为主键(外键约束)，而主键在创建时会自动创建索引 ，进行查看一下：

root@localhost test>show index from province;

或者使用，显示不同的方式

root@localhosttest>show index from province\G;

现在来查看子表的索引创建情况：

root@localhosttest>show index from user\G;

此外，可以查看子表的信息

root@localhost test>show create table user;

4、外键列和参照列必须创建索引，如果外键列不存在索引的话，mysql将自动创建索引。

所谓的外键列就是加了FOREIGN KEY的那一列

而外键约束的参照操作(即是在更新表的时候，子表是否进行相应的操作)包括：

1、CASCADE:从父表中删除或者更新且自动删除或者更新子表中匹配的行

2、SET NULL:从父表删除或者更新行,并设置子表中的外键列为 NULL ,如果使用该选项，必须保证子表列没有指定 NOT NULL

3、RESTRICT:拒绝对父表的删除或者更新操作

4、NOT ACTION:标准的 SQL 关键字，在 MYSQL 中与 RESTRICT 相同

root@localhosttest>create table  user1(
    -> id smallint unsigned primary key auto_increment,
    -> username varchar(10),
    -> pid smallint unsigned,
    -> foreign key (id) references province(id) on delete cascade  在删除时进行一种什么的操作
    -> );

对表 user1 进行查看

root@localhost test>show create table user1;

即是在父表中删除或者更新父表中的记录时也同步删除或者更新子表中相应的行，因此在两张表中有相应的记录。必须先在父表中插入记录，然后再在子表中插入记录，因为子表参照的事父表中的信息。

现在在父表中插入记录,由于 provicne 表中只有两个字段，分别是 id 和 省份名称，由于 id 为自动编号，所以只需操作省份名称

root@localhosttest>insert  province(pname) values ('A');
root@localhosttest>insert  province(pname) values ('B');
root@localhosttest>insert  province(pname) values ('C');

进行查看：

root@localhosttest>SELECT * FROM province;

现在在 user1 表中写几条记录

root@localhosttest>insert user1(username,pid) values('Tom',3);
root@localhosttest>insert user1(username,pid) values('Junt,7);
root@localhosttest>insert user1(username,pid) values('Rose',4);

并进行查看，使用命令 select * from user1;

即是 Tom 所在省份的 id 是 3，就是省份的名称

删除表中的记录

delete from table_name  where condition;

root@localhosttest>delete from province where id=3;        删除一行

删除之后，进行查看

故而即是从父表中删除记录时，子表也随着相应的删除记录。

在外键约束当中，在实际的开发过程中，我们很少使用物理的外键约束，很多都去使用逻辑的外键约束，因为物理的外键约束只有INNODB这种引擎才会支持，像我们另外的一种引擎MYISAM的引擎则不支持，反过来说，如果我想创建的数据表，假设存储引擎为MYISAM，而且又想使用外键约束的话，其实是不可能实现的，所以说，我们在实际的项目开发中，我们不去定义物理的外键，所谓的逻辑外键指的是就是我们在定义两张表的结构的时候，我们是按照存在的某种结构的方式去定义，但是不去使用FOREIGN KEY这个关键词来定义。

a、删除部分记录时，使用delete；

b、删除所有记录时保留该表的结构，使用truncate；

c、删除数据表时，使用drop;

查看users表中的记录

由于上文中已经使用了delete删除了记录，这里清除所有记录

root@localhost test>truncate table users;

或者使用delete进行删除，由于delete条件可选，因此更加灵活，而truncate清除了表中的数据而保留表的结构。

root@localhost test>delete from users;

当表不在需要的时候进行删除

root@localhost test>drop table users;