pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAbout referential integrity.
9+ messages / 7 participants
[nested] [flat]
* About referential integrity.
@ 2002-02-25 15:01 BOURIAUD <david.bouriaud@ac-rouen.fr>
2002-02-25 17:21 ` Re: About referential integrity. Josh Berkus <josh@agliodbs.com>
2002-02-25 17:46 ` Re: About referential integrity. Oliver Elphick <olly@lfix.co.uk>
2002-02-25 19:15 ` Re: About referential integrity. Peter Eisentraut <peter_e@gmx.net>
2002-02-26 07:51 ` Re: About referential integrity. David 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 15:01 About referential integrity. BOURIAUD <david.bouriaud@ac-rouen.fr>
@ 2002-02-25 17:21 ` Josh Berkus <josh@agliodbs.com>
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 15:01 About referential integrity. BOURIAUD <david.bouriaud@ac-rouen.fr>
@ 2002-02-25 17:46 ` Oliver Elphick <olly@lfix.co.uk>
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 15:01 About referential integrity. BOURIAUD <david.bouriaud@ac-rouen.fr>
@ 2002-02-25 19:15 ` Peter Eisentraut <peter_e@gmx.net>
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-25 15:01 About referential integrity. BOURIAUD <david.bouriaud@ac-rouen.fr>
@ 2002-02-26 07:51 ` David BOURIAUD <david.bouriaud@ac-rouen.fr>
2002-02-26 08:02 ` Re: About referential integrity. Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-02-26 21:08 ` Re: About referential integrity. Josh Berkus <josh@agliodbs.com>
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-25 15:01 About referential integrity. BOURIAUD <david.bouriaud@ac-rouen.fr>
2002-02-26 07:51 ` Re: About referential integrity. David BOURIAUD <david.bouriaud@ac-rouen.fr>
@ 2002-02-26 08:02 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-02-26 19:52 ` Re: About referential integrity. Stephan Szabo <sszabo@megazone23.bigpanda.com>
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-25 15:01 About referential integrity. BOURIAUD <david.bouriaud@ac-rouen.fr>
2002-02-26 07:51 ` Re: About referential integrity. David BOURIAUD <david.bouriaud@ac-rouen.fr>
2002-02-26 08:02 ` Re: About referential integrity. Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2002-02-26 19:52 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
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-25 15:01 About referential integrity. BOURIAUD <david.bouriaud@ac-rouen.fr>
2002-02-26 07:51 ` Re: About referential integrity. David BOURIAUD <david.bouriaud@ac-rouen.fr>
@ 2002-02-26 21:08 ` Josh Berkus <josh@agliodbs.com>
2002-02-26 22:30 ` Re: About referential integrity. Stephan Szabo <sszabo@megazone23.bigpanda.com>
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-25 15:01 About referential integrity. BOURIAUD <david.bouriaud@ac-rouen.fr>
2002-02-26 07:51 ` Re: About referential integrity. David BOURIAUD <david.bouriaud@ac-rouen.fr>
2002-02-26 21:08 ` Re: About referential integrity. Josh Berkus <josh@agliodbs.com>
@ 2002-02-26 22:30 ` Stephan Szabo <sszabo@megazone23.bigpanda.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