Possible to have a table with referential integrity to itself?

Is it possible to have a table with circular referential integrity keys to itself? In example, if I had a table called Container

ObjectId  ParentId
1         1
2         1
3         2

ObjectId 1 references itself. Id's 2 and 3 reference their respective parents, which are also in the same table. It wouldn't be possible to delete 3 without deleteing 2, 2 without deleting 1, and it would be impossible to delete 1.

I know I could accomplish the same thing by having a cross reference table, such as,

   ObjectId  ContainerId
   1         1
   2         2
   3         3

   ContainerId  ObjectId
   1            1
   2            1
   3            3

开发者_开发百科

But I'm interested in the first way of accomplishing it more, as it would eliminate a possibly unnecessary table. Is this possible?

---

Yes, self referencing tables are fine.

They are the classical way to represent deeply nested hierarchies.

Just set a foreign key from the child column to the parent column (so, a value in the child must exist in the parent column).

---

I have done this many times. But be aware if you really are managing hierachies of data, SQL isn't good at tree-like queries. Some SQL vendors have SQL extensions to help with this that might be usable, but Joe Celko's 'Nested Sets' is the cat's meow for this. You'll get lots of hits in a search.

Currently I use the nested-sets approach with a self-reference 'parentID' as a short-cut for the references:

• Who is my parent?
• Who are my immediate children?

The rest are nested-sets queries.

---

The first way works, however if you're trying to store an arbitrarily deep tree, the recursive queries will be slow. You could look into storing an adjacency list or a different method (see http://vadimtropashko.wordpress.com/2008/08/09/one-more-nested-intervals-vs-adjacency-list-comparison/).

One thing we do is to store (in a separate table) each object along with all of its successors as well as having a "parent" indicator in the main table, which we use to build the tree in the application.

---

The goal, George, is not to eliminate an unnecessary table when using the self-reference nested set approach. Rather, it is to handle a hierarchy whose depth is not known in advance: your boss's boss's boss's boss. Who knows how deep that organizational tree may go? If you do know the depth of the hierarchy in advance, and it is not subject to frequent change, you would be better served with separate tables because writing queries against nested sets is a headache best avoided. Simplicity is better than complexity.