Received: from calf.in.skwea.co.jp ([210.161.254.3]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f1L3UPx09881; Tue, 20 Feb 2001 22:30:25 -0500 (EST) (envelope-from czeyka@skwea.co.jp) Received: from skwea.co.jp ([172.17.2.87]) by calf.in.skwea.co.jp (8.9.3/8.9.3) with ESMTP id MAA11814; Wed, 21 Feb 2001 12:19:03 +0900 Message-ID: <3A93366B.DC0B83F4@skwea.co.jp> Date: Wed, 21 Feb 2001 12:30:51 +0900 From: Chris Czeyka Organization: SKW East Asia X-Mailer: Mozilla 4.73 [ja] (Win98; U) X-Accept-Language: de-DE,en,ja MIME-Version: 1.0 To: pgsql-sql@postgresql.org, pgsql-general@postgresql.org Subject: two tables - foreign keys referring to each other... Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200102/1041 X-Sequence-Number: 4960 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