Re: create definiton - Mailing list pgsql-general

There are 2 sides to a foreign key  e.g.
if invoices.user_id  is foreign keyed to users.user_id

When you add an invoice, invoices trigger(s) check to make sure the user_id
is valid in the users table. (call it trigger set A)
When you update/delete a user in users, users triggers make sure that the
user_id is not used in the invoices table, if it is the trigger(s) stop you
from update/deleting that user_id in the users table (call it trigger set B)

When you pg_dump a table, the dump contains the SQL statements to create
trigger set A, but NOT trigger set B.

When you drop the table both trigger set A and trigger set B are deleted.

Then, when I alter the schema and reload the table, I have the issue that
only trigger set A is reloaded, hence the referential integrity is only
enforced from the table invoices, not from changes to the table users.

I can give you a more precise example for illustration if you like...

Terry Fielder
Network Engineer
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Tom Jenkins
> Sent: Monday, June 17, 2002 3:37 PM
> To: terry@greatgulfhomes.com
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] create definiton
>
>
> On Mon, 2002-06-17 at 12:43, terry@greatgulfhomes.com wrote:
> > Yeah, but the gotcha there is that any foreign keys are not
> saved as a
> > FOREIGN KEY statement, just as the INSERT/UPDATE trigger
> that enforces the
> > foreign key.  So if you dump the schema, drop the table
> (which deletes the
> > foreign keys in the foreign keyed tables), alter the dump
> and reload: the
> > triggers to enforce the foreign key validation that reside
> in the tables the
> > foreign key is keyed into do not get recreated, so your referential
> > integrity is only enforced WRT changes to your  table
> reloaded from the
> > dump, and NOT enforced from the tables the keys go into.
>
> I believe this is incorrect.  A schema dump will include the triggers.
> from my latest schema dump (this morning):
> <snip>
> --
> -- TOC Entry ID 373 (OID 1133843)
> --
> -- Name: "RI_ConstraintTrigger_1133842" Type: TRIGGER Owner: tjenkins
> --
>
> CREATE CONSTRAINT TRIGGER "mandatorytraininggroupid_mandat"
> AFTER DELETE
> ON "mandatorytraininggroup"  NOT DEFERRABLE INITIALLY
> IMMEDIATE FOR EACH
> ROW EXECUTE PROCEDURE "RI_FKey_cascade_del"
> ('mandatorytraininggroupid_mandat', 'mandatorytraining',
> 'mandatorytraininggroup', 'UNSPECIFIED', 'mandatorytraininggroupid',
> 'mandatorytraininggroupid');
>
> <snip>
> --
> -- TOC Entry ID 460 (OID 1133543)
> --
> -- Name: "RI_ConstraintTrigger_1133542" Type: TRIGGER Owner: tjenkins
> --
>
> CREATE CONSTRAINT TRIGGER "educationemployee_employee_fk" AFTER UPDATE
> ON "employee"  NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
> PROCEDURE "RI_FKey_noaction_upd" ('educationemployee_employee_fk',
> 'education', 'employee', 'FULL', 'educationemployee', 'employeeid');
>
> <snip>
>
> >
> > Please correct me if I am wrong, because if there is a
> better way I would
> > like to know, I deal with specs changing all the time and
> have to drop
> > tables to change them from NULL to NOT NULL etc.
>
>
> )
> --
>
> Tom Jenkins
> Development InfoStructure
> http://www.devis.com
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>