Unique ID field position in a TABLE ?

Databases
1

At first, I set the Unique ID on the TABLE first position.

At second, I put it at the end of the TABLE definition (seems logical to have the whole Unique ID definition at the same place: the end of the TABLE definition.

Now, I have some doubts: where is the correct position of the Unique ID in the TABLE definition ?

BTW: both definitions works fine, I do not found a difference in use.

2

Usually, the key fields of a table (unique ID) are indexed. It is customary to place the key fields in the first columns although not strictly necessary.

Note that in many RDBMS, a single UniqueId field is not required. The key can be made of several fields. Think for example of an invoice. The information is contained in more than one table. Say, InvoiceHeader and InvoiceItem. The header table could have a uniqueID field, the invoice number, for example. The item table would use the combination of invoice number and item number , two different fields, as key (unique identifier).

3

Do you mean in your CREATE TABLE statement?
There is no such thing as a correct position of a field. Any position is fine.

4

Merci Louis.

Note that I really love:

Prior code removed , ID INTEGER NOT NULL, PRIMARY KEY(ID))
It looks to have sense.

Split both part of the definition and put (say) 20 fields between them looks strange after a month or two without seen it.

Maximilian: your addition appears before I clicked in the Post a Reply button.

You are right, excepted in some cases. Also, read the paragraph that starts with Split… above.

From my point of view, it is a matter of how nice or understandable it is. Of course, only if I can put it where I want !

Thanks.

5

LOL, I was just reading up on this myself.

if you have ONE “unique” field, or a single field “Primary key” you can put it with the Field information

(fieldname TEXT Primary Key, nextfield TEXT Unique)

if you have MULTIPLE, you put them at the END (its ok to put single ones at the end as well)

(fieldname TEXT, nextfield TEXT, PRIMARY KEY(fieldname),UNIQUE(nextfield)
6

Key fields can be anywhere within the table. It is just customary to place them at the beginning. The RDBMS will not care one bit. ( :wink: ) The human reader, however, will work much easier with key fields placed at the top of the list.