CODEWITHSUNDEEP
mysqlsqldatabasephpmyadmin
Jcmoney1010
Jcmoney1010

Reputation: 922

Issues with creating a foreign key

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

Upvotes: 1

Views: 99

Answers (2)

Gajendra Singh Rajput
Gajendra Singh Rajput

Reputation: 100

please try this.

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

Upvotes: 0

Michael Berkowski
Michael Berkowski

Reputation: 270609

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.

Upvotes: 1

Related Questions