MYSQL table wont allow multiple foreign keys

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!

Answer

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?