Relations between tables - Mailing list pgsql-novice

From rno
Subject Relations between tables
Date
Msg-id 3A1E5BC3.E8C1512B@f2s.com
Whole thread Raw
List pgsql-novice
Hi everyone,


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

 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
questions:

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
NULL,
     lthema_name                   VARCHAR(30),
     PRIMARY KEY (lthema_id)
 );

CREATE SEQUENCE s_link_id;
CREATE TABLE link (
   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:
<snip>
where  (t0."link_id"=t1."s_link_id") and (t2."lthema_id"=t1."s_lthema_id")
<snip>

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,

seeyou,
--
rno
      mailto:noospot42@f2s.com
      http://www.nova-cinema.com/



pgsql-novice by date:

Previous
From: GH
Date:
Subject: Re: Re: re : PHP and persistent connections
Next
From: ghaverla@freenet.edmonton.ab.ca
Date:
Subject: Re: Skipping numbers in a sequence.