-1

I try to do this into mysql :

Create a first table with A, B, C columns and a composite primary key on A and B. Create a second table A, B, D, E columns with A, B, D as primary key and of course A, B referenced as a foreign from first table.

I use mysql workbench to creates columns, add keys and foreigns constraints, but when i try to apply i receive this error :

ERROR 1215: Cannot add foreign key constraint

The thing is that i don't know what exactly is wrong with my design.

Can you help me.

  • Please give a [mcve] and read [ask]. In particular show your DDL. Probably you don't realize that the referenced columns of a FK must be declared as PK or UNIQUE NOT NULL. (Here, AB in ABDE.) But "referenced as a foreign key" doesn't make sense. A FK does the referencing. And I expect you actually want ABDE(AB) REFERENCES ABC(AB). Please google a description of your problem (using "references" the right way), this is a faq. – philipxy Nov 26 '16 at 01:04

1 Answers1

0

Your problem is that you have (A, B) pairs in the second table which do not have matches in your first table.

Run this:

select secondTable.A, secondTable.B
from secondTable
where not exists (select 1
                  from firstTable
                  where firstTable.A = secondTable.A and firstTable.B = secondTable.B);

With this query you will find A, B values in secondTable which do not match any records in firstTable.

To be able to create your foreign key, you will need to either remove those records from secondTable or insert their matches into firstTable.

Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175