arrays of foreign keys

From: Max <mlavenne(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: arrays of foreign keys
Date: 2007-09-07 23:47:40
Message-ID: 1189208860.719887.185540@d55g2000hsg.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

And pardon me if I posted this question to the wrong list, it seems
this list is the most appropriate.

I am trying to create a table with an array containing foreign keys.
I've searched through the documentation and couldn't find a way to do
so.

Is this something that one can do?

Basically, I have two tables:

create table user (
user_id serial,
login varchar(50) primary key,
tags integer[]-- this is where the problem lies
);

create table tag (
tag_id serial,
name varchar(50) primary key
);

I would like the user.tags field to be a list of foreign keys (tag_ids
specifically).

I know I can solve my problem with the following table:

create table user_tag (
user_id integer,
tag_id integer,
foreign key (user_id) references table user(user_id) on delete
cascade,
foreign key (tag_id) references table tag(tag_id) on delete cascade,
primary key (user_id,tag_id)
);

But I would really like to avoid doing that.

Is there a solution to this problem with arrays of foreign keys, and
if so, how does one do that?

Thanks for any help.

Max

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2007-09-08 01:03:13 Re: Query with "like" is really slow
Previous Message Darek Czarkowski 2007-09-07 23:28:41 Re: Postgresql 7.3 on Red Hat Enterprise 5 (Problem with SEMMNI, SEMMNS)