How to create foreign key that is also a primary key in MySQL?
51,682
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.
Author by
user456584
Updated on January 12, 2020Comments
-
user456584 over 4 years
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.