• Welcome, Guest. Please login.
 
October 21, 2019, 02:55:49 am

News:

Welcome to the SQLitening support forums!


Differences between PRIMARY KEY and UNIQUE INDEX ?

Started by Fim, February 19, 2018, 07:38:10 am

Previous topic - Next topic

Fim

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.
Fim W

Bern Ertl

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)

Fim

Fim W

cj

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.