Обсуждение: arrays of foreign keys
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
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@fetter.org> 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
On Sep 7, 2007, at 18:47 , Max wrote: > 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. It's because this is not how relational databases are designed to work. From the server's point of view, an array is an opaque structure, to be treated as a whole. You're working against the system here. > I know I can solve my problem with the following table: And this is how you should do it. Michael Glaesemann grzm seespotcode net
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 09/07/07 18:47, 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? > > 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. Why? The (literally) cardinal rule of database normalization is "eliminate repeating values". > Is there a solution to this problem with arrays of foreign keys, and > if so, how does one do that? > > Thanks for any help. - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFG5UDUS9HxQb37XmcRAo+WAKDSB8DNYBZiZtOJ3utAkkX9QkDYtACgk0ab HKf6Oe1DbvZP8cmh1e9dZaQ= =lPTU -----END PGP SIGNATURE-----
Max wrote: > 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). Fortunately, this is not possible. > 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? That is the correct and efficient solution of your problem. Go for it! Yours, Laurenz Albe
On Fri, 07 Sep 2007 23:47:40 -0000 Max <mlavenne@gmail.com> 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? Ok - now that you have 4 replies telling you NOT to do this, if you really decide it's how you want to proceed - it can be done by writing your own triggers. Personally, I would heed the advise of the other replies and seriously consider why you want to do this. If you decide to go against standard normalized database practice (as is your right) feel free to ping me off list and I can send you an example of what I've done. Josh
Hello, Thanks everyone for your input. Then, it sounds like I won't use an array of foreign keys. I was just curious about the array functionality. However, I didn't think about setting up a view above the intermediary table with an array_accum, now I have never heard of array_accum. I did some research in the online doc. It's a cool functionality, but what's the performance of it? Would using an array_accum slow down a view? Thanks Max