循环引用在数据库中可以接受吗?

When are circular references acceptable in database?

在数据库中什么时候循环引用是可接受的?

Theoretical and practical, any help is appreciated.

理论和实际，任何帮助都是值得赞赏的。

8 个解决方案

#1

Records which point to other records are useful in a database. Sometimes these records form a cycle. This might still be useful. The only real annoyance in practice is avoiding violating the constraints.

指向其他记录的记录在数据库中是有用的。有时这些记录形成一个循环。这可能仍然有用。在实践中唯一真正的烦恼是避免违反约束。

For example, if you have a user and transaction table, the user might have a pointer to his last transaction. You need to insert the transaction first, then update the last_transaction_id to the correct value. While both these records exist you can't erase them, because the user.last_transaction_id points to transaction.id and transaction.user_id points to user.id. This implies that a user with no transactions has a null last_transaction_id. It also means that you have to null that field before you can delete the transaction.

例如，如果您有一个用户和事务表，那么用户可能有一个指向他最后一个事务的指针。您需要先插入事务，然后将last_transaction_id更新为正确的值。虽然这两个记录都存在，但是不能删除它们，因为用户。last_transaction_id指向事务。id和事务。user_id指向user.id。这意味着没有事务的用户拥有一个null last_transaction_id。这也意味着在删除事务之前，您必须先空出该字段。

Managing these foreign key constraints is a pain but it certainly is possible. There may be problems that arise if you add constraints to the database later which introduce new circular dependencies. You have to be careful in this situation. However, as long as one of the records in the cycle has a nullable foreign-key field, the cycle can be broken and the records can be deleted. Updates are not usually a problem as long as you insert the records in the right order.

管理这些外键约束是一种痛苦，但这是可能的。如果稍后将约束添加到引入新的循环依赖项的数据库，可能会出现问题。在这种情况下你必须小心。但是，只要循环中的一条记录有一个可空的外键字段，就可以打破循环并删除记录。只要按正确的顺序插入记录，更新通常就不是问题。

#2

Consider cities and states. Each city exists within a state. Each state has a capital city.

考虑城市和州。每个城市都存在于一个州内。每个州都有一个首都。

CREATE TABLE city (
  city  VARCHAR(32),
  state VARCHAR(32),
  PRIMARY KEY (city),
  FOREIGN KEY (state) REFERENCES state (state)
);

CREATE TABLE state (
  state VARCHAR(32),
  captial_city VARCHAR(32),
  PRIMARY KEY (state),
  FOREIGN KEY (captial_city) REFERENCES city (city)
);

First problem - You cannot create these tables as shown. The solution is to create them without the foreign keys, and then add the foreign keys afterwards.

第一个问题——您不能创建如下所示的表。解决方案是在没有外键的情况下创建它们，然后添加外键。

Second problem - you cannot insert rows into either table, as each insert will require a pre-existing row in the other table. The solution is to set one of the foreign key columns to be NULL, and insert that data in two phases. e.g.

第二个问题——您不能将行插入到任何一个表中，因为每个插入都需要另一个表中已有的行。解决方案是将一个外键列设置为NULL，并在两个阶段中插入该数据。如。

INSERT INTO city (city, state) VALUES ('Miami', NULL);
INSERT INTO state (state, capital_city) VALUES ('Florida', 'Miami');
UPDATE city SET state='Florida' WHERE city='Miami';

#3

It is technically possible to do, but it can cause all sorts of problems when deleting records as it generates chicken-and-egg problems. These problems often take drastic action like manually dropping the FK's and deleting the offending items to resolve.

这在技术上是可行的，但是当删除记录时，它会引起各种问题，因为它会产生鸡和蛋的问题。这些问题通常采取激烈的行动，比如手动删除FK，删除要解决的问题项。

If you have a relationship like:

如果你有这样的关系:

create table foo_master (
       foo_master_id int not null primary key
      ,current_foo_id int
)


create table foo_detail (
       foo_detail_id int not null primary key
       foo_master_id int not null
)

alter table foo_master
  add constraint fk_foo_current_detail
      foreign key (current_foo_id)
      references foo_detail

alter table foo_detail
  add constraint fk_foo_master
      foreign key (foo_master_id)
      references foo_master

Then deleting a record can cause such a chicken-and-agg problem due to the circular dependencies.

然后，由于循环依赖关系的存在，删除一条记录可能会导致这样的问题。

A better schema for this looks like:

更好的模式是:

create table foo_master (
       foo_master_id int not null primary key
)


create table foo_detail (
       foo_detail_id int not null primary key
       foo_master_id int not null
       is_current char (1)
)

alter table foo_detail
  add constraint fk_foo_master
      foreign key (foo_master_id)
      references foo_master

This means that the relationship is non-cyclic and the 'current' foo_detail record can still be identified.

这意味着关系是非循环的，而“当前”的foo_detail记录仍然可以被识别。

#4

One of the latest additions to the Oracle hierarchical query syntax - the NOCYCLE keyword - was made for expressly this purpose - to deal with circular references in the data. I don't see anything wrong with it, and have had to deal with this kind of model before. It's not too difficult, especially in Oracle which supports deferrable constraints.

Oracle分层查询语法的最新补充之一——NOCYCLE关键字——就是为了这个目的而开发的——用于处理数据中的循环引用。我看不出有什么问题，我以前就得处理这种模型。这并不太难，尤其是在支持可延迟约束的Oracle中。

#5

Circular references should be avoided like the plague. It is possible to set up two way relationships, or even relationships to yourself (if you were a table that is), but a circular dependency is just asking for trouble.

循环引用应该像瘟疫一样避免。建立双向关系是可能的，甚至是与你自己的关系(如果你是表的话)，但循环依赖只是自找麻烦。

#6

I have seen circular references done for performance reasons. It looks ugly though, and the performance might be negligible.

我看到过出于性能原因而做的循环引用。虽然看起来很难看，但是性能可能可以忽略不计。

Example: some bulletin boards (I think phpBB does this) have a lastpostid in the category table that is a shortcut to the last post in the thread.

示例:一些公告板(我认为phpBB会这样做)在类别表中有一个lastpostid，它是线程最后一个帖子的快捷方式。

This creates a circle, where the last post has a FK to the category table and the category table has a FK back to the last post.

这将创建一个圆圈，其中最后一个post有FK到category表，而category表有FK返回到最后一个post。

Like I said, I don't really like it, but I've seen it done.

就像我说的，我不太喜欢它，但我见过它。

#7

rarely i run across a 1:1 relationship that is necessary and imposes a circular relationship

我很少遇到必要的一对一关系，并强加一种循环关系。

note that the foreign-key fields in such a relationship must be nullable, otherwise you can never delete rows from the tables

注意，这样的关系中的外键字段必须是可空的，否则永远不能从表中删除行

#8

I guess it isn't a problem if you are using a write only database. If you plan on using the RUD part of CRUD, you'll likely run into (usually avoidable) complex issues in dealing with them.

如果您使用的是只写的数据库，我想这不是问题。如果您打算使用CRUD的RUD部分，您可能会在处理这些问题时遇到(通常可以避免的)复杂的问题。