Unique columns pair constraint in SQLite with conflict strategy

Question

I need to create a unique constraint for two columns in a row with conflict strategy attached. Suppose we have a table:

CREATE TABLE `telephones`(
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
telephone STRING NOT NULL);

So it is clear that it is a separate table for one-to-many relationship between a user and his telephones. What I need is to create a unique index for user_id and telephone, so database shouldn't have duplicates. AFAIK, here are two ways of creating such a constraint: either by creating an index as a separated SQL request or by creating a constraint inside CREATE TABLE statement. First way looks like this:

CREATE UNIQUE INDEX `user_ids_and_telephones` ON `telephones`(`user_id`, `telephone`) ON CONFLICT IGNORE

And the second way looks like this:

CREATE TABLE `telephones`(
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
telephone STRING NOT NULL,
UNIQUE(`user_id`, `telephone`) ON CONFLICT IGNORE);

My question is: are these ways equivalent and will both work correctly for the goal described, or do they have some logical differences that will affect subsequent duplicates inserting logic?

I didn't find documentation quiet clear about that.

Answer 1

Both ways create an index and as such they act in the same way (see below). The documentation states this as :-

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);

SQL As Understood By SQLite - CREATE TABLE - SQL Data Constraints

However, I do not believe that you can code a conflict clause when defining an index independently. So CREATE UNIQUE INDEX user_ids_and_telephones ON telephones(user_id, telephone) ON CONFLICT IGNORE is not valid.

As such, the conflict handling will differ.

For example consider the following :-

DROP TABLE IF EXISTS `telephones1`;
CREATE TABLE IF NOT EXISTS `telephones1`(
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
telephone STRING NOT NULL);
DROP INDEX IF EXISTS user_ids_and_telephones;
CREATE UNIQUE INDEX `user_ids_and_telephones` ON `telephones1`(`user_id`, `telephone`)
    -- ON CONFLICT IGNORE commented out as is invalid
;

DROP TABLE IF EXISTS `telephones2`;
CREATE TABLE IF NOT EXISTS `telephones2`(
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
telephone STRING NOT NULL,
UNIQUE(`user_id`, `telephone`) ON CONFLICT IGNORE);

SELECT * FROM sqlite_master WHERE type = 'index' AND name LIKE '%telephones%';


INSERT INTO `telephones2` VALUES
    (null,1,'phone1'),(null,2,'phone2'),(null,3,'phone1'),(null,1,'phone1');
INSERT INTO `telephones1` VALUES
    (null,1,'phone1'),(null,2,'phone2'),(null,3,'phone1'),(null,1,'phone1');

• The insert into telephones2 will not fail but only insert 3 of the 4 rows.
• The insert into telephones1 fails without inserting any rows.

as per :-

DROP TABLE IF EXISTS `telephones1`
> OK
> Time: 0.389s


CREATE TABLE IF NOT EXISTS `telephones1`(
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
telephone STRING NOT NULL)
> OK
> Time: 0.31s


DROP INDEX IF EXISTS user_ids_and_telephones
> OK
> Time: 0s


CREATE UNIQUE INDEX `user_ids_and_telephones` ON `telephones1`(`user_id`, `telephone`)
    -- ON CONFLICT IGNORE
> OK
> Time: 0.366s


DROP TABLE IF EXISTS `telephones2`
> OK
> Time: 0.383s


CREATE TABLE IF NOT EXISTS `telephones2`(
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
telephone STRING NOT NULL,
UNIQUE(`user_id`, `telephone`) ON CONFLICT IGNORE)
> OK
> Time: 0.358s


SELECT * FROM sqlite_master WHERE type = 'index' AND name LIKE '%telephones%'
> OK
> Time: 0s


INSERT INTO `telephones2` VALUES
    (null,1,'phone1'),(null,2,'phone2'),(null,3,'phone1'),(null,1,'phone1')
> Affected rows: 3
> Time: 0.356s


INSERT INTO `telephones1` VALUES
    (null,1,'phone1'),(null,2,'phone2'),(null,3,'phone1'),(null,1,'phone1')
> UNIQUE constraint failed: telephones1.user_id, telephones1.telephone
> Time: 0.004s

As can be seen from the output of the query of the slqite_master two indexes are in fact created :-

The one attached to telephones2 being an automatically generated index (i.e. it starts with sqlite_autoindex)