Re: Two tables refenceing each other's columns
От | Robert B. Easter |
---|---|
Тема | Re: Two tables refenceing each other's columns |
Дата | |
Msg-id | 01010202272804.09559@comptechnews обсуждение исходный текст |
Ответ на | Two tables refenceing each other's columns (GH <grasshacker@over-yonder.net>) |
Ответы |
Re: Two tables refenceing each other's columns
(GH <grasshacker@over-yonder.net>)
|
Список | 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/ ------------
В списке pgsql-general по дате отправления: