Should you make a self-referencing table column a foreign key?

Question

For example to create a hierarchy of categories you use a column 'parent_id', which points to another category in the same table.

Should this be a foreign key? What would the dis/advantages be?

Answer 1

Yes, you should make it a foreign key.

The benefits will be a better data model with less redundancy.

Answer 2

Yes. Ensures that you don't have an orphan (entry with no parent), and depending on usage, if you define a cascading delete, when a parent is deleted, all its children will also be deleted.

Disadvantage would be a slight performance hit just like any other foreign key.

Answer 3

Yes, you should. If you have an attribute in a relation of database that serves as the primary key of another relation in the same database you should make it a FK.

You will enjoy the advantages associated to foreign keys:

• Assuming the proper design of the relationships, foreign key constraints make it more difficult for a programmer to introduce an inconsistency into the database.
• Centralizing the checking of these constraints by the database server makes it unnecessary to perform these checks on the application side. This eliminates the possibility that different applications may not check constraints in the same way.
• Using cascading updates and deletes can simplify the application code.
• Properly designed foreign key rules aid in documenting relationships between tables.

The disadvantages:

• If you define Foreign Keys, sometimes it is harder to perform bulk operations.
• Maybe it implies more disk usage and a slight performance hit.

Answer 4

Yes you should.

Advantages (as for any foreign key):

• Ensures that parent_id references a real row in the tabke
• Prevents accidental deletion of a parent that has children, or ensures that the delete cascades to delete the children also
• Provides information the optimiser can use

I can't think of any real disadvantages.