SQL External Key Array refers to a 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 tried to create a table to record orders. Since this order may contain more than one product, I plan to use an array to record the products of each product, the same with the amount. However, when I want to make productid a foreign key that references the id attribute for a table product, I find that productid is an array and products (id) is just one number. How can I solve this problem so that each element of the productid array refers to products (id)? I am using postgresql btw.

thanks adhead!

Answer 1

, "" ""

: FOREIGN KEY "" , , : , "-", .

Answer 2

PostgreSQL .

PostgreSQL 9.4, , . , . 9.6 , - .

CHECK, , FROM . , , , CHECK, .

, .

Answer 3

Postgres int []. . :

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();