Re: Two tables refenceing each other's columns - Mailing list pgsql-general

On Tue, Jan 02, 2001 at 02:27:28AM -0500, some SMTP stream spewed forth:
> Here is some code I played with before. It does what you want.  Just make a
> new database to try it in.

Great, thanks.
I ended up working around it by storing one of the primary keys
in another table with some other information (which is a better way
to have the tables setup, in my case).

I am glad to have your code on hand though.


Thanks

dan

>
>
>
> -- Load the PGSQL procedural language
> -- This could also be done with the createlang script/program.
> -- See man createlang.
> CREATE FUNCTION plpgsql_call_handler()
>         RETURNS OPAQUE AS '/usr/local/pgsql/lib/plpgsql.so'
>         LANGUAGE 'C';
> CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
>         HANDLER plpgsql_call_handler
>         LANCOMPILER 'PL/pgSQL';
>
> CREATE TABLE test1 (id INTEGER PRIMARY KEY);
> CREATE TABLE test2 (id INTEGER PRIMARY KEY);
>
> ALTER TABLE test1 ADD CONSTRAINT test1_id_fk
>         FOREIGN KEY (id) REFERENCES test2
>         ON UPDATE CASCADE
>         ON DELETE CASCADE -- can delete test2
>         INITIALLY DEFERRED;
> ALTER TABLE test2 ADD CONSTRAINT test1_id_fk
>         FOREIGN KEY (id) REFERENCES test1
>         ON UPDATE CASCADE
>         ON DELETE RESTRICT -- disallows delete test1
>         INITIALLY DEFERRED;
>
> CREATE SEQUENCE test_id_seq;
>
> CREATE FUNCTION new_tests() RETURNS INTEGER AS '
> DECLARE
>         new_seq INTEGER;
> BEGIN
>         new_seq := nextval(''test_id_seq'');
>         INSERT INTO test1 VALUES (new_seq);
>         INSERT INTO test2 VALUES (new_seq);
>         RETURN new_seq;
> END;
> ' LANGUAGE 'plpgsql';
>
> -- implicit BEGIN;
> SELECT new_tests();
> -- implicit COMMIT;
> SELECT new_tests();
> SELECT new_tests();
>
> SELECT * FROM test1;
> SELECT * FROM test2;
>
> DELETE FROM test1 WHERE id = 1; -- this will fail
> DELETE FROM test2 WHERE id = 1; -- this will succeed and cacade
>
> SELECT * FROM test1;
> SELECT * FROM test2;
>
>
>
>
> On Tuesday 02 January 2001 01:57, GH wrote:
> > Is something like the following allowed (or is not a Bad Idea)?
> >
> > table1
> > ---------+-------------------------
> > id1      |serial primary key
> > col2     |int references table2(id2)
> >
> > table2
> > ---------+-------------------------
> > id2      |serial primary key
> > col2     |int references table1(id1)
> >
> >
> > Obviously, creating the tables is a problem since the constraints require
> > that the other table exists.
> > If doing the above is *not* a Bad Idea, how could I work around this
> > problem?
> > (That is, (how) can I add the constraints after table creation?
> > I imagine something with "create constraint trigger", but the manual is
> > not very clear on that.)
> >
> >
> > Thanks
> >
> > dan
>
> --
> -------- Robert B. Easter  reaster@comptechnews.com ---------
> - CompTechNews Message Board   http://www.comptechnews.com/ -
> - CompTechServ Tech Services   http://www.comptechserv.com/ -
> ---------- http://www.comptechnews.com/~reaster/ ------------