Search code examples
mysqlrdbms

what will happen to orphaned rows in a table if I add foreign key to one of the columns

A table in an RDBMS that has a foreign key column but was not marked as one has lead to many orphaned rows.

What will change if I Alter the table and make that column a foreign key constraint.

Table:

Posts: id, title, body, user_id (currently not marked as FK)

Will I lose the orphaned rows?

EDIT: 1. Would it make sense to recreate the table with the correct FK constraints and port data from the old table to the new one?

Solution

  • MySQL will disallow creation of the foreign key index if it would result in "orphaned" records in the child table.

    You can force creation of the index with

    set foreign_key_checks = 0;

    before running your alter query - but you will eventually have to clean up the dangling records.