Should I use a Foreign Key to Show Tree Relationship in SQL
database, database-design, foreign-key-relationship, foreign-keys, sql
Solution
Self-referencing foreign keys happen all the time. E.g. an employee might have another "employee" as his manager, so the manager_id will be a foreign key to the employee_id field in the same table.
Foreign keys are the natural candidate for representing the parent node in hierarchical data, although they're not exclusively used for that :)
Problem
I am trying to model a tree relationship in a table. For instance, there are "Categories" and categories can themselves be inside a Parent category. My schema is: ``` id int PRIMARY KEY, parent_id int, name ``` My question is, should I label the parent_id column as a Foreign key? Foreign implies "outside" and not self-referencing. Is there a different type of key for this purpose? My question is similar to: Self-referencing constraint in MS SQL, but I'm asking a different question, cascading not being an issue.