CODEWITHSUNDEEP
mysql
realtebo
realtebo

Reputation: 25691

how to create foreign keys for parent-child relations on a single table?

Example: 

id | name | parent_id |

As you can imagine, parent_id must point to a valid id in the same table.

I'd like to - disallow parent row deletion if there are any children - disallow child editing of parent_id - if parent_id for some reason would change, all child rows must be updated on field parent_id

how to create the foreign keys?

Upvotes: 0

Views: 36

Answers (1)

Stan
Stan

Reputation: 1430

Think should be something like following

create contraint `my_foreign_key`
FOREIGN KEY (parent_id)
REFERENCES my_table(id)
ON DELETE RESTRICT
ON UPDATE CASCADE

Upvotes: 1

Related Questions