-2

I am trying to set up a MySQL database where a column is a foreign key pointing to another table that auto increments. When I try to create the table with the foreign key, I get the following error:

Can't create table '{DATABASE}' . '{TABLE}' (errorno: 150 "Foreign key constraint is incorrectly formed")

An answer found here says that the problem could be that I am trying to set a normal int as a foreign key pointing to an autoincrement int. How should I go about setting up my table structure?

Here is the statement that produces the error:

create table test_scores(id INT NOT NULL AUTO_INCREMENT, student_id INT, FOREIGN KEY (id) REFERENCES students(student_id) ON DELETE CASCADE, test_id INT NOT NULL, FOREIGN KEY (test_id) REFERENCES tests(id) ON DELETE CASCADE, score INT NOT NULL, PRIMARY KEY (id));

Parent tables:

students
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+


tests
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
Community
  • 1
  • 1
Jacob Malachowski
  • 911
  • 1
  • 9
  • 18
  • What's the code which produces this error? There's nothing special about a FK to an auto-increment field. An integer is an integer, regardless of where it comes from. – David Feb 19 '17 at 01:51
  • I added the statement above. – Jacob Malachowski Feb 19 '17 at 01:55
  • Which foreign key produces the error? What does the target table for the key look like? At a brief glance it looks like you're trying to make this table's primary key also be a foreign key, are you sure you want that? How would you know for any given record being inserted into this table that the `id` it's going to generate will map to the other table? – David Feb 19 '17 at 01:58
  • I added the parent tables above. I believe they both are causing the error since they are the exact same structure. If I am understanding your questions correctly, no I am not making this table's primary key the foreign key. The table I am creating has an autoincrement for a primary key, and has two separate columns that are foreign keys pointing to the autoincrement columns in the parent tables. I think your last question was based on a misunderstanding of my table. (referring to primary/foreign keys being the same column) Let me know if you still need that answered. – Jacob Malachowski Feb 19 '17 at 02:04
  • `"I believe they both are causing the error"` - What you *believe* and what you have *tested* are two very different things. – David Feb 19 '17 at 02:07
  • This question is a bit vague. It would be easier to understand what you are asking if you provided the ddl for the students and tests table. Also, explain what you're trying to do with what looks like a pivot table. – Dom DaFonte Feb 19 '17 at 02:09

1 Answers1

1

On this line:

FOREIGN KEY (id) REFERENCES students(student_id) ON DELETE CASCADE,

you probably meant to do this:

FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,

Otherwise you're trying to make id the foreign key, which isn't going to work because you can't know what id is even going to be until you insert a record. Additionally, students has no student_id column, so the attempt to use it as the relationship for the foreign key would fail.

David
  • 208,112
  • 36
  • 198
  • 279