Thread: two tables - foreign keys referring to each other...

two tables - foreign keys referring to each other...

From
Chris Czeyka
Date:
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

Re: two tables - foreign keys referring to each other...

From
Martijn van Oosterhout
Date:
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

Re: [SQL] two tables - foreign keys referring to each other...

From
Stephan Szabo
Date:
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
>