Geek asks and answers

Is it possible to have a table with referential integrity?

Is it possible to have a table with round integrity reference keys? In the example, if I had a table namedContainer

ObjectId  ParentId
1         1
2         1
3         2

ObjectId 1 refers to itself. Identifiers 2 and 3 refer to their respective parents, which are also in the same table. It would be impossible to delete 3 without deleting 2, 2 without deleting 1, and it would be impossible to delete 1.

I know I could do the same with a cross-reference table like

   ObjectId  ContainerId
   1         1
   2         2
   3         3

   ContainerId  ObjectId
   1            1
   2            1
   3            3

But I'm interested in the first way to achieve more, as it will eliminate the possibly unnecessary table. Is it possible?

+3
source share
4 answers

. , , SQL . SQL SQL, , , Joe Celko "Nested Sets" - . .

'parentID' :

  • ?
  • ?

- .

+3

, .

.

( ).

+4

The goal, George, is not to eliminate the unnecessary table when using the homing nested approach. Rather, he should process a hierarchy whose depth is not known in advance: the boss of the boss is the boss. Who knows how deep this organizational tree can go? If you know in advance the depth of the hierarchy and are not subject to frequent changes, it is better for you to maintain separate tables, because writing queries against nested sets is best to avoid a headache. Simplicity is better than complexity.

0
source

More articles:


All Articles