Can't figure out why I can't add a foreign key constraint

Question

I've used google and I know this question has been asked a lot of times. But my problem is the solutions people provide don't work for me.

The problem is this common one: I've got two tables (exercise and typexerc). In exercise I want to add a foreign key that references typexerc.

First I add the column TYPEXERC_ID to exercise with alter table, and I don't get any error. Then I add the foreign key with alter table too and I get this:

Error Code: 1215. Cannot add foreign key constraint 0.374 sec

I do a show columns from exercise; and a show columns from typexerc; to check that the data types are the same for both columns (this is the solution people always give to this question), and in both cases they are smallint(6).

Next I try to see if the tables are done with different engines (I did one with forward engineering from the diagram and the other manually with SQL). For this I do a show table status where name='exercise'; and a show table status where name='typexerc'; and in both cases the engine is InnoDB version 10.

So I don't know what to do. Any help would be appreciated. I'm totally noob BTW.

TYPEXERC:

    CREATE TABLE `typexerc` (
  `TYPEXERC_ID` smallint(6) NOT NULL,
  `TYPEXERC_NAME` varchar(25) NOT NULL,
  PRIMARY KEY (`TYPEXERC_ID`),
  UNIQUE KEY `TYPEXERC_ID` (`TYPEXERC_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

EXERCISE:

CREATE TABLE `exercise` (
  `EXER_ID` int(11) NOT NULL,
  `EXER_DATE` date NOT NULL,
  `EXER_TIME` time NOT NULL,
  `EXER_CAL` smallint(6) NOT NULL,
  `TYPEXERC_ID` smallint(6) NOT NULL,
  PRIMARY KEY (`EXER_ID`),
  UNIQUE KEY `EXER_ID` (`EXER_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='          ';

and now I do:

ALTER TABLE exercise
ADD FOREIGN KEY(TYPEXERC_ID) references typexerc;

tl;dr Just post the table structures, not your step by step guide what you did. This doesn't help at all. — fancyPants, Nov 05 '13 at 12:10
Is it possible that there are some records in exercise that do not have a corresponding entry in typeexerc? — Paddy, Nov 05 '13 at 12:10
Have you tried the suggested answer from this question? http://stackoverflow.com/questions/15534977/mysql-cannot-add-foreign-key-constraint — SchmitzIT, Nov 05 '13 at 12:12
@SchmitzIT That answer is for different data types. I checked the data types were the same with show columns. — Kurospidey, Nov 05 '13 at 12:29
It works for me http://sqlfiddle.com/#!2/1d12f.Are you sure the tables are empty?Also check for triggers. — Mihai, Nov 05 '13 at 12:38
@Kurospidey - if you have PRIMARY KEY, then that key is UNIQUE by default, no need to specify both primary and unique for the same column. Second part is that you most likely have records in both tables (or only one of them). Empty them first before adding the constraint. — N.B., Nov 05 '13 at 12:40
@Kurospidey Did the `LATEST FOREIGN KEY ERROR` not list anything? — SchmitzIT, Nov 05 '13 at 12:41
@Mihai unbelievable, as I did it too from the beginning in workbench and I can't add the foreign key. Picking from the same code I posted. So you can and I can't... — Kurospidey, Nov 05 '13 at 12:44
@N.B. I dropped both tables. I created them again with the code I posted above. This time I removed the UNIQUE KEY line as you said. Then i tried to add the foreign key... it gives me the same error. — Kurospidey, Nov 05 '13 at 12:50
Can you post the exact query you use to create the foreign key? Also, try `SET FOREIGN_KEY_CHECKS = 0; ; SET FOREIGN_KEY_CHECKS = 1;` — N.B., Nov 05 '13 at 12:51
@SchmitzIT I do a show engine innodb status, but I cannot do the \g it gives me a syntax error. Don't know why. — Kurospidey, Nov 05 '13 at 12:53

score 2 · Accepted Answer · answered Nov 05 '13 at 13:01

2

Well, your statement is wrong. You're missing the column you're referencing.

ALTER TABLE exercise
ADD FOREIGN KEY(TYPEXERC_ID) references typexerc(typexerc_id);

see it working live in an sqlfiddle.

answered Nov 05 '13 at 13:01

fancyPants

50,732
33
89
96

cannot believe it was so easy. Well as I said I'm noob. Thanks for your time and thanks to everybody who tried to help. It's much appreciated. – Kurospidey Nov 05 '13 at 13:03

Can't figure out why I can't add a foreign key constraint

1 Answers1