Cannot add foreign key constraint MySQL Workbench

Question

I'm getting "Error Code: 1215. Cannot add foreign key constraint" when trying to create the "orders" table.

credit_cards table:

CREATE TABLE credit_cards (
    customer VARCHAR(30),
    card_no CHAR(16),
    PRIMARY KEY (customer, card_no),
    FOREIGN KEY (customer) REFERENCES customers(username));

orders table:

CREATE TABLE orders (
    order_no INT AUTO_INCREMENT,
    customer VARCHAR(30) NOT NULL,
    date_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    credit_card CHAR(16) NOT NULL,
    PRIMARY KEY (order_no),
    FOREIGN KEY (customer)
        REFERENCES customers (username),
    FOREIGN KEY (credit_card)
        REFERENCES credit_cards (card_no));

The report from SHOW ENGINE INNODB STATUS says that the problem is FOREIGN KEY (credit_card) REFERENCES credit_cards(card_no))

I've read a bunch of resolved questions and still can't figure it out. I'm using MySQL Workbench. Thanks.

Answer 1

You need to reference all the keys in a primary key (or unique key) relationship. I would recommend:

CREATE TABLE credit_cards (
    credit_card_id int auto_increment primary key,
    customer VARCHAR(30),
    card_no CHAR(16),
    UNIQUE KEY (customer, card_no),
    FOREIGN KEY (customer) REFERENCES customers(username));
orders table:

CREATE TABLE orders (
    order_no INT AUTO_INCREMENT,
    customer VARCHAR(30) NOT NULL,
    date_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    credit_card_id int NOT NULL,
    PRIMARY KEY (order_no),
    FOREIGN KEY (customer)
        REFERENCES customers (username),
    FOREIGN KEY (credit_card_id)
        REFERENCES credit_cards (credit_card_id));