[yunqa.de] Re: disadvantage of using ROWID as the primary key of a table?
- From: Delphi Inspiration <delphi@xxxxxxxx>
- To: yunqa@xxxxxxxxxxxxx,yunqa@xxxxxxxxxxxxx
- Date: Fri, 24 Jul 2009 15:43:18 +0200
At 09:18 24.07.2009, Edwin Yip wrote:
>Thanks for the detailed explanation. so in other words, if I define a column
>of any name of type "INTEGER PRIMARY KEY", referencing that column in SQL is
>exactly the same as referencing ROWID, and the values in that column will not
>be altered unexpected by the engine, right?
Right.
>And Is "INTEGER NOT NULL PRIMARY KEY" equal to "INTEGER PRIMARY KEY"?
Yes.
Regarding null values in ROWIDs, please keep this in mind as well (quoted again
from the DISQLite3 Help -> SQL Syntax -> CREATE TABLE):
The PRIMARY KEY attribute normally creates a UNIQUE index on the column or
columns that are specified as the PRIMARY KEY. The only exception to this
behavior is special INTEGER PRIMARY KEY column, described below. According to
the SQL standard, PRIMARY KEY should imply NOT NULL. Unfortunately, due to a
long-standing coding oversight, this is not the case in SQLite. SQLite allows
NULL values in a PRIMARY KEY column. We could change SQLite to conform to the
standard (and we might do so in the future), but by the time the oversight was
discovered, SQLite was in such wide use that we feared breaking legacy code if
we fixed the problem. So for now we have chosen to continue allowing NULLs in
PRIMARY KEY columns. Developers should be aware, however, that we may change
SQLite to conform to the SQL standard in future and should design new programs
accordingly.
Ralf
_______________________________________________
Delphi Inspiration mailing list
yunqa@xxxxxxxxxxxxx
//www.freelists.org/list/yunqa
Other related posts:
