6 
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!

Improve this question

3 Answers 3

Reset to default
7

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.

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

9 Comments

Thx but how to record unknown number of products of one order in the table?
Your comment was just answered by @ekhunayn so I won't repeat the correct explanation given there. In general, with relational DBs you should prefer to use normal relational modelling (1:1, 1:n and n:m relationships via join tables), and only use the fancy stuff like arrays, hstore, json, etc, when you run into issues the usual techniques don't solve or make difficult to solve.
Hi, somebody here knows what the status today, 2015/2016, of the foreign-key-array proposed support?
@PeterKrauss No progress AFAIK
Some alternative solution by CHECK(select ...) clause? On my table no mater about insert/update performance.
|
1

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.

Improve this answer

1 Comment

Sounds like a way to go! Thx!
1

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();
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.