0

I have 3 tables (Users, Links and LinkLists) and I want to store user ids and their link ids in an array. However, when I try the code below I get following error:

incompatible types: integer[] and integer

Is there any way that I can store user_id and an array of link ids in a table?

CREATE TABLE Users (
    id serial primary key not null,
    email varchar(64) unique not null,
);

CREATE TABLE Links (
    id serial primary key not null,
    name varchar(64) not null
);

CREATE TABLE LinkLists(
    user_id integer unique not null REFERENCES Users(id),
    links integer [] REFERENCES Links(id) -- problem here --
);

Example:

Users Table*

1 example@gmail.com

Links Table

1 google.com
2 twitter.com

LinkLists Table

1 [1,2]
Mehmet Ali
  • 313
  • 3
  • 15
  • When you reference a foreign key the types in both tables must be identical – James Jun 08 '19 at 21:57
  • @JaimeDrq I'm totally aware of that. However, I also know that it is possible to create an array elements linking to primary keys. How can I do that? – Mehmet Ali Jun 08 '19 at 21:59
  • 2
    You can't have foreign keys together with array. Storing the IDs in an array is not a good database design to begin with (e.g. you can't easily prevent duplicates in the array). Using a properly designed many-to-many table is the much better choice here. –  Jun 09 '19 at 00:43
  • Possible duplicate of [PostgreSQL array of elements that each are a foreign key](https://stackoverflow.com/questions/41054507/postgresql-array-of-elements-that-each-are-a-foreign-key) – philipxy Jun 09 '19 at 03:37

2 Answers2

2

Probably you do not need array data type on LinkList table.

You need only two foreign keys.

CREATE TABLE LinkLists(
    user_id integer unique not null REFERENCES Users(id),
    links integer not null REFERENCES Links(id) 
);

According to me, you do not need 3nd table: LinkLists

Define a user_id FK on links table and refer to users.

Or best approach, use a treeview table, including REFERENCES ownself

UgurYilmaz
  • 39
  • 5
  • Thank you for your suggestion but it will be needed to have 3nd table. So your approach makes sense but can't we create an array to hold primary keys? – Mehmet Ali Jun 08 '19 at 22:09
  • You should remove the `unique`. And add a primary key on **both** columns. – wildplasser Jun 09 '19 at 15:01
-1

Actually you can do! Just add a new array type field the users table. And define an after insert trigger function for users table.

And update array field by CTE.

I am using that approach succesfully.

UgurYilmaz
  • 39
  • 5