Database - Array as Foreign Key

Question

Can we have an Array field as a Foreign Key? For example, I have following two Tables :

Create Sequence Product_id_seq start with 1 increment by 1;
Create Table Purchase (
Productid integer default next value for product_id_seq,
Qty integer,
cost decimal(17,2)
);

Create Sequence InvoiceNo_seq start with 1 increment by 1;
Create Table Sales (
Invoice_Number integer default next value for InvoiceNo_Seq,
qty integer,
product_ids ARRAY,
sale_value decimal(17,2)
);

I would like to add a constraint in table Sales such as "Foreign Key (Product_ids) references Purchase (Productid)".

Why?

eg. I purchased 20 Calculators on 1st July and Another 10 on 10th July. I sold 25 Calculators on 13th July, I should be able to point to the Productids of both the lots of Calculators, combining which they become 25 Nos (20 from productid 1 and 5 from productid 2). this is where array comes into picture.

I Just want to make sure that the Array Contains Values which are present in Purchase.ProductId.

Can this be Achieved?

Mangoose · Accepted Answer · 2013-07-20 18:15:40Z

1

You can achieve this using Trigger

answered Jul 20, 2013 at 18:15

Mangoose

9221 gold badge9 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sailens Over a year ago

I think it will need more information to consider this a proper answer

Collectives™ on Stack Overflow

Database - Array as Foreign Key

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related