SQLite Forum

Naminng indices during CREATE TABLE
Login

Naminng indices during CREATE TABLE

(1) By Дилян Палаузов (dpa-sqlite2) on 2021-11-28 09:34:44 [source]

I have created a table in the past:

CREATE TABLE t (a TEXT, b TEXT);

I want now to add a constraint, that (a, b) is unique.

For old installations of my system, I want to run:

CREATE UNIQUE INDEX IF NOT EXISTS idx1 on t (a, b);

This creates an index called idx1 and the previous command is idempotent.

For new installations, I want instead to execute from the beginning

CREATE TABLE t (a TEXT, b TEXT, CONSTRAINT idx1 UNIQUE (a, b));

Sqlite names the index, created by the previous command sqlite_autoindex_T_1 .

The problem is, that if that index is going to be dropped in the future, it cannot be named reliably in the DROP command.  Moreover, the aforementioned idempotent command is now semi-idempotent, as it creates an additional, duplicate index.

Please create means to give names to UNIQUE indices, spanning several columns, during CREATE TABLE.

Actually, in CREATE TABLE m (a TEXT CONSTRAINT c UNIQUE); the name of the index cannot be provided, too.

I use SQLite 3.36.0 2021-06-18 .

I see no way how to reset passwords on the “SQLite Forum”.

(2) By David Jones (vman59) on 2021-11-28 12:58:18 in reply to 1 [link] [source]

Seems to me the addition you want is to be able execute

ALTER TABLE t DROP CONSTRAINT idx1.

(3) By Дилян Палаузов (dpa-sqlite2) on 2021-11-28 13:36:40 in reply to 2 [link] [source]

Hello David,

there are two ways to create the same unique index:

  1. CREATE TABLE t (a TEXT, b TEXT, CONSTRAINT idx1 UNIQUE (a, b));
  2. CREATE TABLE t (a TEXT, b TEXT); CREATE UNIQUE INDEX IF NOT EXISTS idx1 on t (a, b);

The following two command shall be equivalent, and revert the index created by any of the aforementioned ways:

  • ALTER TABLE t DROP CONSTRAINT idx1
  • DROP INDEX idx1

In fact, as long as the ALTER TABLE DROP CONSTRAINT and DROP INDEX are equivalent, there is no need for ALTER TABLE DROP CONSTRAINT, when it comes to indices.

Greetings Dilian

(4) By Gunter Hick (gunter_hick) on 2021-11-29 07:34:32 in reply to 1 [link] [source]

These are not "the same index".

CREATE TABLE ( .. UNIQUE(..) ) is a table constraint that SQLite chooses to implement via an automatically created and named index; the side effect is that there is an index that can be used to satisfy specific GROUP BY/ORDER BY clauses. There is no way to insert a duplicate record into the table.

CREATE UNQIUE INDEX() is an index. If the underlying table contains duplicate records, the creation of the index will fail. As long as the index exists, attempts to insert a "duplicate" record will fail, just as with the constraint - but it is an effect of the index, not a constraint on the table.

It is perfectly legal to have a UNIQUE constraint and a UNIQUE INDEX on the same table - indeed if you are regularly sorting by a and b, it may make sense to have UNIQUE(a,b) on the table and UNIQUE INDEX ON (b,a).

Why do you think you need an explicit name for an automatic index?