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