Marshall <[EMAIL PROTECTED]> wrote:
+---------------
| Joachim Durchholz wrote:
| > Actually SQL has references - they are called "primary keys", but they
| > are references nevertheless.
|
| I strongly object; this is quite incorrect. I grant you that from the
| 50,000 foot level they appear identical, but they are not.
+---------------
Agreed. The only thing different about "primary" keys from any other
key is uniqueness -- a selection by primary key will return only one
record. Other than that constraint, many databases treat them exactly
the same as non-primary keys [e.g., can form indexes on them, etc.].
+---------------
| To qualify as a reference, there need to be reference and dereference
| operations on the reference datatype; there is no such operation is SQL.
+---------------
Not in "ANSI SQL92", say, but there might be in most SQL databases!
[See below re OIDs. Also, SQL:1999 had a "REF" type that was essentially
and OID.]
+---------------
| Would you say the relational algebra has references?
+---------------
Don't confuse "SQL" & "relational algebra"!! You'll get real
relational algebraists *way* bent out of shape if you do that!
+---------------
| > (Some SQL dialects also offer synthetic "ID" fields that are
| > guaranteed to remain stable over the lifetime of a record.
|
| Primary keys are updatable; there is nothing special about them.
+---------------
I think he's probably talking about "OIDs" (object IDs). Most
current SQL-based databases provide them, usually as a normally-
invisible "system column" that doesn't show up when you say
"SELECT * FROM", but that *does* appear if you say "SELECT oid, *",
and may be used as a "primary" key even on tables with no actual
primary key:
rpw3=# select * from toy limit 4;
c1 | c2 | c3 | upd
--------+-------+--------------------------------+-----
fall | tape | My Favorite Thanksgiving | 16
xmas | book | My Favorite Christmas | 2
xmas | video | The Grinch who Stole Christmas | 4
summer | book | Unusual 4ths of July | 17
(4 rows)
rpw3=# select oid, * from toy limit 4;
oid | c1 | c2 | c3 | upd
-------+--------+-------+--------------------------------+-----
19997 | fall | tape | My Favorite Thanksgiving | 16
19998 | xmas | book | My Favorite Christmas | 2
19999 | xmas | video | The Grinch who Stole Christmas | 4
20000 | summer | book | Unusual 4ths of July | 17
(4 rows)
rpw3=# select * from toy where oid = 19998;
c1 | c2 | c3 | upd
------+------+-----------------------+-----
xmas | book | My Favorite Christmas | 2
(1 row)
rpw3=# insert into toy values ('fall','book','Glory Road');
INSERT 32785 1
rpw3=# select oid, * from toy where oid = 32785;
oid | c1 | c2 | c3 | upd
-------+------+------+------------+-----
32785 | fall | book | Glory Road | 21
(1 row)
rpw3=#
See <http://www.postgresql.org/docs/8.1/static/datatype-oid.html>
for how PostgreSQL treats OIDs [including some critical limitations].
-Rob
-----
Rob Warnock <[EMAIL PROTECTED]>
627 26th Avenue <URL:http://rpw3.org/>
San Mateo, CA 94403 (650)572-2607
--
http://mail.python.org/mailman/listinfo/python-list
