Thread: two tables - foreign keys referring to each other...
Hey to all,
I got two tables, linked to each other. How can I tell the first CREATE TABLE
(institute_t) to wait to check the foreign key for the second table??? just
like "hold on a little bit... you'll receive your admin_t" :-) ? I thoght
DEFERRABLE, DEFERRED and transaction with BEGIN/COMMIT take care of this.
..or generally: how do you create two crosslinked foreign keyed tables?
hopefully an easy problem for the real professionals!
-----------------> here we go
BEGIN; -- begin table transaction -- Only Postgresql
CREATE TABLE institute_t (
name VARCHAR(48) PRIMARY KEY,
street VARCHAR(48) NOT NULL,
zip VARCHAR(16),
town VARCHAR(32) NOT NULL,
country CHAR(2) NOT NULL, /* country codes ISO-3166*/
phone VARCHAR(32) NOT NULL,
fax VARCHAR(32),
admin VARCHAR(16) REFERENCES admin_t
ON UPDATE CASCADE
ON DELETE SET NULL
DEFERRABLE
INITIALLY DEFERRED
);
CREATE TABLE admin_t (
login VARCHAR(16) PRIMARY KEY,
password VARCHAR(16) NOT NULL,
email VARCHAR(32) NOT NULL,
real_name VARCHAR(32) NOT NULL,
street VARCHAR(48) NOT NULL,
zip VARCHAR(16),
town VARCHAR(32) NOT NULL,
country CHAR(2) NOT NULL, /* country codes -- refer to
ISO-3166*/
phone VARCHAR(32) NOT NULL,
fax VARCHAR(32),
access INTEGER NOT NULL,
institute VARCHAR(48) REFERENCES institute_t
ON UPDATE CASCADE
ON DELETE SET NULL
DEFERRABLE
INITIALLY DEFERRED
);
COMMIT;
of course I get the ERROR, that admin_t doesn't exist. So? help the stupid!
pls!
best greets,
Chris
On Wed, Feb 21, 2001 at 12:30:51PM +0900, Chris Czeyka wrote: [big snip] > of course I get the ERROR, that admin_t doesn't exist. So? help the stupid! > pls! I think the general idea is to create the tables first without the constraints and then do ALTER TABLE ADD CONSTRAINT afterwards. HTH, Martijn
You have to use ALTER TABLE to add the constraint to one of the tables. Deferred refers to the checking of the constraint itself, not really to the check to see if the table is there. On Wed, 21 Feb 2001, Chris Czeyka wrote: > Hey to all, > > I got two tables, linked to each other. How can I tell the first CREATE TABLE > (institute_t) to wait to check the foreign key for the second table??? just > like "hold on a little bit... you'll receive your admin_t" :-) ? I thoght > DEFERRABLE, DEFERRED and transaction with BEGIN/COMMIT take care of this. > > ..or generally: how do you create two crosslinked foreign keyed tables? > > hopefully an easy problem for the real professionals! > > > -----------------> here we go > BEGIN; -- begin table transaction -- Only Postgresql > CREATE TABLE institute_t ( > name VARCHAR(48) PRIMARY KEY, > street VARCHAR(48) NOT NULL, > zip VARCHAR(16), > town VARCHAR(32) NOT NULL, > country CHAR(2) NOT NULL, /* country codes ISO-3166*/ > phone VARCHAR(32) NOT NULL, > fax VARCHAR(32), > admin VARCHAR(16) REFERENCES admin_t > ON UPDATE CASCADE > ON DELETE SET NULL > DEFERRABLE > INITIALLY DEFERRED > ); > > CREATE TABLE admin_t ( > login VARCHAR(16) PRIMARY KEY, > password VARCHAR(16) NOT NULL, > email VARCHAR(32) NOT NULL, > real_name VARCHAR(32) NOT NULL, > street VARCHAR(48) NOT NULL, > zip VARCHAR(16), > town VARCHAR(32) NOT NULL, > country CHAR(2) NOT NULL, /* country codes -- refer to > ISO-3166*/ > phone VARCHAR(32) NOT NULL, > fax VARCHAR(32), > access INTEGER NOT NULL, > institute VARCHAR(48) REFERENCES institute_t > ON UPDATE CASCADE > ON DELETE SET NULL > DEFERRABLE > INITIALLY DEFERRED > ); > COMMIT; > > > of course I get the ERROR, that admin_t doesn't exist. So? help the stupid! > pls! > > best greets, > Chris >