SQLitening Support Forum

Please login or register.

Login with username, password and session length
Advanced search  

News:

Welcome to the SQLitening support forums!

Author Topic: Differences between PRIMARY KEY and UNIQUE INDEX ?  (Read 478 times)

Fim

  • Guru
  • ***
  • Posts: 145
    • View Profile
    • Fims sajt
Differences between PRIMARY KEY and UNIQUE INDEX ?
« on: February 19, 2018, 06:08: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.
Logged
Fim W

Bern Ertl

  • Master Geek
  • ****
  • Posts: 495
  • Excellent
    • View Profile
    • InterPlan Systems
Re: Differences between PRIMARY KEY and UNIQUE INDEX ?
« Reply #1 on: February 19, 2018, 10:30:18 AM »

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

  • Guru
  • ***
  • Posts: 145
    • View Profile
    • Fims sajt
Re: Differences between PRIMARY KEY and UNIQUE INDEX ?
« Reply #2 on: February 19, 2018, 11:01:28 AM »

Thanks. Now I feel safer.
/Fim W.
Logged
Fim W

cj

  • Master Geek
  • ****
  • Posts: 679
    • View Profile
Re: Differences between PRIMARY KEY and UNIQUE INDEX ?
« Reply #3 on: June 12, 2018, 09:20: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.
« Last Edit: June 12, 2018, 09:28:17 AM by cj »
Logged
CJ