Skip site navigation (1) Skip section navigation (2)

Relations between tables

From: rno <noospot42(at)f2s(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Relations between tables
Date: 2000-11-24 12:14:59
Message-ID: (view raw or whole thread)
Lists: pgsql-novice
Hi everyone,

Well I'm very new to this list and also very new from pgsql (and sql/db in

 However after a few weeks fighting with all this I can manage making small app
using PHP3.0.18 +pgsql 6.5.3 using one table or more but I have trouble
establishing relations in between tables for more complex apps so here my first

Let's say I have two tables, links and link_thema, and I wanna set up a third
table to establish relationship in between those. And I would wish a link might
be filed under one *or more* topic(s)...

Ok as of today I  would just create the tables following this structure:

CREATE SEQUENCE s_lthema_id;
CREATE TABLE link_thema (
     lthema_id                     INTEGER DEFAULT nextval('s_lthema_id') NOT
     lthema_name                   VARCHAR(30),
     PRIMARY KEY (lthema_id)

   link_id INTEGER DEFAULT nextval('s_link_id') NOT NULL,
   link_name VARCHAR(30),
   link_url VARCHAR(50),
   link_description TEXT,
   PRIMARY KEY(link_id)

CREATE TABLE file_link_under (
   s_link_id INTEGER,
   s_lthema_id INTEGER

And the relationship I would establish within the Query with:
where  (t0."link_id"=t1."s_link_id") and (t2."lthema_id"=t1."s_lthema_id")

But I've seen a SQL exemple that made it possible to establish a built-in
relationship with:
CREATE TABLE file_link_under (
   s_link_id INTEGER REFERENCES link,
   s_lthema_id INTEGER REFERENCES link_thema
so there was no need for the 'where' statement in the query, but PostgreSQL
doesn't recognise this REFERENCE keyword...

So is there an equivalent for PosgreSQL, and if yes what's the syntax?

This was the main question... 
some time left ?
here's another:   :) 

What do you think of the example I give, would you do it this way too? I have
no clue if I'm on a good path or not...
I read many things about syntax et al, but now I need to establish
relationships in my head just as much as in my database, in a way :)

thanks in advance,


pgsql-novice by date

Next:From: GHDate: 2000-11-24 12:18:04
Subject: Re: Re: re : PHP and persistent connections
Previous:From: Ron ChmaraDate: 2000-11-24 11:52:27
Subject: Re: Re: re : PHP and persistent connections

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group