Cannot add foreign key to column in MYSQL

Question

I've tried many states to add foreign key to a column, but I face error.

I've two table that there's name is "orders" and "order_item". "orders" table has primary key that is named "Id" and "order_item" and column that id name "OrderId". I want to add foreign key for "OrderId" in order_item to reference "Id" column in "orders".

What's problem and how should I solve it?

Database engine : InnoDB

Two columns that mentioned are exist

Type of columns is same and is BIGINT with 20 length.

SQL query:

ALTER TABLE `order_item`
ADD FOREIGN KEY (`OrderId`)
REFERENCES `orders`(`Id`);

MySQL said: Documentation
#1452 - Cannot add or update a child row: a foreign key constraint fails (`aigair1_maqaze`.`#sql-196c_4e`, CONSTRAINT `#sql-196c_4e_ibfk_1` FOREIGN KEY (`OrderId`) REFERENCES `orders` (`Id`))

Very clear, parent data not exists, so truncate the table first. See similar: http://stackoverflow.com/questions/1253459/mysql-error-1452-cannot-add-or-update-a-child-row-a-foreign-key-constraint-fa — Muhammad Muazzam, Nov 15 '15 at 11:50

score 2 · Accepted Answer · edited May 23 '17 at 12:30

2

Very clear, parent data not exists, so truncate the table first. See similar:

thread

edited May 23 '17 at 12:30

Community

1
1

answered Nov 15 '15 at 11:51

Muhammad Muazzam

2,810
6
33
62

Cannot add foreign key to column in MYSQL

1 Answers1