We help IT Professionals succeed at work.

Could you point a way to add a FK to an existent MySQL table?

Eduardo Fuerte
on
Hi Experts

Could you point a way to add a FK to an existent MySQL table?

Accordingly to:
img002
I first added a new column status_id

The above table must to receive a FK  column from this other:
img003
What I had tryed:


ALTER TABLE padronizacaorevendas
ADD FOREIGN KEY (status_id) REFERENCES statusparticipantes(id);

Cannot add or update a child row: a foreign key constraint fails (`local_cheiodegaz`.`#sql-15d8_16c`, CONSTRAINT `#sql-15d8_16c_ibfk_1` FOREIGN KEY (`status_id`) REFERENCES `statusparticipantes` (`id`))

Open in new window


Thanks in advance
Comment
Watch Question

Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
Hi Eduardo,

When you set up a foreign key, you need to make sure that the values stored in the status_id exists in the id of the other table - that's the whole point of a foreign key. I suspect the problem you're having is because you've added the status_id field as an INT NOT NULL. This means the default value for that field will be 0, and if your other table doesn't contains a record with an ID of 0, it will fail the KEY CONSTRAINT.

To work around it, you'll either need to make sure your status_id contains a matching ID, or you'll need to remove the NOT NULL attribute and set all the values to NULL - then you can fire off your ALTER TABLE query.
Eduardo FuerteDeveloper and Analyst

Author

Commented:

Hi Chris


Really.

Removing NOT NULL it worked...

Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Excellent :)