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

Here is some code I played with before. It does what you want.  Just make a
new database to try it in.



-- 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/ ------------