From: | terry(at)greatgulfhomes(dot)com |
---|---|
To: | "'Tom Jenkins'" <tjenkins(at)devis(dot)com> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: create definiton |
Date: | 2002-06-17 21:11:58 |
Message-ID: | 006001c21643$9e553780$2766f30a@development.greatgulfhomes.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)greatgulfhomes(dot)com
> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Tom Jenkins
> Sent: Monday, June 17, 2002 3:37 PM
> To: terry(at)greatgulfhomes(dot)com
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] create definiton
>
>
> On Mon, 2002-06-17 at 12:43, terry(at)greatgulfhomes(dot)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(at)postgresql(dot)org)
>
From | Date | Subject | |
---|---|---|---|
Next Message | Vince Vielhaber | 2002-06-17 21:25:42 | website design |
Previous Message | Tom Jenkins | 2002-06-17 20:36:41 | Re: weird EXPLAIN |