Postgres Pro
Downloads
Home   >   mailing lists

Thread: Adding Foreign Key constraints outside of table def.

Adding Foreign Key constraints outside of table def.

From
Mike Haberman
Date:
Hello all,

 I need to add foreign key support outside of the table via the
 CREATE CONSTRAINT TRIGGER .. command.

 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;

  2.  assuming no for #1 (the on delete and on update is not accepted)
      I need to do the following:

      CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "teamowner"
      NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
      "RI_FKey_cascade_upd" ('<unnamed>', 'league', 'teamowner', 'UNSPECIFIED', ' leagueownerid', 'id');

      The problem is that I am unable to remove the trigger <unnamed>

      drop trigger name on table  does not work.
      I tried "unnamed", "<unnamed>"

      I need to drop the triggers in case I reload the same schema.

   3. If I drop a table, how come the constainsts for that
   table are NOT dropped as well?

   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) ?


thanks

mike

Re: Adding Foreign Key constraints outside of table def.

From
"Gregory Wood"
Date:
>  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

Re: Adding Foreign Key constraints outside of table def.

From
Mike Haberman
Date:
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?
-------------------------------------------------