MYSQL table wont allow multiple foreign keys

Question

I know this has been asked again and again, and I've tried so many times and don't understand why I keep getting errors, but I'm trying to connect the order details table to the order items, users and payment table, but SQL is coming up with. (this is for a school project)

I've been able to connect a table with two constraints but never with three.

#1005 - Can't create table oursmall.order_details (errno: 150 "Foreign key constraint is incorrectly formed")

CREATE TABLE IF NOT EXISTS order_details(
    order_details_id INT(10) NOT NULL AUTO_INCREMENT,
    order_items_id INT(10) NOT NULL,
    users_id INT(10) NOT NULL,
    total DECIMAL(6,2) NOT NULL,
    payment_id INT(10) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
    modified_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(order_details_id),
    CONSTRAINT fk_order FOREIGN KEY(order_items_id) REFERENCES order_items(order_items_id),
    CONSTRAINT fk_users FOREIGN KEY(users_id) REFERENCES users(users_id),
    CONSTRAINT fk_payment FOREIGN KEY(payment_id) REFERENCES users(payment_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE="utf8_unicode_ci";

Thank you!

Multiple FK's should not be a problem. The deal is the columns in the current table designated to be the FK must be keys themselves, plus the FK column data types _must be the same_ in both tables (including signed-ness, if specified ... if one is unsigned and the other not, then it won't work) AND the referenced table(s) should exist before this table is created, etc. If you're still having issues, then append to the question the data structure for the other two tables that are trying to be referenced. — Paul T., Mar 14 '22 at 00:18
Works for me: http://www.sqlfiddle.com/#!9/943bf5 Your other tables may be sketchy. — Joe DiNottra, Mar 14 '22 at 01:54
Constraints `fk_users` and `fk_payment` may refer to different rows easily. — Akina, Mar 14 '22 at 05:00
[MySQL Creating tables with Foreign Keys giving errno: 150](https://stackoverflow.com/q/1457305/3404097) — philipxy, Mar 14 '22 at 09:51

score 0 · Accepted Answer · answered Mar 14 '22 at 06:15

The column(s) referenced by a foreign key must be a key of the referenced table. Either the primary key or at least a secondary unique key.^*

CONSTRAINT fk_payment FOREIGN KEY(payment_id) REFERENCES users(payment_id)

Is payment_id really the primary or unique key of the users table? I would be surprised if it is.

The second foreign key references users.users_id, right? That's what I assume is the primary key of that table.

^* InnoDB supports a non-standard feature to allow the referenced column to be any indexed column, even a non-unique one. But this is not the standard of foreign keys in the SQL language, and I don't recommend doing it. For example, if a foreign key references a value that may appear on multiple rows in the parent table, what does that mean? Which row is truly the parent row?

MYSQL table wont allow multiple foreign keys

1 Answers1