33

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.

user456584
  • 86,427
  • 15
  • 75
  • 107
  • 4
    This design doesn't look right. To me it seems like you could just add a field to Sale which marks it as Normal or Special. This smells – Joe Phillips Apr 07 '11 at 02:07
  • You are *over-normalizing* the schema. As Joe Phillips says, a much simpler solution exists. Your design could lead to headaches as the two states should be mutually exclusive (either normal, or special), what prevents a sale to be recorded as both special and normal in your schema? *Code*, you say? That's it; that's the headache you don't need. – Majid Fouladpour Apr 07 '11 at 02:18
  • 9
    These are just dummy examples. The question is not a design question, it is a technical one. – user456584 Mar 07 '13 at 16:22

1 Answers1

62

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.

dgilland
  • 2,758
  • 1
  • 23
  • 17