Thread: About referential integrity.
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
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 relationaldatabases, SQL-92 and/or SQL-99, do not work thatway. Longer Answer: You can use: SELECT * FROM t1 NATURAL JOIN t2; ... to get the functionality you are talking about (provided that theforiegn key reference has the same name in both tables). -Josh
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 heartbe troubled, neither let it be afraid." John 14:27
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
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
> 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
> > 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.
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 persuasivereason to transgress the SQL standard. That is, substantialfuncitonalityneeds to be gained from the extension, sufficient tooffset the issues raised with porting applicationsto and fromPostgresql. However, there appears to be a way to give you what you want withoutviolating the standard: we could make Natural Join workforforiegn-keyed columns, not just columns with similar names. This wouldgive us some useful funcitionality without breakingthe standard. I understand from Stephan that there is some technical problem withthis, but we could add it to the TODO list, yes? -Josh Berkus
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. :)