Thread: Can arrays reference primary keys in CREATE TABLE?
Hi, I'm a rather new SQL user, and I found interesting that you can use "references" to make sure that a value inserted in a table is a primary key from another table. Something like: create table ta ( a_id integer primary key ); create table tb ( a_ref integer references ta(a_id) ); However, I'd like to define a table containing an array, and that all elements of the array be a primary key from another table, so I tried: create table tb ( a_ref integer[] references ta(a_id) ); But that doesn't work, I think because an array of integers can't be directly compared to an integer. Is there a way to do this? (I'm using Red Hat 9 with PostgreSQL 7.3.2, by the way.) Thanks in advance for any answer or for pointing our where I can find it. Fil
On Friday 09 May 2003 10:21 pm, Filipe Bonjour wrote: > Hi, > > I'm a rather new SQL user, and I found interesting that you can > use "references" to make sure that a value inserted in a table is a > primary key from another table. Something like: Yep - referential integrity is vital. > However, I'd like to define a table containing an array, and that > all elements of the array be a primary key from another table, so I > tried: > > create table tb ( > a_ref integer[] references ta(a_id) > ); > > But that doesn't work, I think because an array of integers can't > be directly compared to an integer. Don't use arrays, use another table, e.g. CREATE TABLE tb_items ( tb_ref integer REFERENCE tb, ta_ref integer REFERENCES ta, PRIMARY KEY (tb_ref,ta_ref) ); -- Richard Huxton
Richard Huxton wrote: >On Friday 09 May 2003 10:21 pm, Filipe Bonjour wrote: > > >>Hi, >> >>I'm a rather new SQL user, and I found interesting that you can >>use "references" to make sure that a value inserted in a table is a >>primary key from another table. Something like: >> >> > >Yep - referential integrity is vital. > I used a trigger to get referential integrity. So, on update or insert, it fires the function that's going to read the new array and check if really exists on the other table > > > >>However, I'd like to define a table containing an array, and that >>all elements of the array be a primary key from another table, so I >>tried: >> >>create table tb ( >> a_ref integer[] references ta(a_id) >> ); >> >>But that doesn't work, I think because an array of integers can't >>be directly compared to an integer. >> >> > >Don't use arrays, use another table, e.g. > >CREATE TABLE tb_items ( > tb_ref integer REFERENCE tb, > ta_ref integer REFERENCES ta, > PRIMARY KEY (tb_ref,ta_ref) >); > > > -- Luis Sousa Especialista de Informática Gabinete de Gestão de Informação, ext: 7837 Campus de Gambelas Universidade do Algarve, tel: 289800900
Luis Sousa wrote: > Richard Huxton wrote: > >> On Friday 09 May 2003 10:21 pm, Filipe Bonjour wrote: >> >> >>> Hi, >>> >>> I'm a rather new SQL user, and I found interesting that you can >>> use "references" to make sure that a value inserted in a table is a >>> primary key from another table. Something like: >>> >> >> >> Yep - referential integrity is vital. >> > I used a trigger to get referential integrity. > So, on update or insert, it fires the function that's going to read the > new array and check if really exists on the other table And what happens on UPDATE or DELETE to the primary key table? You used a trigger to check something, but unless you have a full set of said triggers and they do the locking as well, you do not have referential integrity. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Luis Sousa wrote: > Jan Wieck wrote: >> You used a trigger to check something, but unless you have a full set of >> said triggers and they do the locking as well, you do not have >> referential integrity. >> >> >> Jan >> >> > I guess you are right. > I'll have to write a trigger for UPDATE or DELETE. How can I do the lock > only on that record ? That exactly is the problem with arrays here. You can't index an array in a way so that you can effectively select all rows who's array contains a particular value. Your trigger can only select all of them and loop through the arrays - but it has to select all of them for update (resulting effectively in a full table lock, bye bye concurrency) because otherwise it'll see an older snapshot ignoring concurrent transactions. What you can do, if you don't want or can't give up the array in the first place, set up another table that holds the primary key and the arrays single values in rows. This table is managed by INSERT, UPDATE and DELETE triggers of the table with the array. Now put the foreign key constraint on that table instead and you have the desired behaviour with real referential integrity. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Jan Wieck wrote: > Luis Sousa wrote: > >> Richard Huxton wrote: >> >>> On Friday 09 May 2003 10:21 pm, Filipe Bonjour wrote: >>> >>> >>>> Hi, >>>> >>>> I'm a rather new SQL user, and I found interesting that you can >>>> use "references" to make sure that a value inserted in a table is a >>>> primary key from another table. Something like: >>> >>> >>> >>> Yep - referential integrity is vital. >>> >> I used a trigger to get referential integrity. >> So, on update or insert, it fires the function that's going to read >> the new array and check if really exists on the other table > > > And what happens on UPDATE or DELETE to the primary key table? > > You used a trigger to check something, but unless you have a full set of > said triggers and they do the locking as well, you do not have > referential integrity. > > > Jan > > I guess you are right. I'll have to write a trigger for UPDATE or DELETE. How can I do the lock only on that record ? Luis Sousa
On Thu, 22 May 2003, Jan Wieck wrote: > Luis Sousa wrote: > > Jan Wieck wrote: > >> You used a trigger to check something, but unless you have a full set of > >> said triggers and they do the locking as well, you do not have > >> referential integrity. > >> > >> > >> Jan > >> > >> > > I guess you are right. > > I'll have to write a trigger for UPDATE or DELETE. How can I do the lock > > only on that record ? > > That exactly is the problem with arrays here. You can't index an array > in a way so that you can effectively select all rows who's array > contains a particular value. Your trigger can only select all of them there is contrib/intarray which could be used for indexed access to array of integers. In principle, it should be not difficult to generalize this module to support many types. > > Jan > > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
On Fri, 23 May 2003, Oleg Bartunov wrote: > On Thu, 22 May 2003, Jan Wieck wrote: > > > Luis Sousa wrote: > > > Jan Wieck wrote: > > >> You used a trigger to check something, but unless you have a full set of > > >> said triggers and they do the locking as well, you do not have > > >> referential integrity. > > >> > > >> > > >> Jan > > >> > > >> > > > I guess you are right. > > > I'll have to write a trigger for UPDATE or DELETE. How can I do the lock > > > only on that record ? > > > > That exactly is the problem with arrays here. You can't index an array > > in a way so that you can effectively select all rows who's array > > contains a particular value. Your trigger can only select all of them > > there is contrib/intarray which could be used for indexed access to > array of integers. In principle, it should be not difficult to > generalize this module to support many types. Yeah, that should get around locking all the rows. It will still mean that deadlocks are even more likely to occur with such tables than with the normal constraint conditions however (locking the row containing one element we're looking for effectively locks for all of those values). As a side note, there are some other issues with making the constraint work for non-no action constraints. I'm not sure if SET NULL or SET DEFAULT can be meaningfully done at all with the current array support, nor exactly what CASCADE means for delete (does it remove the array element or the entire row).
Stephan Szabo wrote: > On Fri, 23 May 2003, Oleg Bartunov wrote: >>On Thu, 22 May 2003, Jan Wieck wrote: >>>That exactly is the problem with arrays here. You can't index an array >>>in a way so that you can effectively select all rows who's array >>>contains a particular value. Your trigger can only select all of them >> >>there is contrib/intarray which could be used for indexed access to >>array of integers. In principle, it should be not difficult to >>generalize this module to support many types. > > > Yeah, that should get around locking all the rows. It will still mean > that deadlocks are even more likely to occur with such tables than with > the normal constraint conditions however (locking the row containing one > element we're looking for effectively locks for all of those values). > > As a side note, there are some other issues with making the constraint > work for non-no action constraints. I'm not sure if SET NULL or SET > DEFAULT can be meaningfully done at all with the current array support, > nor exactly what CASCADE means for delete (does it remove the array > element or the entire row). Foreign keys have probably not been designed with a multi-dimensional design in mind. Now we're talking about an array referencing, what about arrays of arrays? While some cases sure can be implemented or simulated, I share Stepahn's concerns about the meaning of referential actions like ON DELETE CASCADE. And even if we can solve this one today, tomorrow someone will ask for an implementation where every even indexed element of the array references table A and every odd one references table B (except it is a multiple of 7 because those are used as flags with some totally different meaning), and of course ON DELETE CASCADE means "remove the element from the array" so that all following ones flip their referencing table ... and the multiples of 7's have to leapfrog :-) If Oracle implements it first, I might change my mind. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #