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

databasedatabase-designforeign-keys

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?

Best Answer

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.