On 25/08/15 14:45, David G. Johnston wrote:
On Mon, Aug 24, 2015 at 10:02 PM, Gavin Flower
<GavinFlower@xxxxxxxxxxxxxxxxx
<mailto:GavinFlower@xxxxxxxxxxxxxxxxx>>wrote:
Also the best practice is to make the primary key name 'id' as you
do know the table it is in, so prepending the table name is
redundant - so you can clearly identify foreign keys because the
suffix '_id 'is prepended by the table name of the referenced
table. Hence 'id' is a primary key, and account_id is a foreign
key pointing into the account table.
I would much rather be able to write:
SELECT parent_id, child_id, [...]
FROM parent
JOIN child USING (parent_id)
instead of
SELECT parent.id <http://parent.id> AS parent_id, child.id
<http://child.id> AS child_id, [...]
FROM parent
JOIN child ON (parent.id <http://parent.id> = child.parent_id)
Yes, looking at the parent table it is obvious that the id you are
looking at is the "parent" id. But as soon as you join two or more
tables you are guaranteed to have multiple columns with the name "id"
that you now need to disambiguate.
The column name "table_id" refers to the primary identifier for that
entity no matter where it appears. I'd rather have one redundant
situation than one exception to the rule.
David J.
Hmm...
I consider it good practice to always give an alias for each table used,
especially for non trivial SQL statements.
So I think the above would look better (using slightly more realistic
table names) as:
SELECT
c.id,
s.id,
[...]
FROM
company c
JOIN shop s USING (s.company_id = c.id);
Which is I think a lot clearer (it is obvious that you are joining a
foreign key with a primary key), and you can add more stuff without it
suddenly becoming ambiguous.
I once wrote a Sybase stored proc with over 3000 lines of SQL (not
practical to split it up, unfortunately), individual selects were often
over half a page. It interrogated 17 tables from two different
databases and needed 5 temporary tables.
Cheers,
Gavin
P.S. the use of '[...]' was started by me way back in the heyday of
usenet, in the beginning of the 1990's! Previously people used '[
omitted ]'.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
