Обсуждение: simulating partial fkeys..

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/

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.

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 #

On Thursday 05 Jun 2003 3:55 pm, 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

I'm afraid not (AFAIK). What might be a solution in your case is to define
another unique index. For example, you might have (row_id) as your primary
key with another unique index on (row_id,row_type). You could then have a
foreign-key that referenced those two columns.

> or any work around ? (on update or insert trigger is the only thing i can
> think of)

Yep, you'll need to build some triggers of your own. The techdocs guides
section is down at the moment, but see my brief example in the attachment.

It would be really useful to be able to have something like:

CREATE contract ( con_id  SERIAL, con_type varchar(4), ... PRIMARY KEY (con_id)
);

CREATE tel_con_section ( tcs_id  SERIAL, tcs_con_ref int4, ... CONSTRAINT contract_fk FOREIGN KEY (tcs_con_ref,'TEL')
REFERENCEScontract  
(con_id,con_type)
);

or even: FOREIGN KEY (tcs_con_ref) REFERENCES contract (con_id) WHERE
contract.con_type='TEL'

Is there a developer around who could comment how plausible this would be?

--  Richard Huxton

On Fri, 6 Jun 2003, Richard Huxton wrote:

> On Thursday 05 Jun 2003 3:55 pm, 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
>
> I'm afraid not (AFAIK). What might be a solution in your case is to define
> another unique index. For example, you might have (row_id) as your primary
> key with another unique index on (row_id,row_type). You could then have a
> foreign-key that referenced those two columns.
>
> > or any work around ? (on update or insert trigger is the only thing i can
> > think of)
>
> Yep, you'll need to build some triggers of your own. The techdocs guides
> section is down at the moment, but see my brief example in the attachment.

As a side note, I think the attached doesn't entirely work yet as
something similar to a foreign key.  You almost certainly need to deal
with deletes as well as inserts and updates and without some kind of
locking I think you're going to fall prey to concurrent transactions
violating the constraint (what happens if someone say inserts a
server_product at the same time someone else updates server).  Neither of
those should be hard to add to it.

> It would be really useful to be able to have something like:
>
> CREATE contract (
>   con_id  SERIAL,
>   con_type varchar(4),
>   ...
>   PRIMARY KEY (con_id)
> );
>
> CREATE tel_con_section (
>   tcs_id  SERIAL,
>   tcs_con_ref int4,
>   ...
>   CONSTRAINT contract_fk FOREIGN KEY (tcs_con_ref,'TEL') REFERENCES contract
> (con_id,con_type)
> );
>
> or even:
>   FOREIGN KEY (tcs_con_ref) REFERENCES contract (con_id) WHERE
> contract.con_type='TEL'
>
> Is there a developer around who could comment how plausible this would be?

The former syntax is probably reasonable, the latter seems more
problematic.  However, IMHO the right way to do this is for someone who
has the time and inclination ( not me ;) ) to look at supporting
subselects in CHECK constraints. This allows you to define whatever wacky
constraint logic you want and it should be done properly (including the
concurrency issues and such).

There are so many (bad) ways to skin this cat... I'm looking for a more elegant
solution.

If I

SELECT date_part('month',rowdate) as month, count(*) as rows FROM mytable GROUP
BY month;

It might only return
month | rows
-------+------1     | 2343     | 9984     | 4035     | 25210    | 64312    | 933

I would like:
month | rows
-------+------1     | 2342     | 03     | 9984     | 4035     | 2526     | 07     | 08     | 09     | 010    | 64311
|012    | 933
 


I could create a one-column table with values 1 - 12 in it, and select from
that table with a where clause matching "month". I could also create a view 
"SELECT 1 UNION SELECT 2 UNION ..." and select against the view. There MUST be
a more elegant way to do this.

Any thoughts?


__________________________________
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com

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 casesfield case_idfield case_namefield case_status

table statusfield statusfield relationfield 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

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> SELECT date_part('month',rowdate) as month, count(*) as rows FROM mytable GROUP
> BY month;
>...
> I could create a one-column table with values 1 - 12 in it, and select from
> that table with a where clause matching "month". I could also create a view 
> "SELECT 1 UNION SELECT 2 UNION ..." and select against the view. There MUST be
> a more elegant way to do this.

The first solution is probably the best one. It does not seem that "unelegant" 
to me. Another way would be just to do it in the application itself.

...or you could consider this one I came up with. Use at your own risk ;)

SELECT dos.mym AS "Month", COALESCE(uno.rc,0) AS "Total" FROM  (SELECT date_part('month',rowdate) AS mym, count(*) AS
rc FROM mytable GROUP BY 1) AS uno
 
RIGHT JOIN  (SELECT oid::integer-15 AS mym   FROM pg_type ORDER BY oid ASC LIMIT 12) AS dos
USING (mym);

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200306072131

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE+4phlvJuQZxSWSsgRAqLRAJsGr5YNiGXKoXBOWq6+3OpSZXOG3ACdFr2F
ywb1tBYllZt6CKtKYhoc7G4=
=6yvp
-----END PGP SIGNATURE-----

CGG:

> I could create a one-column table with values 1 - 12 in it, and select from
> that table with a where clause matching "month". 

This is probably the simplest, most elegant solution.  It is also the "most 
relational".

> I could also create a view
> "SELECT 1 UNION SELECT 2 UNION ..." and select against the view.

This would be both awkward and have appaling performance.

> There MUST
> be a more elegant way to do this.

Another method would be to write a set returning function that generates the 
months and corresponds them to a cursor of the totals and outputs that.

However, I think your first method is likely to be the fastest and easiest to 
maintain.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

On Friday 06 June 2003 18:26, Chris Gamache wrote:

> I could create a one-column table with values 1 - 12 in it, and select from
> that table with a where clause matching "month". I could also create a view
> "SELECT 1 UNION SELECT 2 UNION ..." and select against the view. There MUST
> be a more elegant way to do this.

You probably need a pivot table (the one-column table with values 1 - 12).
Oracle Magazine had a useful article on this subject (relevant for none-Oracle 
SQL too) a while back: http://otn.oracle.com/oramag/oracle/02-sep/o52sql.html
(registration probably required).

Of course you could also use a set returning function a la:

CREATE OR REPLACE FUNCTION months() RETURNS SETOF INT AS ' BEGIN   FOR i IN 1..12 LOOP     RETURN NEXT i;   END LOOP;
RETURN;
END;' LANGUAGE 'plpgsql';


Ian Barwick
barwick@gmx.net