Graal Forums - SQL Primary Key ?

Quote:

Each table in SQLite may have at most one PRIMARY KEY. If the keywords PRIMARY KEY are added to a column definition, then the primary key for the table consists of that single column. Or, if a PRIMARY KEY clause is specified as a table-constraint, then the primary key of the table consists of the list of columns specified as part of the PRIMARY KEY clause. If there is more than one PRIMARY KEY clause in a single CREATE TABLE statement, it is an error.

If a table has a single column primary key, and the declared type of that column is "INTEGER", then the column is known as an INTEGER PRIMARY KEY. See below for a description of the special properties and behaviors associated with an INTEGER PRIMARY KEY.

Each row in a table with a primary key must feature a unique combination of values in its primary key columns. For the purposes of determining the uniqueness of primary key values, NULL values are considered distinct from all other values, including other NULLs. If an INSERT or UPDATE statement attempts to modify the table content so that two or more rows feature identical primary key values, it is a constraint violation. According to the SQL standard, PRIMARY KEY should always imply NOT NULL. Unfortunately, due to a long-standing coding oversight, this is not the case in SQLite. Unless the column is an INTEGER PRIMARY KEY 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.

Something like...

Quote:

CREATE TABLE tbl (prim INTEGER PRIMARY KEY, str TEXT);

is working for me. Note that all this does is make it an alias for the rowid.

Depending on what you're doing, rowid may suit your needs, but keep in mind that it can change (so don't rely on it staying the same). If you want to map rows between tables (like with foreign keys), you should use PRIMARY KEY.