CODEWITHSUNDEEP
mysql
Mirrana
Mirrana

Reputation: 1731

MySQL foreign keys not being created

I thought I had foreign keys setup on my tables, but despite having the correct syntax in my DDL, and even when attempting to add a constraint after the fact, nothing happens. I do not get any errors, and I get a message saying '10 row(s) affected Records: 10 Duplicates: 0 Warnings: 0'

Here is the definition for my table:

CREATE TABLE USERS_COURSES (
  USERS_COURSES_ID INT NOT NULL AUTO_INCREMENT,
  USER_ID          INT NOT NULL,
  COURSE_ID        INT NOT NULL,
  ROLE             INT NOT NULL,
  CONSTRAINT PK_USERS_COURSES PRIMARY KEY (USERS_COURSES_ID),
  CONSTRAINT U_USERS_COURSES UNIQUE (USER_ID, COURSE_ID),
  CONSTRAINT FK_USERS_COURSES_USERS FOREIGN KEY (USER_ID) REFERENCES USERS (USER_ID),
  CONSTRAINT FK_USERS_COURSES_COURSES FOREIGN KEY (COURSE_ID) REFERENCES COURSES (COURSE_ID)
);

When I look at the table in MySQL Workbench, and via IntelliJ IDEA, all I see is a single index called FK_USERS_COURSES_COURSES (COURSE_ID)

I can run the following commands as many times as I like, and apparnetly nothing happens:

ALTER TABLE USERS_COURSES ADD CONSTRAINT FK_USERS_COURSES_USERS FOREIGN KEY (USER_ID) REFERENCES USERS (USER_ID);
ALTER TABLE USERS_COURSES ADD CONSTRAINT FK_USERS_COURSES_COURSES FOREIGN KEY (COURSE_ID) REFERENCES COURSES (COURSE_ID);

Here is what I see in my Database tab in IntelliJ:

enter image description here

What might be causing this? I need the foreign keys to prevent deletions, and right now this is not happening.

Upvotes: 1

Views: 1009

Answers (1)

Mike
Mike

Reputation: 2761

Check out the InnoDB documentation for MySQL. While MySQL accepts foreign key declarations as valid syntax, at present only the InnoDB engine actually implements them. If it's not the default on your setup, include it in the table options - CREATE TABLE...ENGINE=InnoDB

Upvotes: 1

Related Questions