Search code examples
mysqldatabaseforeign-keyssqlyog

"please select equal number of source and reference" on sqlyog

I am trying to add a foreign key constraint via sqlyog, and getting this error, although I am only select one source and one reference columns

please select equal number of source and reference

Does anyone knows what it means in this case? Note that I do have an equal number of source and reference columns...

Solution

  • I came across the same issue with SQLYog v9.01. The error message is misleading and the real cause of an error can be totally different.

    Things I checked to solve this are the following:

    • Check table engyne types, should be both InnoDb
    • Check if your target table is not the same as source.
    • Check datatypes, length and charset collation of referenced fields.
    • If you already have data in your tables check its consistency.

    For example, you should remove all unrelated data from table B which relates to table A

     DELETE target FROM B AS target LEFT JOIN A USING(id_A) WHERE A.id_A IS NULL
    • Finally ones in my case I had to FLUSH tables to create my constraints successfully.

    Manual constraint creation from Query window can give you more information on your error type.

    Just a reminder:

    ALTER TABLE `B` ADD CONSTRAINT `FK_B` FOREIGN KEY (`id_A`) REFERENCES `A` (`id_A`) ON DELETE CASCADE ;

    Good luck!