参照完整性

最新推荐文章于 2023-11-01 17:06:58 发布

roger_lmj

最新推荐文章于 2023-11-01 17:06:58 发布

阅读量4.5k

点赞数 1

分类专栏：数据库文章标签：数据库 duplicates table mysql warnings insert

数据库专栏收录该内容

56 篇文章 0 订阅

订阅专栏

单的说就是表间主键外键的关系。

参照完整性属于表间规则。对于永久关系的相关表，在更新、插入或删除记录时，如果只改其一不改其二，就会影响数据的完整性：例如修改父表中关键字值后，子表关键字值未做相应改变；删除父表的某记录后，子表的相应记录未删除，致使这些记录称为孤立记录；对于子表插入的记录，父表中没有相应关键字值的记录；等等。对于这些设计表间数据的完整性，统称为参照完整性。

MySQL数据库开发中的外键与参照完整性

来源： zdnet　 作者： Builder　 时间： 2006-10-09 Tag： 点击： <script src="/plus/count.php?aid=37685&mid=&view=yes"></script>

参照完整性(Referential integrity)是数据库设计中一个重要的概念。在系统不同的列表中，当数据库所有参照合法或非合法关联时都会涉及到参照完整性。当参照完整性存在时，任何与不存在记录的关联将变得无效化，由此可防止用户出现各种错误，从而提供更为准确和实用的数据库。

　　参照完整性通常通过外键(foreign key)的使用而被广泛应用。长久以来，流行工具开源RDBMS MySQL并没有支持外键，原因是这种支持将会降低RDBMS的速度和性能。然而，由于很多用户对参照完整性的优点倍感兴趣，最近MySQL的不同版本都通过新InnoDB列表引擎支持外键。由此，在数据库组成的列表中保持参照完整性将变得非常简单。

　　为了建立两个MySQL表之间的一个外键关系，必须满足以下三种情况：

两个表必须是InnoDB表类型。
使用在外键关系的域必须为索引型(Index)。
使用在外键关系的域必须与数据类型相似。

　　例子是理解以上要点的最好方法。如表A所示，建立两个表，其中一个列出动物种类及相应的代码(表名为：species)，另一表列出动物园中的动物(表名为：zoo)。现在，我们想通过species关联这两个表，所以我们只需要接受和保存zoo表中包含species表中的合法动物的入口到数据库中。

　　表A

mysql> CREATE TABLE species

            (id TINYINT NOT NULL AUTO_INCREMENT, name VARCHAR(50) NOT NULL,

            PRIMARY KEY(id)) ENGINE=INNODB;

            Query OK, 0 rows affected (0.11 sec)

            mysql> INSERT INTO species VALUES (1, 'orangutan'), (2, 'elephant'), (3, 'hippopotamus'), (4, 'yak');

            Query OK, 4 rows affected (0.06 sec)

            Records: 4 Duplicates: 0 Warnings: 0

            mysql> CREATE TABLE zoo (id INT(4) NOT NULL,

            name VARCHAR(50) NOT NULL, FK_species TINYINT(4) NOT NULL,

            INDEX (FK_species),

            FOREIGN KEY (FK_species) REFERENCES species (id),

            PRIMARY KEY(id)) ENGINE=INNODB;

注意:对于非InnoDB表， FOREIGN KEY 语句将被忽略。

　　现在，fieldszoo.species 与species.id 之间存在一个外键关系。只有相应的zoo.specie 与species.id field的一个值相匹配，动物表中的入口才可被访问。以下的输出即演示了当你想输入一个Harry Hippopotamus记录，而使用到不合法的species代码：

mysql> INSERT INTO zoo VALUES (1, 'Harry', 5);



            ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails

这里，MySQL核查species 表以查看species代码是否存在，如果发现不存在，就拒绝该记录。当你输入正确代码的，可以与以上做比较。

mysql> INSERT INTO zoo VALUES (1, 'Harry', 3);



            Query OK, 1 row affected (0.06 sec)

　　这里，MySQL核查species 表以查看species代码是否存在，当发现存在，允许记录保存在zoo表中。

　　为了删除一个外键关系，首先使用SHOW CREATE TABLE找出InnoDB的内部标签，如表B所示：

表 B

+-------+---------------------------------------------------+



            | Table | Create Table |



            +-------+---------------------------------------------------+



            | zoo | CREATE TABLE `zoo` (



            `id` int(4) NOT NULL default '0',



            `name` varchar(50) NOT NULL default ',



            `FK_species` tinyint(4) NOT NULL default '0',



            KEY `FK_species` (`FK_species`),



            CONSTRAINT `zoo_ibfk_1` FOREIGN KEY (`FK_species`) 



            REFERENCES `species` (`id`)



            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |



            +-------+----------------------------------------------------+

　　然后使用带有DROP FOREIGN KEY 语句的ALTER TABLE 命令，如以下：

mysql> ALTER TABLE zoo DROP FOREIGN KEY zoo_ibfk_1;



            Query OK, 1 row affected (0.11 sec)



            Records: 1 Duplicates: 0 Warnings: 0

　　为了将一个外键添加到一个现成的表中，使用ADD FOREIGN KEY 的 ALTER TABLE 语句指定合适的域作为一个外键：

mysql> ALTER TABLE zoo ADD FOREIGN KEY (FK_species) REFERENCES species (id);



            Query OK, 1 rows affected (0.11 sec)



            Records: 1 Duplicates: 0 Warnings: 0

　　如以上例子解释的，外键在捉摸数据入口错误上起着重要的作用，由此可建立更为强健更加集成的数据库。另一方面值得提到的是，执行外键核实是内部资料处理的过程，且不同表之间指定复杂的内部关系可以导致数据库的性能下降。所以，在参照完整性与性能考虑之间找到平衡点相当重要，而使用外键就是能够确保性能与稳健之间的最优结合。

　　我期望本期的有关外键的介绍对你有所好处，你将会在下回的MySQL数据库设计中感受到外键的好处。编程快乐！

（）

使用参照完整性来加强基本商业规则

添加时间:2007-4-22　

对于数据库系统来说，拥有一套执行基本商业规则的方法是非常重要的。如果一个事物元试图插入，更新，或者删除一个表格行，那么它就违反了商业规则，然后，数据库必须拒绝这个事物元并在应用程序中做错误的标记。虽然通过程序代码来规范规则是可行的，但是如果有一个更加简单的方法来执行那是最合人意了。因为表格可以通过SOL*PLUS和其他有效的方法来访问，所以数据库水平可以通过提供更好的解决方案来约束。

Oracle提供了大量的约束完整性和数据库的触发器来帮助商业规则的管理。让我们暂时先来探索一些约束完整性，然后再将焦点放到外码的约束上，这个将是我们这篇文章的主要内容。

参照完整性（RI）的规则确定了一对多和多对多的关系强迫在关系计划中。另外，有效值同样也被约束强迫。对于Web服务器应用程序来说，约束特别重要，因为Web服务器允许约束在数据库服务器中检查发生，因此，它担当了校对约束的任务。

在Oracle表格中可以应用几种类型的约束来规范数据的完整性，它包括：

·检查约束：在行插入时间的时候，这个约束被有效的引入到列中。在检验region所有出现的地方时，需要使用一个应用程序来检查是东，南，西，或者北，现在，将检查约束加入到表格说来定义确保region栏的有效性。

·非空约束：这个约束被用作说明一个列中不包含一个空值。在SQL insert和update时间中这个是被强迫加入的。

·主码约束：这个约束是用来确定表格的主码。这个操作需要原来的列是唯一的，并且，Oracle将在目标主码上创建一个唯一的索引。

·参考约束：这是外键约束由Oracle来完成。一个参考约束仅仅应用在SQL 的插入和删除时间里。比如，一个一对多的关系存在于EMPLOYEE和DEPENDENT表格中，每个职工可能有许多相依赖的关系，但是每个关系仅仅属于一个职工。这个参考约束在insert时间告诉Oracle在DEPENDENT.emp_num中的值必须和职员栏中的EMPLOYEE.emp_num相匹配，因此，在关系栏添加之前一定要确保有效职员存在。如果这个行依旧存在于DEPENDENT表格中，那么在SQL delete时间里参照约束可以用来确定职员不被删除。

·唯一约束：这个约束确保表格中所有列的值不会出现在复写条目中。

注意唯一和主键的区别，它们都是创建一个唯一的索引，一个表格仅含有一个主键约束列，但是，它有可能在其他列中含有许多的唯一约束。

外键约束

参照完整性，外键约束确定了表格或者视图的栏作为外键。这个外键栏和一个主键或者唯一键在另外一个表格中建立了直接的关系。这个表格包含的外键被引用做孩子，并且参考键被用做双亲表格。外键和参考键可以被用在同一个表格中。除此之外，双亲和子表必须在相同的数据库中。

下面是创建一个外键约束的语法：

　　

　　ALTER TABLE (table_name)

　　　ADD

　　　　CONSTRAINT (foreign key constraint name)

　　　　FOREIGN KEY

　　　　( field name )

　　　　REFERENCES primary_table_name

　　　　(primary_table_primary_index_field)

虽然外键约束通常被用在应用程序编码的场所来进行完整性的检查，但是使用参考完整性的约束加入到I/O开销中来执行insert和update操作是重要的。另外的I/O是可归于外键必须使双亲表有效的事实。通过保证参考列表被检索可使这个开销最小，在大量的重载中，它在使先前的完整约束丧失能力重载和在重载完成后使约束恢复能力的过程中非常有用。一旦约束被激活，任何错误都可被纪录和操作。

参照完整性有时候需要双重编码。比如，在多重SQL*Form中，你也许不知道参照完整性是否违反了规则直到你将许多页面放到表格中并且你的表格试图提交的事物元的时候。在Web服务器应用程序中，你需要非常小心的保证Oracle事物元的安全。

一个数据库系统要有能力执行商业规则。参照完整性是Oracle提供给商业规则的一个方法。有关系统允许商业规则利用约束来控制并参照完整性成了关联表格的骨干。

许多应用程序不使用外键参考完整性，它们利用应用程序代码来执行商业规则。这个方法并不简单因为应用表格可以通过其他的方法比如SQL*PLUS被访问。

（）

参照完整性的原则要求子表的外键值或者为“NULL”，或者与父表的主键值精确匹配。

例如，参照完整性约束可能要求所有雇员都被指派到存储在另一个表中的某一公司部门。这样，雇员就不会被指派到不存在的部门。

关系的基数说明父表中的多少条记录可与子表中的记录直接相关。基数可以表示为一对一、一对多、多对一或多对多。

基数设置只能显示在使用 IDEF1X 表示法的图表中。

注释关系表示法支持基数，但不以图表形式显示它。

在 IDEF1X 表示法中的基数设置。