0

This is my first time working on database, and I couldn't find a way out on this problem.
I was trying to add foreign key to one of columns in my table, but it gets

"ERROR 1215 : Cannot add foreign key constraint"

I googled this problem, but it was pretty hard to figure out what is wrong.
Here's what happened
I wanted my tables be like below

tableA
└ id - PRIMARY KEY
└ name
└ password

tableB
└ title
└ no - PRIMARY KEY
└ and so on... (plenty of more columns here) then!!
└ unique_id - PRIMARY KEY
(So, tableB has multiple primary keys)

tableC
└ id - References tableA (id) & PRIMARY KEY
└ no - References tableB (no) & PRIMARY KEY
└ memo
(So does tableC)

So I created columns like this, and I tried to set foreign keys in tableC

id column have gone well, but no column still returns error code I mentioned above,
Error code 1215.

I matched a data type for both tableC's id and tableB's id.

What did I do wrong?? :(

P.S. Maybe is this with something from documentation?? (http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html)

However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.

Y.Prithvi
  • 1,221
  • 3
  • 14
  • 27
lkaybob
  • 1
  • You matched the data type, but have you matched the display length as well? – N.B. Aug 07 '14 at 09:21
  • 1
    Are you using InnoDB as the engine for all tables? – Zyga Aug 07 '14 at 09:23
  • possible duplicate of [Error Code: 1215. Cannot add foreign key constraint (foreign keys)](http://stackoverflow.com/questions/17691282/error-code-1215-cannot-add-foreign-key-constraint-foreign-keys) – Daniel W. Aug 07 '14 at 09:25
  • @N.B. I'm not sure. I'll check it – lkaybob Aug 07 '14 at 09:28
  • @Zyga Yes. All tables are using InnoDB – lkaybob Aug 07 '14 at 09:28
  • 1
    When you say you have 2 columns in the same table that have PRIMARY KEY, do you intend that the primary key is a composite of both? TableB seems strange... if unique_id is "unique", then why do you need a composite primary key? Anyway... to reference a composite primary key, you must have all columns in the referencing table... not just one. – Frazz Aug 07 '14 at 09:32
  • Just post the whole table structure. Do `show create table table_name;` and post the result. – fancyPants Aug 07 '14 at 09:33
  • I forgot to finish this question. As I discussed this problem with my partners, one of my partners misunderstood the structure of database. It should have been like this. **tableA** has another column called "unique_id", and in **tableB**, "unique_id" references "unique)id" in **tableA**. Anyway thanks for answering my question guys. :) – lkaybob Aug 16 '14 at 14:31

0 Answers0