CODEWITHSUNDEEP
sqlsql-serverdatabase-designforeign-keys
David Starkey
David Starkey

Reputation: 1840

SQL Server FK same table

I'm thinking of adding a relationship table to a database and I'd like to include a sort of reverse relation functionality by using a FK pointing to a PK within the same table. For example, Say I have table RELATIONSHIP with the following:

ID (PK)    Relation      ReverseID (FK)
1          Parent        2
2          Child         1
3          Grandparent   4
4          Grandchild    3
5          Sibling       5

First, is this even possible? Second, is this a good way to go about this? If not, what are your suggestions?

Upvotes: 0

Views: 213

Answers (1)

Cade Roux
Cade Roux

Reputation: 89741

1) It is possible.

2) It may not be as desirable in your case as you might want - you have cycles, as opposed to an acyclic structure - because of this if your FK is in place you cannot insert any of those rows as they are. One possibility is that after allowing NULLs in your ReverseID column in your table DDL, you would have to INSERT all the rows with NULL ReverseID and then doing an UPDATE to set the ReverseID columns which will now have valid rows to reference. Another possibility is to disable the foregin key or don't create it until the data is in a completely valid state and then apply it.

3) You would have to do an operation like this almost every time, and if EVERY relationship has an inverse you either wouldn't be able to enforce NOT NULL in the schema or you would regularly be disabling and re-enabling constraints.

4) The sibling situation is the same.

I would be fine using the design if this is controlled in some way and you understand the implications.

Upvotes: 2

Related Questions