jeudi 14 janvier 2016

Cannot create Unique Partial Indexes - Sqlite3

Consider the following table.

CREATE TABLE IF NOT EXISTS wsfiles
(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  website_id integer NOT NULL,
  parent_id integer,
  archive_id integer,
  path character varying,
  path_sha character varying NOT NULL,
  target character varying,
  name character varying NOT NULL,
  is_directory boolean
  CONSTRAINT uniq_file_website_backup UNIQUE (archive_id, path_sha) ON CONFLICT REPLACE
);

And I need to create the following unique partial index.

CREATE UNIQUE INDEX IF NOT EXISTS idx_unique_directory ON wsfiles(path_sha) WHERE is_directory = 1;

But when executing the above schema I am getting a syntax error near the WHERE clause in CREATE UNIQUE INDEX query. This is working well in my local machine and the problem occurs when I am executing on the production machine.

Error: near line 98: near "WHERE": syntax error

Production machine sqlite3 version is 3.7.17

Local machine sqlite3 version is 3.8.5

Aucun commentaire:

Enregistrer un commentaire