0

I was able to add foreign key constraints on a column when I first made the table, and I had created the column to accept null values.

But now that there is data in the table, actual records with null values, it seems I'm unable to add a foreign key constraint on one of the columns, because there are null values.

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails

This is kind of a pain, and it seems like I might just forego putting a foreign key back on this column.

Blaine Lafreniere
  • 3,451
  • 6
  • 33
  • 55
  • How so? Nulls are perfectly acceptable in a foreign key column. I think there's more to it that you are not showing. Can you add the specific table definition? – The Impaler Oct 08 '20 at 16:00
  • On creation, yeah, I was able to create the table with a foreign key constraint on a column that could be null, but at that moment there was no data in the table. I'm using this migration tool called phinx. At some point in my migrations, I rename a table, this seems to have the side-effect of also renaming the related foreign key constraints... which I would rather not happen. So now I have to delete the FK constraint, and add it again, that's where I'm at currently. – Blaine Lafreniere Oct 08 '20 at 16:09
  • I have attempted to add a foreign key constraint on the table (which contains data with null values) using the command: `alter table tbl_name add foreign key (fk_id) references foreign_tbl (id)` - the same error occurs, `Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails.` – Blaine Lafreniere Oct 08 '20 at 16:16
  • actually, I think I realize the issue... when there is an ID set, it doesn't point to anything, so that's what that error must mean. – Blaine Lafreniere Oct 08 '20 at 16:19

0 Answers0