Re: ordering of primary key columsn

Eric,

In the SQL model, columns in all table constrains (unique, primary key, foreign key) are ordered. This isn't terribly explicit throughout the SQL spec, but the existence of an ORDINAL_POSITION column in the KEY_COLUMN_USAGE table in the INFORMATION_SCHEMA makes it very clear. The description of this column in SQL 2008 reads:

“The value of ORDINAL_POSITION is the ordinal position of the specific column in the constraint being described. If the constraint described is a key of cardinality 1 (one), then the value of ORDINAL_POSITION is always 1 (one).”

One could add an informative note to the DM spec along these lines:

[[
Note: The order of a primary key constraint's columns is determined by the DDL statement used to create it. In SQL implementations that support the information schema, this order can be accessed through the INFORMATION_SCHEMA.KEY_COLUMN_USAGE.ORDINAL_POSITION column.
]]

Best,
Richard


On 15 Aug 2012, at 08:47, Eric Prud'hommeaux wrote:

> * Paul Tyson <phtyson@sbcglobal.net> [2012-08-15 21:14-0500]
>> Regarding http://www.w3.org/TR/2012/PR-rdb-direct-mapping-20120814/.
>> 
>> The definition of the row node IRI in section 3 says "for each column in
>> the primary key, in order...".
>> 
>> Is there a canonical ordering for primary key constraints? The examples
>> appear to follow the order of PK declaration in a DDL clause.
> 
> I *think* the answer to this is that a primary key is a candidate key (with an extran no-NULLs constraint) and candidate keys, by virtue of being referents of foreign keys, have order. That's a bit loose, e.g. who's to say that an unreferenced primary key isn't just a set? The grammar says that "PRIMARY KEY" is followed by a "<column name list>", but elsewhere referes to keys as sets. Unique constraints are defined in terms of "SELECT UCL..." where UCL is the above "<column name list>" and SELECTs are ordered. Is this sufficient grounds to believe that primary keys are ordered?
> 
> 
>> Perhaps the definition should clarify the intended order of PK column
>> names in the row node IRI. If such an order cannot be reliably derived
>> from database metadata, maybe lexical ordering could be prescribed.
> 
> I've always seen it preserved, but I haven't found that prescribed yet.
> 
> 
>> Also, in the row node definition, the last 3rd-level bullet says "if it
>> is not the last column in the foreign key,...". Perhaps that should be
>> "...in the primary key,..."?
> 
> I took care of this.
> 
> 
>> Regards,
>> --Paul
> -- 
> -ericP
>

Received on Wednesday, 15 August 2012 12:47:00 UTC