Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – What am I missing here when creating this Foreign Key?

code

CREATE TABLE Students(
id INT PRIMARY KEY AUTO_INCREMENT,
firstName VARCHAR(100),
lastName VARCHAR(100),
majorID INT);

CREATE TABLE majors(
id INT,
majorName VARCHAR(100));

ALTER TABLE majors
    ADD FOREIGN KEY(id) REFERENCES Students(majorID);

For Some Reason ever time I try to create a Foreign Key I keep getting the "unable to create foreign key constraint" error in mysql yet the parent and it are both the exact same type.

I’ve tried adding the key later with Alter table, removing primary key constraint from both, one then the other, assigned the unsigned constraint and removed it to no avail.

I feel really dumb at the moment as I’ve seen from past work I was able to add them the exact same way with no issues so not sure what exactly has changed.

Tags:

2

Answers


  1. 0 
    ALTER TABLE majors
ADD CONSTRAINT majors_ibfk_1 FOREIGN KEY(id) REFERENCES Students(majorID);

    Try ADD CONSTRAINT majors_ibfk_1.

    BTW, shouldn’t be this instead as your majorID is the foreign key in Students table?

    ALTER TABLE Students    
ADD CONSTRAINT students_ibfk_1 FOREIGN KEY(majorID) REFERENCES majors(id);
    Login or Signup to reply.
  2. 0

    You have the direction of reference backwards.

    In your example, I would expect Students to contain a foreign key, referencing Majors. Not the other way around.

    A foreign key should reference the primary key of the referenced table.

    Example:

    ALTER TABLE Students
    ADD FOREIGN KEY(majorID) REFERENCES Majors(id);

    If you were to constrain Majors to reference the primary key of in Students, then each major would only allow one student. I think you want many students to be in each major, so you should put the foreign key in Students, so several rows of that table can have the same value, which references the primary key of Majors.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search