Issues with creating a foreign key

Question

I'm having issues with setting up a relation between 2 tables called Customer and Customer_Number. I have both tables set to InnoDB both have indexes, but when I go to create the foreign key, I get a "no index defined" error. Below are some screen shots

Here Is the Customer table. enter image description here

Here is the Customer_Number table.

enter image description here And here is my error message when trying to create the foreign key.

enter image description here And lastly, this is the error I get when trying to create the relationship manually.

enter image description here I just can't seem to figure out the issue, and it's driving me nuts!

the output for SHOW CREATE TABLE Customer is

 CREATE TABLE `Customer` (
`Customer_ID` int(11) NOT NULL AUTO_INCREMENT,
`First` varchar(255) NOT NULL,
`Last` varchar(255) NOT NULL,
PRIMARY KEY (`Customer_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

and the output for SHOW CREATE TABLE Customer_Number is

 CREATE TABLE `Customer_Number` (
`Num_ID` int(11) NOT NULL AUTO_INCREMENT,
`Customer_ID` int(11) NOT NULL,
`Number` varchar(255) NOT NULL,
PRIMARY KEY (`Num_ID`),
KEY `Customer_ID` (`Customer_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

Hmmm I actually have no problem creating these tables and then adding the FK, or creating them with the FK in the original definition. This works: `ALTER TABLE Customer_Number ADD FOREIGN KEY (Customer_ID) REFERENCES Customer (Customer_ID);` — Michael Berkowski, Sep 24 '14 at 16:47
hmmm when I put what you gave me, I get this error `#1452 - Cannot add or update a child row: a foreign key constraint fails (`thesnoopz`.`#sql-23d7_8c3428`, CONSTRAINT `#sql-23d7_8c3428_ibfk_1` FOREIGN KEY (`Customer_ID`) REFERENCES `Customer` (`Customer_ID`)) ` — Jcmoney1010, Sep 24 '14 at 16:49
Ohh, that seems like you might already have a value in some rows in `Customer_Number.Customer_ID` which do not have a corresponding parent row in `Customer.Customer_ID`. In order to create the FK, any values already there have to be valid row references back to the `Customer` parent table. — Michael Berkowski, Sep 24 '14 at 16:51
`SELECT * FROM Customer_Number WHERE Customer_ID NOT IN (SELECT Customer_ID FROM Customer)` to see if that's the case. — Michael Berkowski, Sep 24 '14 at 16:52
There are 3 rows in `Customer_Number` and the `Customer_ID` is 0 in all 3 rows. Do these rows need to be empty or deleted to form the FK relation? — Jcmoney1010, Sep 24 '14 at 16:57
Yes, you'll need to delete them to be able to add the constraint. You can't null them because the column is `NOT NULL`. It's also possible to temporarily disable FK checks, add the FK constraint, update the rows to point to a matching parent row instead of deleting them, then reenable FK checks; http://stackoverflow.com/questions/8538636/mysql-set-foreign-key-checks — Michael Berkowski, Sep 24 '14 at 17:03
That seemed to work. If you want to make it into an answer I will give you the points — Jcmoney1010, Sep 24 '14 at 17:52
I deleted the rows. The other issue was the fact that I was updating the database via a form, and every time I entered a number, it would save to the `Customer_Number` table, but fill the `Customer_ID` with a 0 value. I deleted those rows and manually enter the values on phpMyAdmin, and it worked. I now need to figure out how to get the `Customer_ID` to work correctly when updating for a web form. — Jcmoney1010, Sep 24 '14 at 17:59

score 1 · Accepted Answer · answered Sep 24 '14 at 18:05

The two CREATE TABLE statements as posted are correct and should be able to accept a new FOREIGN KEY constraint on Customer_Number.Customer_ID since the necessary criteria are met (same data type as referenced column, comparable index or primary key on referenced column).

An ALTER statement succeeds in my testing:

ALTER TABLE Customer_Number ADD FOREIGN KEY (Customer_ID) REFERENCES Customer (Customer_ID);

Being unfamiliar with how PhpMyAdmin abstracts some RDBMS errors, it is hard to say for sure what exactly has gone wrong in the GUI. But if you run the ALTER statement manually and encounter errors about failed foreign key constraints, that's an indication the referencing table already contains values in the column which do not reference a valid row value in the parent table. To uncover those rows so you can address them, execute a query like:

SELECT * FROM Customer_Number WHERE Customer_ID NOT IN (SELECT Customer_ID FROM Customer)

Once you have found the problematic rows, you can either delete them (if unneeded) or update their values to the value of a valid row value in the referenced table. If the column's definition allowed NULL (which yours does not) you could also UPDATE them to set them NULL then run the ALTER statement again.

It is also possible to disable foreign key checks temporarily, add the constraint, update the rows to match valid parent table values, the reenable foreign key checks.

score 0 · Answer 2 · edited Sep 25 '14 at 09:37

0

please try this.

alter table Customer_Number add foreign key(customer_ID) references Customer (Customer_ID);

edited Sep 25 '14 at 09:37

Reporter

3,897
5
33
47

answered Sep 25 '14 at 09:31

Gajendra Singh Rajput

100
8

Issues with creating a foreign key

2 Answers2