mysql参照完整性.doc

1. 什么是参照完整性?参照完整性(完整性约束)是数据库设计中的一个重要概念，当数据库中的一个表与一个或多个表进行关联时都会涉及到参照完整性。比如下面这个例子：文章分类表 - categoriescategory_id name1 SQL Server2 Oracle3 PostgreSQL5 SQLite文章表 - articlesarticle_id category_id title1 1 aa2 2 bb3 4 cc可见以上两个表之间是通过category_id，其中categories表有4条记录，articles表有3条记录。然而可能因为某种原因我们删掉了categories 表中category_id=4的记录，而articles表却还是有一条category_id=4的记录，很明显，category_id=4的这条记录不应该存在在articles表中，这样会很容易造成数据错乱。相反，外键关系(Foreign Key relationships)讨论的是父表(categories)与子表(articles)的关系，通过引入外键(Foreign Key)这个概念来保证参照完整性(Referential integrity)，将使会数据库变的非常简单。比如，要要做到删除categories表中category_id=4记录的同时删除 articles 表中category_id=4的所有记录，如果没有引入外键的话，我们就必须执行2条SQL语句才行；如果有外键的话，可以很容易的用一条SQL语句就可以达到要求。2. 使用外键的条件MySQL只在v3.23.34版本以后才引入外键的，所以在这之前的版本就别想了:)，除此之外，还必须具备以下几个条件： 1) 在f配置文件中打开InnoDB引擎支持。 # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /var/db/mysql/ innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /var/db/mysql/ innodb_log_arch_dir = /var/db/mysql/ 2) 相关联的表都必须采用InnoDB引擎。 3) 相关联的字段都必须建立所以。 MySQL v4.0版本以后，定义外键时会自动建立所以，所以在 v4.0 版本以前（含v4.0版本）必须手工定义索引。 4) 相关联的字段必须采用类似的数据类型，或者说可转换的数据类型，当然相同类型是最好不过了。 比如父表的字段是TINYINT类型，则子表只能采用TINYINT、SMALLINT、INT、BIGINT等几种类型。3. 外键语法参考可以通过 CREATE TABLE 或者 ALTER TABLE 来定义外键。CREATE TABLE 语法：CREATE TEMPORARY TABLE IF NOT EXISTS tbl_name (create_definition,)create_definition: column_definition | CONSTRAINT symbol FOREIGN KEY index_name (index_col_name,) reference_definitioncolumn_definition: col_name type NOT NULL | NULL DEFAULT default_value AUTO_INCREMENT UNIQUE KEY | PRIMARY KEY COMMENT string reference_definitionindex_col_name: col_name (length) ASC | DESCreference_definition: REFERENCES tbl_name (index_col_name,) MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ON DELETE reference_option ON UPDATE reference_optionreference_option: RESTRICT | CASCADE | SET NULL | NO ACTIONALTER TABLE 语法：ALTER IGNORE TABLE tbl_name alter_specification , alter_specification alter_specification: | ADD CONSTRAINT symbol PRIMARY KEY index_type (index_col_name,) | ADD CONSTRAINT symbol UNIQUE INDEX index_name index_type (index_col_name,) | ADD CONSTRAINT symbol FOREIGN KEY index_name (index_col_name,) reference_definition | DROP FOREIGN KEY fk_symbol4. 定义外键mysql CREATE TABLE categories ( - category_id tinyint(3) unsigned NOT NULL AUTO_INCREMENT, - name varchar(30) NOT NULL, - PRIMARY KEY(category_id) - ) ENGINE=INNODB;Query OK, 0 rows affected (0.36 sec)mysql INSERT INTO categories VALUES (1, SQL Server), (2, Oracle), (3, PostgreSQL), (4, MySQL), (5, SQLite);Query OK, 5 rows affected (0.48 sec)Records: 5 Duplicates: 0 Warnings: 0mysql CREATE TABLE members ( - member_id INT(11) UNSIGNED NOT NULL, - name VARCHAR(20) NOT NULL, - PRIMARY KEY(member_id) - ) ENGINE=INNODB;Query OK, 0 rows affected (0.55 sec)mysql INSERT INTO members VALUES (1, test), (2, admin);Query OK, 2 rows affected (0.44 sec)Records: 2 Duplicates: 0 Warnings: 0mysql CREATE TABLE articles ( - article_id INT(11) unsigned NOT NULL AUTO_INCREMENT, - title varchar(255) NOT NULL, - category_id tinyint(3) unsigned NOT NULL, - member_id int(11) unsigned NOT NULL, - INDEX (category_id), - FOREIGN KEY (category_id) REFERENCES categories (category_id), - CONSTRAINT fk_member FOREIGN KEY (member_id) REFERENCES members (member_id), - PRIMARY KEY(article_id) - ) ENGINE=INNODB;QueryOK, 0 rows affected (0.63 sec)注意：对于非InnoDB表，FOREIGN KEY子句会被忽略掉。如果遇到如下错误：ERROR 1005: Cant create table ./test/articles.frm (errno: 150)请仔细检查以下定义语句，常见的错误一般都是表类型不是INNODB、相关联的字段写错了、缺少索引等等。至此categories.category_id和articles.category_id、members.member_id和articles.member_id已经建立外键关系，只有 articles.category_id 的值存在与 categories.category_id 表中并且articles.member_id的值存在与members.member_id表中才会允许被插入或修改。例如：mysql INSERT INTO articles (category_id, member_id, title) VALUES (6, 1, foo);ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (test/articles, CONSTRAINT articles_ibfk_1 FOREIGN KEY (category_id)REFERENCES categories (id)mysql INSERT INTO articles (category_id, member_id, title) VALUES (3, 3, foo);ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (test/articles, CONSTRAINT fk_member FOREIGN KEY (member_id) REFERENCES members (member_id)可见上面两条语句都会出现错误，因为在categories表中并没有category_id=6、members表中也没有member_id=3的记录，所以不能插入。而下面这条SQL语句就可以。mysql INSERT INTO articles (category_id, member_id, title) VALUES (3, 2, bar);Query OK, 1 row affected (0.03 sec)5. 删除外键定义不知道大家有没有发现，在前面定义外键的时候articles.member_id外键比articles.category_id子句多了一个CONSTRAINT fk_member ？这个fk_member就是用来删除外键定义用的，如下所示：mysql ALTER TABLE articles DROP FOREIGN KEY fk_member;Query OK, 1 row affected (0.25 sec)Records: 1 Duplicates: 0 Warnings: 0这样articles.member_id外键定义就被删除了，但是如果定义时没有指定CONSTRAINT fk_symbol （即外键符号）时该怎么删除呢？别急，没有指定时，MySQL会自己创建一个，可以通过以下命令查看：mysql SHOW CREATE TABLE articles;+-+| Table | Create Table |+-+| articles | CREATE TABLE articles ( article_id int(11) unsigned NOT NULL auto_increment, category_id tinyint(3) unsigned NOT NULL, member_id int(11) unsigned NOT NULL, title varchar(255) NOT NULL, PRIMARY KEY (article_id), KEY category_id (category_id), KEY member_id (member_id), CONSTRAINT articles_ibfk_1 FOREIGN KEY (category_id) REFERENCES categories (id) ENGINE=InnoDB DEFAULT CHARSET=latin1 |+-+1 row in set (0