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

Question

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.

Answer 1

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.