Is It Possible To Reference A Foreign Key To Parent Table?

CREATE TABLE Product ( 'Product_id' int, 'Stock_quantity' int, 'Product_name' varchar(50), 'Model' varchar(50), 'Average_rating' float(3), 'RAM' int, '

Solution 1:

You are stumbling over one of the many quirks with inheritance: There are no “global” constraints on an inheritance hierarchy, consequently you cannot have a foreign key to a inheritance hierarchy.

The primary key you defined on product does not extend to computer.

Even though a SELECT on product will append the results for the inheritance children as well, these are not part of the table parent and consequently cannot be used as target for the foreign key. The foreign key is between sale and productonly, the inheritance children are excluded.

There is no proper way to do this with inheritance.

It is better for computernot to be an inheritance child of product, but to have a foreign key to product (with a unique constraint on it), so that the data for a computer object will be split between the two tables. This is somewhat inconvenient for queries, but you can have a foreign key that way.

Here is an example:

CREATETABLE product (
   product_id integerPRIMARY KEY,
   prodname text NOTNULL
);

CREATETABLE computer (
   product_id integerPRIMARY KEY,
   processor text NOTNULL,
   FOREIGN KEY (product_id) REFERENCES product(product_id)
);

CREATETABLE sale (
   sale_id integerPRIMARY KEY,
   product_id integerNOTNULLREFERENCES product(product_id)
);

Now there is no direct foreign key reference from sale to computer, but since the product_id of computer and product is identical, you can always find the unique computer for a sale if it exists:

SELECT p.prodname, c.processor
FROM sale s
   JOIN product p USING (product_id)
   LEFTJOIN computer c USING (product_id)
WHERE sale_id =42;

If you have more product “subtypes”, you can add more left joins.