SQL Foreign Key Array referencing non-array

Question

CREATE TABLE orders (
   id          SERIAL PRIMARY KEY,
   productid   INTEGER[] NOT NULL,
   amount      INTEGER[] NOT NULL,
   totalprice   FLOAT NOT NULL,
   ordertime    TIMESTAMP NOT NULL,
   FOREIGN KEY (productid) REFERENCES products(id)
);

I was trying to create a table to record orders. Since that one order may contain more than one product, I plan to use an array to record the productids of every product, same thing with amount. However when I want to make the productid a foreign key which references the id attribute of table product, I found that productid is an array but products(id) is just one number. How can I solve this to make every element of the productid array reference the products(id)?? I am using postgresql btw.

Thx adhead!

Answer 1

Arrays of foreign keys are not supported by PostgreSQL at this time.

Support was proposed for PostgreSQL 9.4, but performance and implementation quality issues lead to its being dropped from the release. It might be added in a future release, or might not. As of 9.6 I don't see anybody working on it.

You can not use a CHECK constraint instead, as they do not support subqueries, a FROM clause, or references to columns in other tables. While it's possible to trick the parser by writing a function to hide the query of the other table then using the function in the CHECK expression, this is not correct and will not work reliably.

I strongly suggest normalising the table instead, so you use multiple individual records instead of arrays.

Answer 2

I think to solve this issue you can use a table for "orders" and another table for "products"

if the relationship between them one:many put the FOREIGN KEY in "products" table however, it seems that the relationship is many:many, so create a new table "order-product" that has the two key from the other two tables.

Answer 3

Postgres does not support a foreign key constraint for int[]. However, you could use triggers to help automatically enforce the constraint. Here is an example of updating the orders when a product is deleted:

CREATE FUNCTION remove_product() RETURNS TRIGGER as $_$                                
BEGIN                                                                                
  UPDATE orders                                                                 
  SET topic_id=array_remove(products, OLD.id)                                  
  WHERE products @> ARRAY[OLD.topic_id];                                             
  RETURN NEW;                                                                        
END;                                                                                 
$_$ LANGUAGE plpgsql;                                                              

create trigger R               
after delete on products
for each row
execute procedure remove_product();