Re: Adding Foreign Key constraints outside of table def.

thank you !

> ALTER TABLE foo ADD FOREIGN KEY (bar) REFERENCES foo2 (bar2) ON DELETE SET
> NULL;

this works (and when you drop the table, they get dropped as well). It's
only when you ADD CONSTRAINTS.


On Fri, Jun 22, 2001 at 11:31:48AM -0400, Gregory Wood wrote:
> >  1.  Is there a way to add it without worrying about the
> >      syntax of "RI_FKey_..()"  e.g. (this would be nice if it worked)
> >
> >      ADD CONSTRAINT $fk.LocalColumnNames FOREIGN KEY
> ($fk.LocalColumnNames)
> >      REFERENCES $fk.ForeignTableName ($fk.ForeignColumnNames)
> >      ON DELETE set null, ON UPDATE cascade;
>
> Here is how I added a foreign key to a table after the fact:
>
> ALTER TABLE foo ADD FOREIGN KEY (bar) REFERENCES foo2 (bar2) ON DELETE SET
> NULL;
>
> Haven't actually tried within the ADD CONSTRAINST syntax.
>
> >    2.... skipped
>
> >    3. If I drop a table, how come the constainsts for that
> >    table are NOT dropped as well?
>
> I don't know about this one, I hope someone more knowledgable will step in.
> I'm surprised if this is indeed the behavior.
>
> >    4. for circular references,
> >       Table A references B
> >       B references C
> >       C references A
> >
> >       is it impossible to do an insert  (assuming all the foreign key
> >       constraints are set up) ?
>
> You'll have to set up at least two of the constraints as INITIALLY DEFERRED,
> then perform any inserts to all three tables inside of a transaction.
>
> Greg

--
-------------------------------------------------
I am Vinz, Vinz Clortho.  Keymaster of Gozer,
Volguus Zildrohar, Lord of the Sebouillia.
Are you the Gatekeeper?
-------------------------------------------------