Field order when creating PK

[Guest_imported]

It seems that when regardless of how you define a primary key, the field order always ends up to be alphabetical.
For example if you define the PK as:

ALTER TABLE PKTest
ADD CONSTRAINT PKTest_PK
PRIMARY KEY (ColumnC, ColumnA, ColumnB);

When you check the ColumnPosition in the dbc.indices table, the order will always be alphabetic:

ColumnName ColumnPosition
---------- --------------
ColumnA 1
ColumnB 2
ColumnC 3

So, the question is: Is there any way to prevent this from happening?

 

[tdatgod]

Hi,
Why does it matter? Do you want C, B , A rather than A, B, C so that they HASH to different AMPS or so you don't have to specify them when you do the

sel * from table?

I don't work on the parser so I wouldn't know why. I guess I could ask them and get back to you.

However, According to Codd and Date, In the relational model there is no such thing as column order. None is any more likely to be first than any other one.

That is why you must specify the columns you wish to project ( select ) coulumns from a relation (table).

Select c,b,a from blort.

I guess SQL gives you short hand

Select * from blort;

but then it is up to the database engine to pick the order since the query didn't specify it.

 

[MartinvdP]

hi whamdin ,

I noticed that the order of the columns of a select * query is the same as the order of columns you did your create table in.
You can see the order in the system table "dbc.columns", the column "columnid".
I don't know if this is specified or just luck, I always see this order, you proberly should not count on this behaviour.
Ofcource tdatgod is right, with a asterix query the order is up to the machine.

Cheers Martin