0

I'm trying to add a foreign key to an already existing table called OrdersTbl. I added a new column called ApprovedBy like so:

ALTER TABLE OrdersTbl ADD ApprovedBy BIGINT UNSIGNED NOT NULL;

After that, I tried setting it as the foreign key:

ALTER TABLE OrdersTbl
ADD CONSTRAINT ApprovedByEmp FOREIGN KEY (ApprovedBy)
REFERENCES EmployeesTbl(EmployeeID);

But I keep getting

Error 1452: Cannot add or update child row

What am I doing wrong? Should I have set the field ApprovedBy as FOREIGN KEY instead?

shruti1810
  • 3,920
  • 2
  • 16
  • 28
Villager
  • 89
  • 2
  • 2
  • 9
  • `ApprovedBy` cannot be null. And when you refer to another table it must contain values that are in table `employeestbl`. You need to fill the values before referencing or allow nulls. – juergen d Apr 28 '14 at 15:20
  • Please read here: http://stackoverflow.com/questions/1253459/mysql-error-1452-cannot-add-or-update-a-child-row-a-foreign-key-constraint-fa?rq=1 , here: http://stackoverflow.com/questions/12104601/error-1452-cannot-add-or-update-a-child-row?rq=1 , and here: http://stackoverflow.com/questions/13469099/error-code-1452-cannot-add-or-update-a-child-row?rq=1 – MinhD Apr 28 '14 at 15:20

2 Answers2

1

When defining

 ALTER TABLE OrdersTbl ADD ApprovedBy BIGINT UNSIGNED NOT NULL;

then ApprovedBy cannot be null. The default value will be set which is 0.

Then you apply a foreign key to EmployeesTbl. That means the DB checks that ApprovedBy only contains values that are in EmployeesTbl. But that is not the case. The values are 0.

So either allow null for ApprovedBy and set the values later accordingly or set the correct values before adding the foreign key.

Community
  • 1
  • 1
juergen d
  • 201,996
  • 37
  • 293
  • 362
0

My mistake. It seems that I had to change the values in table OrdersTbl manually. In other words, upon adding the column ApprovedBy, the values set for every entry was of course '0' by default. Since there are no Employees with the ID '0' it failed and sent that error. After manually changing the values in each entry to '1' (Or any other existing ID in table EmployeesTbl) the problem was fixed.

Villager
  • 89
  • 2
  • 2
  • 9