Re: [GENERAL] Two tables refenceing each other's columns
You can use ALTER TABLE ADD CONSTRAINT to add foreign key constraints after table creation. On Tue, 2 Jan 2001, 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
[GENERAL] Two tables refenceing each other's columns
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
Re: [GENERAL] Two tables refenceing each other's columns
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 [EMAIL PROTECTED] -
- CompTechNews Message Board http://www.comptechnews.com/ -
- CompTechServ Tech Services http://www.comptechserv.com/ -
-- http://www.comptechnews.com/~reaster/
Re: [GENERAL] Two tables refenceing each other's columns
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 [EMAIL PROTECTED] -
- CompTechNews Message Board http://www.comptechnews.com/ -
- CompTechServ Tech Services http://www.comptechserv.com/ -
-- http://www.comptechnews.com/~reaster/
