pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
About referential integrity.
9+ messages / 7 participants
[nested] [flat]

* About referential integrity.
@ 2002-02-25 15:01  BOURIAUD <david.bouriaud@ac-rouen.fr>
  0 siblings, 4 replies; 9+ messages in thread

From: BOURIAUD @ 2002-02-25 15:01 UTC (permalink / raw)
  To: pgsql-sql

Hi there !
I've just subscribed to this list because I found something quite annoying 
about postgres and SQL querries as well.
Indeed, as far as I know, postgreSQL is reffered as a SGBD-R, the 'R' meaning 
relational.

Anyway, I don't understand why when tables are built with this referential 
integrity the back-end doesn't fullfill any request posted on those tables.

Since I fear I'm not clear in the above, let me show you an example.


Let's say I create two tables as shown below :

create table t1
(
	key1 serial PRIMARY KEY,
	field1 varchar(30)
);

create table t2
(
	key2 serial not null PRIMARY KEY,
	key1 int4 references t1,
	field2 varchar(30)
); 

As far as I know, t2 and t1 are linked together. If I want to select rows 
that belong to both tables, I would have to write something like this :
select * from t1, t2 where t1.key1 = t2.key1;

I think this is quite redundant, since I've created my tables so as to make 
sure that they would respect what is in the select above !!!!


So, why souldn't I be abble to write :
select * from t1, t2;

and have the backend returns me only the rows that have t1.key1 = t2.key2 ???

If anyone have any clue about it, please let me know.

I know that this is not a postgreSQL bug, since there is NO SGBDR acting so 
(as far as I know anyway). But I'd like to know why such SGBD, said to be 
Relationnal don't do this by themselves.
Thanks for your clues and for having read me.
-- 
David BOURIAUD
----------------------------------------------------------
In a world without walls or fences, what use do we have 
for windows or gates ?
----------------------------------------------------------
ICQ#102562021




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

* Re: About referential integrity.
@ 2002-02-25 17:21  Josh Berkus <josh@agliodbs.com>
  parent: BOURIAUD <david.bouriaud@ac-rouen.fr>
  3 siblings, 0 replies; 9+ messages in thread

From: Josh Berkus @ 2002-02-25 17:21 UTC (permalink / raw)
  To: BOURIAUD <david.bouriaud@ac-rouen.fr>; pgsql-sql

David,

> Anyway, I don't understand why when tables are built with this
>  referential 
> integrity the back-end doesn't fullfill any request posted on those
>  tables.

Short answer:   Because the international specifications for relational
 databases, SQL-92 and/or SQL-99, do not work that way.

Longer Answer:  You can use:

SELECT * FROM t1 NATURAL JOIN t2;

... to get the functionality you are talking about (provided that the
 foriegn key reference has the same name in both tables).

-Josh



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

* Re: About referential integrity.
@ 2002-02-25 17:46  Oliver Elphick <olly@lfix.co.uk>
  parent: BOURIAUD <david.bouriaud@ac-rouen.fr>
  3 siblings, 0 replies; 9+ messages in thread

From: Oliver Elphick @ 2002-02-25 17:46 UTC (permalink / raw)
  To: BOURIAUD <david.bouriaud@ac-rouen.fr>; +Cc: pgsql-sql

On Mon, 2002-02-25 at 15:01, BOURIAUD wrote:
> Since I fear I'm not clear in the above, let me show you an example.
> 
> 
> Let's say I create two tables as shown below :
> 
> create table t1
> (
> 	key1 serial PRIMARY KEY,
> 	field1 varchar(30)
> );
> 
> create table t2
> (
> 	key2 serial not null PRIMARY KEY,
> 	key1 int4 references t1,
> 	field2 varchar(30)
> ); 
> 
> As far as I know, t2 and t1 are linked together. If I want to select rows 
> that belong to both tables, I would have to write something like this :
> select * from t1, t2 where t1.key1 = t2.key1;
> 
> I think this is quite redundant, since I've created my tables so as to make 
> sure that they would respect what is in the select above !!!!
> 
> 
> So, why souldn't I be abble to write :
> select * from t1, t2;
> 
> and have the backend returns me only the rows that have t1.key1 = t2.key2 ???

That is not what SQL does.

The purpose of referential integerity is to ensure that a value in one
table corresponds to a row in another.  It operates on database changes;
it does not extend to select queries.
-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C

     "Peace I leave with you, my peace I give unto you; not 
      as the world giveth, give I unto you. Let not your 
      heart be troubled, neither let it be afraid."         
                                John 14:27 




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

* Re: About referential integrity.
@ 2002-02-25 19:15  Peter Eisentraut <peter_e@gmx.net>
  parent: BOURIAUD <david.bouriaud@ac-rouen.fr>
  3 siblings, 0 replies; 9+ messages in thread

From: Peter Eisentraut @ 2002-02-25 19:15 UTC (permalink / raw)
  To: BOURIAUD <david.bouriaud@ac-rouen.fr>; +Cc: pgsql-sql

BOURIAUD writes:

> So, why souldn't I be abble to write :
> select * from t1, t2;
>
> and have the backend returns me only the rows that have t1.key1 = t2.key2 ???

If that's what you want you can write

select * from t1 natural join t2;

-- 
Peter Eisentraut   peter_e@gmx.net




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

* Re: About referential integrity.
@ 2002-02-26 07:51  David BOURIAUD <david.bouriaud@ac-rouen.fr>
  parent: BOURIAUD <david.bouriaud@ac-rouen.fr>
  3 siblings, 2 replies; 9+ messages in thread

From: David BOURIAUD @ 2002-02-26 07:51 UTC (permalink / raw)
  To: pgsql-sql

Le Lundi 25 Février 2002 16:01, vous avez écrit :

Hi to all !
First of all, thanks to the ones who answered me, for I didn't know that it 
wasn't a SQL specification, though I suspected it, and I didn't even suspect 
that it could roughly done with the "NATURAL JOIN" specificated in the "FROM" 
clause.

Anyway, couldn't it be an extention to postgreSQL ? If you have heard about 
Business Objects, you should know that it can do it very well provided you 
draw the relationchips between the tables of your database. And it's of much 
use, since you just have to concentrate on the fields you want to fetch and 
the program takes care of the relations and handles group by, order by and 
where clause.

As of PostgreSQL, I don't think it would be a hard thing to put on, since the 
relations are stored in a system table, including the fields the tables are 
linked by. A sort of syntax :

select *
from AUTO JOIN t1, t2;

should do the stuff, and leave the SQL/92 and 99 specifications alone !


-- 
David BOURIAUD
----------------------------------------------------------
In a world without walls or fences, what use do we have 
for windows or gates ?
----------------------------------------------------------
ICQ#102562021



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

* Re: About referential integrity.
@ 2002-02-26 08:02  Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  parent: David BOURIAUD <david.bouriaud@ac-rouen.fr>
  1 sibling, 1 reply; 9+ messages in thread

From: Christopher Kings-Lynne @ 2002-02-26 08:02 UTC (permalink / raw)
  To: David BOURIAUD <david.bouriaud@ac-rouen.fr>; pgsql-sql

> Anyway, couldn't it be an extention to postgreSQL ? If you have 
> heard about 
> Business Objects, you should know that it can do it very well 
> provided you 
> draw the relationchips between the tables of your database. And 
> it's of much 
> use, since you just have to concentrate on the fields you want to 
> fetch and 
> the program takes care of the relations and handles group by, 
> order by and 
> where clause.

Nope - that's the job of the client program (ie. Business Objects)

> As of PostgreSQL, I don't think it would be a hard thing to put 
> on, since the 
> relations are stored in a system table, including the fields the 
> tables are 
> linked by. A sort of syntax :
> 
> select *
> from AUTO JOIN t1, t2;

How is this different from:

select *
from t1 NATURAL JOIN t2;

???

Chris




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

* Re: About referential integrity.
@ 2002-02-26 19:52  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  0 siblings, 0 replies; 9+ messages in thread

From: Stephan Szabo @ 2002-02-26 19:52 UTC (permalink / raw)
  To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: David BOURIAUD <david.bouriaud@ac-rouen.fr>; pgsql-sql


> > As of PostgreSQL, I don't think it would be a hard thing to put
> > on, since the
> > relations are stored in a system table, including the fields the
> > tables are
> > linked by. A sort of syntax :
> >
> > select *
> > from AUTO JOIN t1, t2;
>
> How is this different from:
>
> select *
> from t1 NATURAL JOIN t2;
>
> ???

I think he still wants it to work even if the columns aren't named
the same.  In any case, this isn't likely to be reasonable
right now due to the format of that information in the system
tables.




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

* Re: About referential integrity.
@ 2002-02-26 21:08  Josh Berkus <josh@agliodbs.com>
  parent: David BOURIAUD <david.bouriaud@ac-rouen.fr>
  1 sibling, 1 reply; 9+ messages in thread

From: Josh Berkus @ 2002-02-26 21:08 UTC (permalink / raw)
  To: David BOURIAUD <david.bouriaud@ac-rouen.fr>; pgsql-sql

David,

> Anyway, couldn't it be an extention to postgreSQL ? If you have heard
>  about 
> Business Objects, you should know that it can do it very well
>  provided you 
> draw the relationchips between the tables of your database. And it's
>  of much 
> use, since you just have to concentrate on the fields you want to
>  fetch and 
> the program takes care of the relations and handles group by, order
>  by and 
> where clause.

Certainly it could.  However, the project needs a really persuasive
 reason to transgress the SQL standard.  That is, substantial
 funcitonality needs to be gained from the extension, sufficient to
 offset the issues raised with porting applications to and from
 Postgresql. 

However, there appears to be a way to give you what you want without
 violating the standard:  we could make Natural Join work for
 foriegn-keyed columns, not just columns with similar names. This would
 give us some useful funcitionality without breaking the standard.

I understand from Stephan that there is some technical problem with
 this, but we could add it to the TODO list, yes?

-Josh Berkus



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

* Re: About referential integrity.
@ 2002-02-26 22:30  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 9+ messages in thread

From: Stephan Szabo @ 2002-02-26 22:30 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: David BOURIAUD <david.bouriaud@ac-rouen.fr>; pgsql-sql


On Tue, 26 Feb 2002, Josh Berkus wrote:

> David,
>
> > Anyway, couldn't it be an extention to postgreSQL ? If you have heard
> >  about
> > Business Objects, you should know that it can do it very well
> >  provided you
> > draw the relationchips between the tables of your database. And it's
> >  of much
> > use, since you just have to concentrate on the fields you want to
> >  fetch and
> > the program takes care of the relations and handles group by, order
> >  by and
> > where clause.
>
> Certainly it could.  However, the project needs a really persuasive
>  reason to transgress the SQL standard.  That is, substantial
>  funcitonality needs to be gained from the extension, sufficient to
>  offset the issues raised with porting applications to and from
>  Postgresql.
>
> However, there appears to be a way to give you what you want without
>  violating the standard:  we could make Natural Join work for
>  foriegn-keyed columns, not just columns with similar names. This would
>  give us some useful funcitionality without breaking the standard.

If we were to do this, I'd say using NATURAL JOIN is a bad idea.  It'd
be better to make a new join type since otherwise it'd have unexpected
behavior for someone who knew the spec but not the change.

Also, this still needs more definition.  What do we do with multiple
foreign keys, especially ones where the schema has recursive ones?  What
about match types and NULLs -- is it equality or "matchness" we care
about? It may be a good idea, but it needs some fleshing out. :)




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


end of thread, other threads:[~2002-02-26 22:30 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-02-25 15:01 About referential integrity. BOURIAUD <david.bouriaud@ac-rouen.fr>
2002-02-25 17:21 ` Josh Berkus <josh@agliodbs.com>
2002-02-25 17:46 ` Oliver Elphick <olly@lfix.co.uk>
2002-02-25 19:15 ` Peter Eisentraut <peter_e@gmx.net>
2002-02-26 07:51 ` David BOURIAUD <david.bouriaud@ac-rouen.fr>
2002-02-26 08:02   ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-02-26 19:52     ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-02-26 21:08   ` Josh Berkus <josh@agliodbs.com>
2002-02-26 22:30     ` Stephan Szabo <sszabo@megazone23.bigpanda.com>

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