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. 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 2

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.