pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsimulating partial fkeys..
4+ messages / 4 participants
[nested] [flat]
* simulating partial fkeys..
@ 2003-06-05 14:55 mallah@trade-india.com
0 siblings, 3 replies; 4+ messages in thread
From: mallah@trade-india.com @ 2003-06-05 14:55 UTC (permalink / raw)
To: pgsql-sql
hi ,
Is there any way to enforce fkeys only on subset of
the table something on the lines of unique partial indexes
or any work around ? (on update or insert trigger is the only thing i can think of)
regds
mallah.
-----------------------------------------
Get your free web based email at trade-india.com.
"India's Leading B2B eMarketplace.!"
http://www.trade-india.com/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: simulating partial fkeys..
@ 2003-06-06 11:35 Jan Wieck <JanWieck@Yahoo.com>
parent: mallah@trade-india.com
2 siblings, 0 replies; 4+ messages in thread
From: Jan Wieck @ 2003-06-06 11:35 UTC (permalink / raw)
To: mallah@trade-india.com; +Cc: pgsql-sql
mallah@trade-india.com wrote:
>
> hi ,
>
> Is there any way to enforce fkeys only on subset of
> the table something on the lines of unique partial indexes
Sure. Put NULL values into the referencing fields of those rows you
don't want to be checked.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: simulating partial fkeys..
@ 2003-06-06 12:20 Bruno Wolff III <bruno@wolff.to>
parent: mallah@trade-india.com
2 siblings, 0 replies; 4+ messages in thread
From: Bruno Wolff III @ 2003-06-06 12:20 UTC (permalink / raw)
To: mallah@trade-india.com; +Cc: pgsql-sql
On Thu, Jun 05, 2003 at 20:25:32 +0530,
mallah@trade-india.com wrote:
>
>
> hi ,
>
> Is there any way to enforce fkeys only on subset of
> the table something on the lines of unique partial indexes
>
> or any work around ? (on update or insert trigger is the only thing i can think of)
If the foreign column is null it isn't checked against the other table.
You can probably use this to do what you want.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: simulating partial fkeys..
@ 2003-06-06 15:58 Josh Berkus <josh@agliodbs.com>
parent: mallah@trade-india.com
2 siblings, 0 replies; 4+ messages in thread
From: Josh Berkus @ 2003-06-06 15:58 UTC (permalink / raw)
To: mallah@trade-india.com; pgsql-sql
Mallah,
> Is there any way to enforce fkeys only on subset of
> the table something on the lines of unique partial indexes
>
> or any work around ? (on update or insert trigger is the only thing i can
> think of)
<grin> so, why don't you ask me these questions? Nu?
If you mean what I believe that you mean ... let me give you an example from
my own systems:
table cases
field case_id
field case_name
field case_status
table status
field status
field relation
field description
In this schema, the table "status" holds status values for all relations
(tables), not just for "cases". I would like to create an FK from
case_status to status.status *only for those values of status where relation
= 'cases'.
Can't be done. (at least, not with an FK declaration -- see below for a
workaround)
This is a classic example of one of the failures of the SQL Standard. The
above relationship is easily definable in Relational Calculus, but SQL will
not support it. And given PostgreSQL's commitment to that standard, we cannot
really extend Postgres's FK implementation to cover that situation.
"distributed keys" is another really good example of a useful Relational
structure that SQL will not support.
The only way to enforce this in the database would be to create triggers (not
Rules, for performance reasons, since Rules can't use indexes) FOR INSERT,
UPDATE ON cases, and FOR UPDATE, DELETE on status. The triggers on status
would be annoyingly long.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-06-06 15:58 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-06-05 14:55 simulating partial fkeys.. mallah@trade-india.com
2003-06-06 11:35 ` Jan Wieck <JanWieck@Yahoo.com>
2003-06-06 12:20 ` Bruno Wolff III <bruno@wolff.to>
2003-06-06 15:58 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox