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
>