CODEWITHSUNDEEP
mysqlsqlmysql-workbench
Kurospidey
Kurospidey

Reputation: 423

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

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;

Upvotes: 0

Views: 132

Answers (1)

fancyPants
fancyPants

Reputation: 51868

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);

Upvotes: 2

Related Questions