Search code examples
databasedatabase-designforeign-keys

advantages and disadvantages of making a self-referencing table column a foreign key


What are the advantages and disadvantages of making a self-referencing table column a foreign key?

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


Solution

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