SQLitening Support Forum

SQLitening Support => You've got Questions? We've got Answers! => Topic started by: Fim on February 19, 2018, 07:38:10 am

Title: Differences between PRIMARY KEY and UNIQUE INDEX ?
Post by: Fim on February 19, 2018, 07:38:10 am
Are there any differences between
CREATE TABLE HIS (AA PRIMARY KEY, BB, CC);

and

CREATE TABLE HIS (AA, BB, CC);
CREATE UNIQUE INDEX HIS_INDEX0 ON HIS (AA);

/Fim W.
Title: Re: Differences between PRIMARY KEY and UNIQUE INDEX ?
Post by: Bern Ertl on February 19, 2018, 12:00:18 pm
Quote...
In most cases, UNIQUE and PRIMARY KEY constraints are implemented by creating a unique index in the database. (The exceptions are INTEGER PRIMARY KEY and PRIMARY KEYs on WITHOUT ROWID tables.) Hence, the following schemas are logically equivalent:

    CREATE TABLE t1(a, b UNIQUE);

    CREATE TABLE t1(a, b PRIMARY KEY);

    CREATE TABLE t1(a, b);
    CREATE UNIQUE INDEX t1b ON t1(b);
...


http://www.sqlite.org/lang_createtable.html  (scroll down to: SQL Data Constraints)
Title: Re: Differences between PRIMARY KEY and UNIQUE INDEX ?
Post by: Fim on February 19, 2018, 12:31:28 pm
Thanks. Now I feel safer.
/Fim W.
Title: Re: Differences between PRIMARY KEY and UNIQUE INDEX ?
Post by: cj on June 12, 2018, 11:50:25 am
Why not always use create index?
It has the ability to be dropped.

But ...
No pirmary key specification with create index or autoincrement
Not easily dropped without rebuilding table.

These are considerations and not an answer to this quesition.