is there any way to reference array to another table in PostgreSql?I am doing it by getting error

Question

CREATE TABLE destinations(code varchar(80) PRIMARY KEY,
                          name varchar(80),
                          updated_at varchar(80),
                          latitude varchar(80),
                          longitude varchar(80),
                          country varchar(80) references countries(code),
                          parent varchar(80) references destinations(code),
                          regions int[] ELEMENT REFERENCES regions);

all tables mentioned in reference exist.

I am using PostgreSQL version 12.

I am getting this error.

Answer 1

This is not valid SQL syntax, and there is no way to have a foreign key on elements of an array.

You should use a proper normalized relational design, like

CREATE TABLE destinations(
   code varchar(80) PRIMARY KEY,
   name varchar(80),
   updated_at varchar(80),
   latitude varchar(80),
   longitude varchar(80),
   country varchar(80) references countries(code),
   parent varchar(80) references destinations(code)
);

CREATE TABLE destinations_regions (
   regions int REFERENCES regions NOT NULL,
   code varchar(88) REFERENCES destinations NOT NULL,
   PRIMARY KEY (regions, code)
);

The second table implements the m-to-n relationship.

This will be faster and more efficient.