Is it possible to reference a foreign key to parent table?

Question

CREATE TABLE Product 
(
    "Product_id" int,
    "Stock_quantity" int,
    "Product_name" varchar(50),
    "Model" varchar(50),
    "Average_rating" float(3),
    "RAM" int,
    "Color" varchar(20),
    "Price" float(10),
    PRIMARY KEY ("Product_id")
);

CREATE TABLE Sale 
(
    "Sale_id" int,
    "Sale_date" date,
    "Employee_id" int,
    "Customer_id" int,
    "Product_id" int,
    "Product_quantity" int,
    "Rating" int,
    PRIMARY KEY ("Sale_id"),
    FOREIGN KEY("Employee_id") REFERENCES Employee("Employee_id") ,
    FOREIGN KEY("Customer_id") REFERENCES Customer("Customer_id") ,
    FOREIGN KEY("Product_id") REFERENCES Product("Product_id")
);

CREATE TABLE Computer  
(
    "Type" varchar(10),
    "Processor" varchar(20),
    "Monitor_size" int
)  inherits(Product);

CREATE TABLE Mobile 
(
    "Os" varchar(30),
    "Screen_size" int
) inherits(Product);

Here is my tables while insertion of sale rows I get this error.

ERROR: insert or update on table "sale" violates foreign key constraint "PK_Product_id"
SQL state: 23503
Detail: Key (Product_id)=(12) is not present in table "product".

It says there is no presence of the row, but I can see them when I view the table:

I inserted every product as computer or mobile not as product.

could it be that you use different sessions? and you did not commit your changes? — Nerevar, May 16 '18 at 16:20
It exists in product table when i view it with pgAdmin, but i can't reference them. — phosphorus, May 16 '18 at 16:22
Re-validate with the session as pointed out by @Nerevar, It could be a reason. — , May 16 '18 at 16:25

Laurenz Albe · Accepted Answer · 2018-05-16T20:53:26.087

3

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 product only, the inheritance children are excluded.

There is no proper way to do this with inheritance.

It is better for computer not 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:

CREATE TABLE product (
   product_id integer PRIMARY KEY,
   prodname text NOT NULL
);

CREATE TABLE computer (
   product_id integer PRIMARY KEY,
   processor text NOT NULL,
   FOREIGN KEY (product_id) REFERENCES product(product_id)
);

CREATE TABLE sale (
   sale_id integer PRIMARY KEY,
   product_id integer NOT NULL REFERENCES 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)
   LEFT JOIN computer c USING (product_id)
WHERE sale_id = 42;

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

edited May 16 '18 at 20:53

answered May 16 '18 at 17:29

Laurenz Albe

209,280
17
206
263

But how do you reference from `Sale` to specific product table? – w.k May 16 '18 at 20:00
Via the `product` table. You have a unique constraint on the source of the foreign key constraint from `computer` to `product`, so you can join the three tables to find the unique `computer` record that belongs to a given `sale`. – Laurenz Albe May 16 '18 at 20:02
I'm sorry, still don't grab it. In `Sale`-table you have then `product_id` which references to `product` table and there is product type. But in `Sale` table you have sold certain phone or computer, you have to reference to specific table too somehow. I can see how to do it without constraints (like combining `product_type` and `item_id`), but how to costraint it on DB level?It really tortures me, I have pretty same problem here https://stackoverflow.com/q/50378680/196241 – w.k May 16 '18 at 20:37
I have added an example to illustrate my idea. – Laurenz Albe May 16 '18 at 20:53
OK, if I understand correctly, all subproducts share same pool of IDs? Those are PKs of Product table and are referenced from certain tables. – w.k May 17 '18 at 04:45
Exactly. It does not have to be that way, but I thought it is simple and intuitive. – Laurenz Albe May 17 '18 at 04:48

Is it possible to reference a foreign key to parent table?

1 Answers1

Linked