oracle 数据库中是否可以接受循环引用？

Are circular references acceptable in database?

提问by yli

When are circular references acceptable in database?

数据库中什么时候可以接受循环引用？

Theoretical and practical, any help is appreciated.

理论和实践，任何帮助表示赞赏。

采纳答案by u7867

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_idto the correct value. While both these records exist you can't erase them, because the user.last_transaction_idpoints to transaction.idand transaction.user_idpoints 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为正确的值。虽然这两个记录都存在，但您无法删除它们，因为user.last_transaction_id指向transaction.id和transaction.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.

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

回答by fisharebest

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) NOT NULL,
  PRIMARY KEY (city),
  FOREIGN KEY (state) REFERENCES state (state)
);

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

First problem - You cannot create these tables as shown, because a foreign key can't reference a column in a table that doesn't (yet) exist. 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，然后分两个阶段插入该数据。例如

--Create state record
INSERT INTO state (state, capital_city) VALUES ('Florida', NULL);

--Create various city records
INSERT INTO city (city, state) VALUES ('Miami', 'Florida');
INSERT INTO city (city, state) VALUES ('Tallahassee', 'Florida');
INSERT INTO city (city, state) VALUES ('Orlando', 'Florida');

--Set one of the cities as the capital
UPDATE state SET capital_city = 'Tallahassee' WHERE state = 'Florida';

回答by Jeffrey Kemp

One of the latest additions to the Oracle hierarchical query syntax - the NOCYCLEkeyword - 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 中。

回答by ConcernedOfTunbridgeWells

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 记录。

回答by Geoff

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.

这将创建一个圆圈，其中最后一个帖子有一个指向类别表的 FK，而类别表有一个返回到最后一个帖子的 FK。

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

就像我说的，我不是很喜欢它，但我已经看到它完成了。

回答by Steven A. Lowe

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

我很少遇到必要的 1:1 关系并强加循环关系

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

请注意，这种关系中的外键字段必须可以为空，否则永远无法从表中删除行

回答by sal

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 部分，则在处理它们时可能会遇到（通常是可以避免的）复杂问题。

回答by TheTXI

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.

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