How add foreign key in existing table
ALTER TABLE retailer_commission ADD FOREIGN KEY (Retailer_Id) REFERENCES retailer(Id)
Asked
Active
Viewed 321 times
0
dev1
- 231
- 2
- 5
- 12
-
show both tables (parent/client) schema...it seems both columns schema/data type/properties are not same.... – Zafar Malik Mar 28 '16 at 05:45
-
Both tables schema are same(datatype is int(11)) – dev1 Mar 28 '16 at 05:48
-
may you show output of "show create table mytable" for both tables. – Zafar Malik Mar 28 '16 at 05:49
-
Possible duplicate of [MySQL Error 1215: Cannot add foreign key constraint](http://stackoverflow.com/questions/16969060/mysql-error-1215-cannot-add-foreign-key-constraint) – MusicLovingIndianGirl Mar 28 '16 at 05:56
2 Answers
0
Since the types of the Retailer_id column in the retailer_commission table and the Id column in the retailer table are both the same type (int(11)), there must be another explanation for your error.
One likely explanation is that the retailer_commission table has records with Retailer_id values which refer to records in retailer which do not exist.
If the following query gives you a non empty set, the records returned should be considered as problematical:
SELECT rc.*
FROM retailer_commission rc LEFT JOIN retailer r
ON rc.Retailer_id = r.Id
WHERE r.Id IS NULL
Tim Biegeleisen
- 502,043
- 27
- 286
- 360
-
I am using this query,Query working fine result has been display.New error has display "#1452 - Cannot add or update a child row: a foreign key constraint fails (`testdatabase`.`#sql-15c0_dc`, CONSTRAINT " – dev1 Mar 28 '16 at 06:06
0
Problem has been resolved,In foreign key table there is lot of records that is the reason to occurred this error,I have truncate this table(Foreign table) then execute "ALTER TABLE retailer_commission ADD FOREIGN KEY (Retailer_Id) REFERENCES retailer(Id)",Query has been successfully done.
dev1
- 231
- 2
- 5
- 12