CODEWITHSUNDEEP
mysqlinheritanceforeign-keysprimary-key
user456584
user456584

Reputation: 88815

How to create foreign key that is also a primary key in MySQL?

This should be a fairly straightforward question, but I'm unable to find an easy answer. How do you create a foreign key that is also a primary key in MySQL? Here's my current attempt:

CREATE TABLE Sale(
    sale_id CHAR(40),
    PRIMARY KEY(sale_id),
    discount DOUBLE,
    type VARCHAR(255),
    price DOUBLE,
    );

CREATE TABLE Normal_Sale(
    sale_id CHAR(40),
    PRIMARY KEY(sale_id);
);

CREATE TABLE Special_Sale(
    sale_id CHAR(40),
    PRIMARY KEY(sale_id);
);

What am I missing here?

Thanks in advance.

Upvotes: 33

Views: 59928

Answers (1)

dgilland
dgilland

Reputation: 2748

Add FOREIGN KEY (sale_id) REFERENCES Sale(sale_id) to each foreign table:

CREATE TABLE Sale(
    sale_id CHAR(40),
    PRIMARY KEY(sale_id),
    discount DOUBLE,
    type VARCHAR(255),
    price DOUBLE
) ENGINE=INNODB;

CREATE TABLE Normal_Sale(
    sale_id CHAR(40),
    PRIMARY KEY(sale_id),
    FOREIGN KEY (sale_id) REFERENCES Sale(sale_id)
) ENGINE=INNODB;

CREATE TABLE Special_Sale(
    sale_id CHAR(40),
    PRIMARY KEY(sale_id),
    FOREIGN KEY (sale_id) REFERENCES Sale(sale_id)
) ENGINE=INNODB;

Just make sure your database is InnoDB which supports Foreign References.

Upvotes: 62

Related Questions