On Fri, Sep 07, 2007 at 11:47:40PM -0000, Max wrote: > 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? It may be, but it's a bad idea. > Basically, I have two tables: > > create table user ( > user_id serial, > login varchar(50) primary key, > tags integer[]-- this is where the problem lies Yes, it's a design problem. > ); > > 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. Why? It's good, standard, normalized design, and it will work just fine. You can make a VIEW atop this one using array_accum(), and you can even make that VIEW writeable if you come up with behavior for INSERT, UPDATE and DELETE. > Is there a solution to this problem with arrays of foreign keys, and > if so, how does one do that? See above :) Cheers, David. > > Thanks for any help. > > Max > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings -- David Fetter <david@xxxxxxxxxx> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
