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.
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.