Re: [GENERAL] Array values and foreign keys
Le mer 27/10/2004 à 11:59, Pierre-Frédéric Caillaud a écrit : > > > You can't express it directly with a CHECK constraint but you can do this > : > > - add CHECK( test_array( yourcolumn )) in your table definition > - create function test_array which takes an array and looks if all its > elements are in your table T2, I do something like comparing the length of > the array to SELECT count(1) FROM T2 WHERE key IN array > You can do it other ways but you'll have to use a function. > > Fine. I got it right after fiddling a little bit. The function is something like: CREATE FUNCTION test_array (smallint[]) RETURNS bool AS ' select case when count(1) = array_upper($1,1) then true else false end from t2 where cle = any($1); ' LANGUAGE SQL; It compares the length of the array to the number of elements actually found in the reference table. -- === Daniel Savard === ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Array values and foreign keys
On Wed, Oct 27, 2004 at 05:59:46PM +0200, Pierre-Fr?d?ric Caillaud wrote: > > - add CHECK( test_array( yourcolumn )) in your table definition > - create function test_array which takes an array and looks if all > its elements are in your table T2, I do something like comparing the > length of the array to SELECT count(1) FROM T2 WHERE key IN array This provides only partial foreign key checking: depending on how the application works, you might also need to ensure that updates and deletes in T2 don't break the references in T1. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Array values and foreign keys
You can't express it directly with a CHECK constraint but you can do this : - add CHECK( test_array( yourcolumn )) in your table definition - create function test_array which takes an array and looks if all its elements are in your table T2, I do something like comparing the length of the array to SELECT count(1) FROM T2 WHERE key IN array You can do it other ways but you'll have to use a function. On Wed, 27 Oct 2004 10:19:02 -0400, Daniel Savard <[EMAIL PROTECTED]> wrote: Is there a way to define a foreign key for the values of an array? For example, if table T1 is having a colum A which is defined as integer[] can I define a foreign key in order to force each value to be a pointer (index) to a row in a table T2? If yes, how? Is there any shortcomings to this approach? Thanks, Daniel Savard --- Daniel Savard [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Array values and foreign keys
Is there a way to define a foreign key for the values of an array? For example, if table T1 is having a colum A which is defined as integer[] can I define a foreign key in order to force each value to be a pointer (index) to a row in a table T2? If yes, how? Is there any shortcomings to this approach? Thanks, Daniel Savard --- Daniel Savard [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
