Unique columns pair constraint in SQLite with conflict strategy
Unique columns pair constraint in SQLite with conflict strategy
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:
user_id
telephone
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.
1 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.
CREATE UNIQUE INDEX user_ids_and_telephones ON telephones(user_id, telephone) ON CONFLICT IGNORE
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');
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)
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
