+7 vote
4.6k views
asked in Programming by (1.6k points)

 I have THE FOLLOWING 
 
[U] [B] TABLE1 [/ B] [/ U] Indices: code, provider code: varchar (15) supplier: int (4) 
 
[U] [B] TABLE 2 [/ B] [/ U] Indices: code, provider code: varchar (15) supplier: int (4) 
 
And try to put a foreign key with this command 
 
ALTER TABLE table1 ADD CONSTRAINT FOREIGN KEY `` `FK_tabla1 FK_tabla1` (`code`, `supplier`) REFERENCES `table2` (`code`, `supplier`) ON DELETE NO ACTION ON UPDATE NO ACTION; 
And all the while I get the error 
 
Can not add or update a child row: a foreign key constraint fails (`XXXXXXXXX / # sql-810_70491`, CONSTRAINT `fk_tabla1` FOREIGN KEY (`code`, `supplier`) REFERENCES `table2` (`code`, `supplier` ) ON DELETE NO ACTION ON UPDATE 
Not to do ... 
 
Thanks 

1 Answer

+7 votes
answered by (17.1k points)

You are trying to add a foreign key when no referential integrity. In table1 have code-provider couples who are not in table2 ... like the relationship is reversed? 
 
Anyway note that when defining a multi reference key field references must be given together within the same row, so if you table2 (1, 1), (1, 2), (2, 3 ) and table 1 (1, 1), (1, 2), (1, 3) the tuple (1, 3) is not present in table2 and will not leave. 
 
To see how many rows do not meet the integrity, you can launch a 
 
SELECT COUNT (*) AS count FROM table1 t1 LEFT JOIN table2 t2 ON t1.codigo = t2.codigo t1.proveedor = t2.proveedor AND WHERE IS NULL t2.codigo 
If not many can remove the COUNT and do you return the t1. * And so see what they are. 
 
Greetings.

Ask a Question
Welcome to WikiTechSolutions where you can ask questions and receive answers from other members of the community.

You can ask a question without registration.

Categories

...