pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
two tables - foreign keys referring to each other...
8+ messages / 7 participants
[nested] [flat]

* two tables - foreign keys referring to each other...
@ 2001-02-21 03:30 Chris Czeyka <czeyka@skwea.co.jp>
  2001-02-21 04:00 ` Re: two tables - foreign keys referring to each other... Martijn van Oosterhout <kleptog@svana.org>
  2001-02-21 05:09 ` Re: two tables - foreign keys referring to each other... Chris Czeyka <czeyka@skwea.co.jp>
  2001-02-21 07:34 ` Re: [SQL] two tables - foreign keys referring to each other... Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-02-21 09:02 ` Re: two tables - foreign keys referring to each other... Grigoriy G. Vovk <Grigoriy.Vovk@linustech.com.cy>
  0 siblings, 4 replies; 8+ messages in thread

From: Chris Czeyka @ 2001-02-21 03:30 UTC (permalink / raw)
  To: pgsql-sql; pgsql-general@postgresql.org

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



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: two tables - foreign keys referring to each other...
  2001-02-21 03:30 two tables - foreign keys referring to each other... Chris Czeyka <czeyka@skwea.co.jp>
@ 2001-02-21 04:00 ` Martijn van Oosterhout <kleptog@svana.org>
  3 siblings, 0 replies; 8+ messages in thread

From: Martijn van Oosterhout @ 2001-02-21 04:00 UTC (permalink / raw)
  To: Chris Czeyka <czeyka@skwea.co.jp>; +Cc: pgsql-sql; pgsql-general@postgresql.org

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



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: two tables - foreign keys referring to each other...
  2001-02-21 03:30 two tables - foreign keys referring to each other... Chris Czeyka <czeyka@skwea.co.jp>
@ 2001-02-21 05:09 ` Chris Czeyka <czeyka@skwea.co.jp>
  3 siblings, 0 replies; 8+ messages in thread

From: Chris Czeyka @ 2001-02-21 05:09 UTC (permalink / raw)
  To: Kirby C. Bohling <kbohling@birddog.com>; +Cc: Martijn van Oosterhout <kleptog@svana.org>; pgsql-sql

ThX Kirby, ThX Martijn,

as you can see I'm new to the SQL- and database world.
My policy for this project is to FORCE an admin to be in an institute. If one
admin behaves bad for the database project the institute is responsible for
her/him. This institute would be represented by a special admin (therefore the
link back).

Anyway, I see that crosslinking is really a little bit tricky...
I will do this check in the application level (Java) and see, if this is easier
and even necessary. For now I might use only admin(fk)->institute. This is
necessary to trace back responsibility.

as beginner I appreciate good hints,

cheers,
Chris

> 
>         IMHO, you should consider not having the admin table have a link to the
> institute table.  If you want to find the institute a particular admin
> is connected with, find that look in the institutes table.  The astute
> reader will note the advice is symmetric, you can not have a link from
> the institute to the admin.  If you don't want to have dangling admin's
> you might be able to get a trigger/stored procedure to do it for you
> (Note:I done triggers in Oracle, never in PostGres so take that with a
> grain of salt.  I would be shocked if you couldn't do it with a Trigger
> under PostGres.  I believe it us commonly done with long objects as a
> matter of fact).
> 
>         Do what you like, and I hope this helps.
> 
>                 Kirby
>



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: [SQL] two tables - foreign keys referring to each other...
  2001-02-21 03:30 two tables - foreign keys referring to each other... Chris Czeyka <czeyka@skwea.co.jp>
@ 2001-02-21 07:34 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-02-21 08:38   ` Re: two tables - foreign keys referring to each other... Frank Joerdens <frank@joerdens.de>
  3 siblings, 1 reply; 8+ messages in thread

From: Stephan Szabo @ 2001-02-21 07:34 UTC (permalink / raw)
  To: Chris Czeyka <czeyka@skwea.co.jp>; +Cc: pgsql-sql; pgsql-general@postgresql.org


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
> 




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: two tables - foreign keys referring to each other...
  2001-02-21 03:30 two tables - foreign keys referring to each other... Chris Czeyka <czeyka@skwea.co.jp>
  2001-02-21 07:34 ` Re: [SQL] two tables - foreign keys referring to each other... Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2001-02-21 08:38   ` Frank Joerdens <frank@joerdens.de>
  0 siblings, 0 replies; 8+ messages in thread

From: Frank Joerdens @ 2001-02-21 08:38 UTC (permalink / raw)
  To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: Chris Czeyka <czeyka@skwea.co.jp>; pgsql-sql

On Tue, Feb 20, 2001 at 11:34:30PM -0800, Stephan Szabo wrote:
> 
> You have to use ALTER TABLE to add the constraint to one of the tables.

Maybe I am stating the obvious but you should make sure that you include
the ALTER TABLE statements in the *.sql files that you use to create the
tables, rather than running them from the psql prompt. Otherwise you'll
have trouble to figure out what exactly you did when you come back to
the database later; as foreign keys don't show up as 'foreign keys' in
schema dumps, but as a set of <unnamed> triggers. Those are quite hard
to read, or interpret as what they essentially are, i.e. foreign keys
(depending on your philosophical outlook, that is, whether you consider
the essence of your foreign keys to be a set of triggers, or vice versa
;-)).

Regards, Frank



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: two tables - foreign keys referring to each other...
  2001-02-21 03:30 two tables - foreign keys referring to each other... Chris Czeyka <czeyka@skwea.co.jp>
@ 2001-02-21 09:02 ` Grigoriy G. Vovk <Grigoriy.Vovk@linustech.com.cy>
  3 siblings, 0 replies; 8+ messages in thread

From: Grigoriy G. Vovk @ 2001-02-21 09:02 UTC (permalink / raw)
  To: Chris Czeyka <czeyka@skwea.co.jp>; +Cc: pgsql-sql

I think, if it is relationship many-to-many (one admin can be in many
institute, and one institute can has many admin, you should use relation
table, see below.

> -----------------> 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 institute_admin (
	row	int 		primary key,
	name	varchar(48)	references institute_t,
	login 	varchar(16)	references admin_t
);

> 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;
>

If you have diffarant relation, describe it.

-----------------------------
Grigoriy G. Vovk




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* two tables - foreign keys referring to each other...]
@ 2001-02-21 16:42 Josh Berkus <josh@agliodbs.com>
  2001-02-22 17:11 ` Re: two tables - foreign keys referring to each other...] J.H.M. Dassen (Ray) <jdassen@cistron.nl>
  0 siblings, 1 reply; 8+ messages in thread

From: Josh Berkus @ 2001-02-21 16:42 UTC (permalink / raw)
  To: pgsql-sql

Chris,

> ..or generally: how do you create two crosslinked foreign keyed tables?
> 
> hopefully an easy problem for the real professionals!

	No.  The problem is:  Why would you want to create two crosslinked
foriegn-keyed tables?  As an experienced SQL professional, this seems
like a recipe for instant disaster to me.

	The purpose of a foriegn key is to enforce a parent-->child or data
table<--reference list relationships, to prevent incomplete or erroneous
records from being added.  This requires the foriegn key relationship to
be one-way.

	In fact, if you built your two-way foriegn keys (using ALTER TABLE), I
think you might find that you can't add any records to either table. 
Certainly you won't be able to delete any.  I once had an experience
with a complex legacy data structure where I accidentally set up a
circular foriegn key relationship (among 5 tables), and I had to
re-build the database from scripts to fix it.

	If you're looking to set up a many-to-many relationship, then what you
want is a linking table.  However, I highly advise you to pick up a
primer on database design (such as "Database Design for Mere Mortals")
before proceeding any further, or you'll end up spending the next year
paying for what you don't understand now.

					-Josh Berkus
-- 
______AGLIO DATABASE SOLUTIONS___________________________
                                        Josh Berkus
   Complete information technology      josh@agliodbs.com
    and data management solutions       (415) 565-7293
   for law firms, small businesses       fax  621-2533
    and non-profit organizations.       San Francisco



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: two tables - foreign keys referring to each other...]
  2001-02-21 16:42 two tables - foreign keys referring to each other...] Josh Berkus <josh@agliodbs.com>
@ 2001-02-22 17:11 ` J.H.M. Dassen (Ray) <jdassen@cistron.nl>
  0 siblings, 0 replies; 8+ messages in thread

From: jdassen@cistron.nl @ 2001-02-22 17:11 UTC (permalink / raw)
  To: pgsql-sql

Josh Berkus <josh@agliodbs.com> wrote:
>In fact, if you built your two-way foriegn keys (using ALTER TABLE), I
>think you might find that you can't add any records to either table.
>Certainly you won't be able to delete any.

It can be done: use DEFERRABLE foreign key constraints (see
http://www.postgresql.org/docs/aw_pgsql_book/node161.html)

Ray
-- 
Personally, I guess I'd favor a sort of modified form of medieval justice.
The idea is that you throw the lawyers for both parties into a pond. The
person whose lawyer sinks would be declared the winner.
        Rob Kleinschmidt 




^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2001-02-22 17:11 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-02-21 03:30 two tables - foreign keys referring to each other... Chris Czeyka <czeyka@skwea.co.jp>
2001-02-21 04:00 ` Martijn van Oosterhout <kleptog@svana.org>
2001-02-21 05:09 ` Chris Czeyka <czeyka@skwea.co.jp>
2001-02-21 07:34 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-02-21 08:38   ` Frank Joerdens <frank@joerdens.de>
2001-02-21 09:02 ` Grigoriy G. Vovk <Grigoriy.Vovk@linustech.com.cy>
2001-02-21 16:42 two tables - foreign keys referring to each other...] Josh Berkus <josh@agliodbs.com>
2001-02-22 17:11 ` Re: two tables - foreign keys referring to each other...] J.H.M. Dassen (Ray) <jdassen@cistron.nl>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox