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
