I'm trying to create a foreign key between two tables. Problem is one of those tables has a composite primary key..

My tables are products (one row per product) and product_price_history (many rows per product). I have a composite key in product_price_history, which is product id and start date of a specific price for that product.

Here's my code :

CREATE TABLE products (
    product_id INT IDENTITY(1,1) PRIMARY KEY,
    product_name VARCHAR(50) NOT NULL,
    product_desc VARCHAR(255) NULL,
    product_group_id INT
)

CREATE TABLE product_price_history (
    product_id INT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NULL,
    price NUMERIC (6,2) NOT NULL
)

ALTER TABLE product_price_history
    ADD CONSTRAINT pk_product_id_start_dt
    PRIMARY KEY (product_id,start_date)

Now I'm trying to create a foreign key between the products table and the product_price_history table but I can't because its a composite key.

Also it doesn't make sense to add the start date (the other part of the foreign key) to the products table.

What's the best way to deal with this? Can I create a foreign key between these tables? Do I even NEED a foreign key?

My intentions here are

1. to enforce uniqueness of the product price information. A product can only have one price at any time.
2. to link these two tables so there's a logical join between them, and I can show this in a database diagram

CodePudding user response：

The foreign key on the product_price_history table should only include product_id. Your target is to ensure that any entry product_price_history already has "parent" entry in products. That has nothing to do with start_date.

The way I see this situation, in theory, fully normalized version of the tables would have to have current_price as unique value in products table. And the product_price_history is simply a log table.

It's not necessary to do it this way, with a physical field, but thinking from this perspective helps to see where your tables model is slightly de-normalized.

Also, if you make product_price_history table anything but simple log table, how do you ensure that new start_date is newer than previous end_date? You can't even express that as a primary key. What if you edit start_date later? I would even think to create different compaund key for product_price_history table. Perhaps product_id insert_date or only auto-increment id, while still keeping foreign key relationship to the products.product_id.