Marshall <marshall.spight@gmail.com> 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			<rpw3@rpw3.org>
627 26th Avenue			<URL:http://rpw3.org/>
San Mateo, CA 94403		(650)572-2607