On 2015-08-23 03:32 AM, Barry Smith wrote:
> Could this not be achieved by two indexes: one partial and one complete?
>
> CREATE UNIQUE INDEX idx_books1 ON Books(title, author);
>
> CREATE UNIQUE INDEX idx_books2 ON Books(title) WHERE author ISNULL;
>
> To save space and (maybe) time, you could put a 'WHERE author NOTNULL' on the
> first index.
>
> Of course, I'm just talking about how to code it, the issues mentioned by R
> Smith is a different kettle of fish.
>
> Cheers,
>
> Barry
Yes this will work for what the OP wanted, I think. Great suggestion,
to prove the concept, consider:
-- Processing SQL in: D:\Documents\SQLiteAutoScript.sql
-- Script Items: 10 Parameter Count: 0 SQLitespeed v2.0.1
-- 2015-08-23 04:50:41.253 | [Info] Script Initialized,
Started executing...
--
================================================================================================
CREATE TABLE tA(c1 TEXT, c2 TEXT, c3 TEXT);
CREATE UNIQUE INDEX tAI1 ON tA(c1, c2);
CREATE UNIQUE INDEX tAI2 ON tA(c1) WHERE c2 IS NULL;
INSERT INTO tA VALUES ('ABC', 'Joe', '1');
INSERT INTO tA VALUES ('ABC', 'John', '2');
INSERT INTO tA VALUES ('ABC', 'Jim', '3');
INSERT INTO tA VALUES ('ABC', NULL, '4');
INSERT INTO tA VALUES ('ABC', NULL, '5');
-- 2015-08-23 04:50:41.263 | [ERROR] UNIQUE constraint
failed: tA.c1
-- 2015-08-23 04:50:41.264 | [Info] Script failed -
Rolling back...
-- 2015-08-23 04:50:41.264 | [Success] Transaction Rolled back.
-- 2015-08-23 04:50:41.264 | [ERROR] Failed to complete:
Script Failed in Item 7: UNIQUE constraint failed: tA.c1
-- ------- DB-Engine Logs (Contains logged information from all
DB connections during run) ------
-- [2015-08-23 04:50:41.226] APPLICATION : Script
D:\Documents\SQLiteAutoScript.sql started at 04:50:41.226 on 23 August.
-- [2015-08-23 04:50:41.263] ERROR (2067) : abort at 15 in
[INSERT INTO tA VALUES ('ABC', NULL, '5');]: UNIQUE constraint
failed: tA.c1
--
================================================================================================
