By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,660 Members | 1,267 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,660 IT Pros & Developers. It's quick & easy.

Array values and foreign keys

P: n/a
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
ds*****@cids.ca

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a


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 <ds*****@cids.ca> 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
ds*****@cids.ca

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #2

P: n/a
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 ma*******@postgresql.org

Nov 23 '05 #3

P: n/a
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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.