Enforcing uniqueness from multiple indexes

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

Enforcing uniqueness from multiple indexes

wmertens
Hi,

I have the following problem: I have data where two independent values need
to be unique. I'm using one (id) as the primary key, and the other (let's
call it k) should just cause insertion or updating to fail if it already
exists in another row with a different id in the table.

Furthermore, I'd like this to be free from race conditions :)

If I just use two different UNIQUE indexes, doing a "INSERT OR REPLACE"
will cause another row that has the same k to be replaced, instead of
failing to update due to the differing id. I suppose this is entirely
logical, but it's not what I'd like to achieve :)

So, bottom line, is there a way to insert or replace a row so that first
the id constraint is observed (replacing a previous row with the same id),
and then the k constraint is verified (failing to replace if k is already
present in the table)?

I'm ok with doing some multi-step thing, perhaps first trying UPDATE and
then INSERT, but I'm not sure what would be most efficient, and I'm also
not sure how to prevent racing when checking for k…

Thanks in advance for any insights!

Wout.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Enforcing uniqueness from multiple indexes

Richard Hipp-3
On 8/18/17, Wout Mertens <[hidden email]> wrote:
>
> So, bottom line, is there a way to insert or replace a row so that first
> the id constraint is observed (replacing a previous row with the same id),
> and then the k constraint is verified (failing to replace if k is already
> present in the table)?


CREATE TABLE demo(id INTEGER PRIMARY KEY, k TEXT, otherstuff ANY);
CREATE INDEX demo_k ON demo(k);
CREATE TRIGGER demo_trigger1 BEFORE INSERT ON demo BEGIN
  SELECT raise(ABORT,'uniqueness constraint failed on k')
   FROM demo WHERE k=new.k;
END;

The above will force uniqueness on k for INSERT statements.  You'll
want a second "BEFORE UPDATE" trigger to do similar enforcement for
UPDATEs if that is an issue for you.


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Enforcing uniqueness from multiple indexes

wmertens
Splendid! Many thanks!

On Fri, Aug 18, 2017 at 1:47 PM Richard Hipp <[hidden email]> wrote:

> On 8/18/17, Wout Mertens <[hidden email]> wrote:
> >
> > So, bottom line, is there a way to insert or replace a row so that first
> > the id constraint is observed (replacing a previous row with the same
> id),
> > and then the k constraint is verified (failing to replace if k is already
> > present in the table)?
>
>
> CREATE TABLE demo(id INTEGER PRIMARY KEY, k TEXT, otherstuff ANY);
> CREATE INDEX demo_k ON demo(k);
> CREATE TRIGGER demo_trigger1 BEFORE INSERT ON demo BEGIN
>   SELECT raise(ABORT,'uniqueness constraint failed on k')
>    FROM demo WHERE k=new.k;
> END;
>
> The above will force uniqueness on k for INSERT statements.  You'll
> want a second "BEFORE UPDATE" trigger to do similar enforcement for
> UPDATEs if that is an issue for you.
>
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Enforcing uniqueness from multiple indexes

Gerry Snyder-4
In reply to this post by Richard Hipp-3
Should it be :

CREATE UNIQUE INDEX ...


Gerry Snyder

On Aug 18, 2017 4:47 AM, "Richard Hipp" <[hidden email]> wrote:

> On 8/18/17, Wout Mertens <[hidden email]> wrote:
> >
> > So, bottom line, is there a way to insert or replace a row so that first
> > the id constraint is observed (replacing a previous row with the same
> id),
> > and then the k constraint is verified (failing to replace if k is already
> > present in the table)?
>
>
> CREATE TABLE demo(id INTEGER PRIMARY KEY, k TEXT, otherstuff ANY);
> CREATE INDEX demo_k ON demo(k);
> CREATE TRIGGER demo_trigger1 BEFORE INSERT ON demo BEGIN
>   SELECT raise(ABORT,'uniqueness constraint failed on k')
>    FROM demo WHERE k=new.k;
> END;
>
> The above will force uniqueness on k for INSERT statements.  You'll
> want a second "BEFORE UPDATE" trigger to do similar enforcement for
> UPDATEs if that is an issue for you.
>
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Enforcing uniqueness from multiple indexes

Richard Hipp-3
On 8/18/17, Gerry Snyder <[hidden email]> wrote:
> Should it be :
>
> CREATE UNIQUE INDEX ...

I don't think it matters in this case.  The trigger is also enforcing
uniqueness.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Enforcing uniqueness from multiple indexes

wmertens
The UNIQUE will either do nothing, or will erase existing lines if a
trigger is forgotten. So not having the UNIQUE seems safer.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Enforcing uniqueness from multiple indexes

Nico Williams
In reply to this post by wmertens
An INSERT OR UPDATE sure would be nice.  What i often do in cases like this
is: UPDATE .. WHERE; INSERT .. SELECT .. WHERE NOT EXISTS (SELECT ..);.
That's two statements -- if that's a problem, then you should use D. R.
Hipp's trigger-based solution.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Enforcing uniqueness from multiple indexes

R Smith
In reply to this post by Richard Hipp-3

On 2017/08/18 1:47 PM, Richard Hipp wrote:

> On 8/18/17, Wout Mertens <[hidden email]> wrote:
>> So, bottom line, is there a way to insert or replace a row so that first
>> the id constraint is observed (replacing a previous row with the same id),
>> and then the k constraint is verified (failing to replace if k is already
>> present in the table)?
>
> CREATE TABLE demo(id INTEGER PRIMARY KEY, k TEXT, otherstuff ANY);
> CREATE INDEX demo_k ON demo(k);
> CREATE TRIGGER demo_trigger1 BEFORE INSERT ON demo BEGIN
>    SELECT raise(ABORT,'uniqueness constraint failed on k')
>     FROM demo WHERE k=new.k;
> END;
>
> The above will force uniqueness on k for INSERT statements.  You'll
> want a second "BEFORE UPDATE" trigger to do similar enforcement for
> UPDATEs if that is an issue for you.

Isn't this what conflict clauses on constraints are for?

Wouldn't the following achieve the same?:

CREATE TABLE demo(
   id INTEGER PRIMARY KEY ON CONFLICT REPLACE,
   k TEXT UNIQUE ON CONFLICT ABORT,
   otherstuff ANY
);

To amuse myself, I've tested it and it works as expected - New IDs that
conflict are replaced, but a conflicting k gets aborted - but am I
missing some fine-print or hidden caveat?

Cheers,
Ryan


>
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Enforcing uniqueness from multiple indexes

R Smith


On 2017/08/18 6:08 PM, R Smith wrote:
>
> Isn't this what conflict clauses on constraints are for?
>

Apologies, I usually add the test-case scripts in case anyone else wish
to test it or similar, the case in question herewith added below:

   -- SQLite version 3.17.0  [ Release: 2017-02-13 ]  on SQLitespeed
version 2.0.2.4.
   -- Script Items: 7          Parameter Count: 0
   --
================================================================================================

CREATE TABLE demo(
   id INTEGER PRIMARY KEY ON CONFLICT REPLACE,
   k TEXT UNIQUE ON CONFLICT ABORT,
   otherstuff ANY
);

INSERT INTO demo VALUES
(1,10,'One-Mississippi'),
(2,20,'Two-Mississippi'),
(3,30,'Three-Mississippi')
;

   -- This one works as expected, replacing the previous key.
INSERT INTO demo VALUES (2,40,'Four-Mississippi');

SELECT * FROM demo;
   --      id      |  k  | otherstuff
   -- ------------ | --- | -----------------
   --       1      |  10 | One-Mississippi
   --       2      |  40 | Four-Mississippi
   --       3      |  30 | Three-Mississippi

   -- This one should fail since the id is new but k conflicts...
INSERT INTO demo VALUES (5,40,'Four-Mississippi-Again');
   -- and does:

   -- 2017-08-18 18:14:20.463  |  [ERROR]      UNIQUE constraint failed:
demo.k
   --   Script Stats: Total Script Execution Time:     0d 00h 00m and
00.025s
   --                 Total Script Query Time:         0d 00h 00m and
00.004s
   --                 Total Database Rows Changed:     4
   --                 Total Virtual-Machine Steps:     167
   --                 Last executed Item Index:        5
   --                 Last Script Error: Script Failed in Item 4: UNIQUE
constraint failed: demo.k
   --
------------------------------------------------------------------------------------------------

   -- 2017-08-18 18:14:20.465  |  [Info]       Script failed - Rolling
back...
   -- 2017-08-18 18:14:20.466  |  [Success]    Transaction Rolled back.
   -- 2017-08-18 18:14:20.466  |  [ERROR]      Failed to complete:
Script Failed in Item 4: UNIQUE constraint failed: demo.k
   --
================================================================================================

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Enforcing uniqueness from multiple indexes

David Raymond
Tried it and found a <potential> issue with update, though it might actually work the way he wants. Good call though, I keep forgetting the on conflict thing can be on table creation and not just for a query.

--continuing from your script...

sqlite> select * from demo;
--EQP-- 0,0,0,SCAN TABLE demo
id|k|otherstuff
1|10|One-Mississippi
2|40|Four-Mississippi
3|30|Three-Mississippi

sqlite> update demo set id = 1 where k = 30;
--EQP-- 0,0,0,SEARCH TABLE demo USING INDEX sqlite_autoindex_demo_1 (k=?)

sqlite> select * from demo;
--EQP-- 0,0,0,SCAN TABLE demo
id|k|otherstuff
1|30|Three-Mississippi
2|40|Four-Mississippi

sqlite> update demo set k = 40 where id = 1;
--EQP-- 0,0,0,SEARCH TABLE demo USING INTEGER PRIMARY KEY (rowid=?)
Error: UNIQUE constraint failed: demo.k

sqlite> update demo set id = 1, k = 30 where id = 2;
--EQP-- 0,0,0,SEARCH TABLE demo USING INTEGER PRIMARY KEY (rowid=?)

sqlite> select * from demo;
--EQP-- 0,0,0,SCAN TABLE demo
id|k|otherstuff
1|30|Four-Mississippi

sqlite>

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of R Smith
Sent: Friday, August 18, 2017 12:18 PM
To: [hidden email]
Subject: Re: [sqlite] Enforcing uniqueness from multiple indexes



On 2017/08/18 6:08 PM, R Smith wrote:
>
> Isn't this what conflict clauses on constraints are for?
>

Apologies, I usually add the test-case scripts in case anyone else wish
to test it or similar, the case in question herewith added below:

   -- SQLite version 3.17.0  [ Release: 2017-02-13 ]  on SQLitespeed
version 2.0.2.4.
   -- Script Items: 7          Parameter Count: 0
   --
================================================================================================

CREATE TABLE demo(
   id INTEGER PRIMARY KEY ON CONFLICT REPLACE,
   k TEXT UNIQUE ON CONFLICT ABORT,
   otherstuff ANY
);

INSERT INTO demo VALUES
(1,10,'One-Mississippi'),
(2,20,'Two-Mississippi'),
(3,30,'Three-Mississippi')
;

   -- This one works as expected, replacing the previous key.
INSERT INTO demo VALUES (2,40,'Four-Mississippi');

SELECT * FROM demo;
   --      id      |  k  | otherstuff
   -- ------------ | --- | -----------------
   --       1      |  10 | One-Mississippi
   --       2      |  40 | Four-Mississippi
   --       3      |  30 | Three-Mississippi

   -- This one should fail since the id is new but k conflicts...
INSERT INTO demo VALUES (5,40,'Four-Mississippi-Again');
   -- and does:

   -- 2017-08-18 18:14:20.463  |  [ERROR]      UNIQUE constraint failed:
demo.k
   --   Script Stats: Total Script Execution Time:     0d 00h 00m and
00.025s
   --                 Total Script Query Time:         0d 00h 00m and
00.004s
   --                 Total Database Rows Changed:     4
   --                 Total Virtual-Machine Steps:     167
   --                 Last executed Item Index:        5
   --                 Last Script Error: Script Failed in Item 4: UNIQUE
constraint failed: demo.k
   --
------------------------------------------------------------------------------------------------

   -- 2017-08-18 18:14:20.465  |  [Info]       Script failed - Rolling
back...
   -- 2017-08-18 18:14:20.466  |  [Success]    Transaction Rolled back.
   -- 2017-08-18 18:14:20.466  |  [ERROR]      Failed to complete:
Script Failed in Item 4: UNIQUE constraint failed: demo.k
   --
================================================================================================

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Enforcing uniqueness from multiple indexes

wmertens
Oh wow, I didn't know about ON CONFLICT, thanks!

Unfortunately the behavior on UPDATE (actually INSERT OR REPLACE in my
case) is not the desired behavior, it removes the row with the same k but
different id.

However, using a TRIGGER BEFORE INSERT as well as UPDATE, as proposed by
Richard, does the trick.

I tried a mix of both too but the triggers were easier to understand for me.

On Fri, Aug 18, 2017 at 7:08 PM David Raymond <[hidden email]>
wrote:

> Tried it and found a <potential> issue with update, though it might
> actually work the way he wants. Good call though, I keep forgetting the on
> conflict thing can be on table creation and not just for a query.
>
> --continuing from your script...
>
> sqlite> select * from demo;
> --EQP-- 0,0,0,SCAN TABLE demo
> id|k|otherstuff
> 1|10|One-Mississippi
> 2|40|Four-Mississippi
> 3|30|Three-Mississippi
>
> sqlite> update demo set id = 1 where k = 30;
> --EQP-- 0,0,0,SEARCH TABLE demo USING INDEX sqlite_autoindex_demo_1 (k=?)
>
> sqlite> select * from demo;
> --EQP-- 0,0,0,SCAN TABLE demo
> id|k|otherstuff
> 1|30|Three-Mississippi
> 2|40|Four-Mississippi
>
> sqlite> update demo set k = 40 where id = 1;
> --EQP-- 0,0,0,SEARCH TABLE demo USING INTEGER PRIMARY KEY (rowid=?)
> Error: UNIQUE constraint failed: demo.k
>
> sqlite> update demo set id = 1, k = 30 where id = 2;
> --EQP-- 0,0,0,SEARCH TABLE demo USING INTEGER PRIMARY KEY (rowid=?)
>
> sqlite> select * from demo;
> --EQP-- 0,0,0,SCAN TABLE demo
> id|k|otherstuff
> 1|30|Four-Mississippi
>
> sqlite>
>
> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> On Behalf Of R Smith
> Sent: Friday, August 18, 2017 12:18 PM
> To: [hidden email]
> Subject: Re: [sqlite] Enforcing uniqueness from multiple indexes
>
>
>
> On 2017/08/18 6:08 PM, R Smith wrote:
> >
> > Isn't this what conflict clauses on constraints are for?
> >
>
> Apologies, I usually add the test-case scripts in case anyone else wish
> to test it or similar, the case in question herewith added below:
>
>    -- SQLite version 3.17.0  [ Release: 2017-02-13 ]  on SQLitespeed
> version 2.0.2.4.
>    -- Script Items: 7          Parameter Count: 0
>    --
>
> ================================================================================================
>
> CREATE TABLE demo(
>    id INTEGER PRIMARY KEY ON CONFLICT REPLACE,
>    k TEXT UNIQUE ON CONFLICT ABORT,
>    otherstuff ANY
> );
>
> INSERT INTO demo VALUES
> (1,10,'One-Mississippi'),
> (2,20,'Two-Mississippi'),
> (3,30,'Three-Mississippi')
> ;
>
>    -- This one works as expected, replacing the previous key.
> INSERT INTO demo VALUES (2,40,'Four-Mississippi');
>
> SELECT * FROM demo;
>    --      id      |  k  | otherstuff
>    -- ------------ | --- | -----------------
>    --       1      |  10 | One-Mississippi
>    --       2      |  40 | Four-Mississippi
>    --       3      |  30 | Three-Mississippi
>
>    -- This one should fail since the id is new but k conflicts...
> INSERT INTO demo VALUES (5,40,'Four-Mississippi-Again');
>    -- and does:
>
>    -- 2017-08-18 18:14:20.463  |  [ERROR]      UNIQUE constraint failed:
> demo.k
>    --   Script Stats: Total Script Execution Time:     0d 00h 00m and
> 00.025s
>    --                 Total Script Query Time:         0d 00h 00m and
> 00.004s
>    --                 Total Database Rows Changed:     4
>    --                 Total Virtual-Machine Steps:     167
>    --                 Last executed Item Index:        5
>    --                 Last Script Error: Script Failed in Item 4: UNIQUE
> constraint failed: demo.k
>    --
>
> ------------------------------------------------------------------------------------------------
>
>    -- 2017-08-18 18:14:20.465  |  [Info]       Script failed - Rolling
> back...
>    -- 2017-08-18 18:14:20.466  |  [Success]    Transaction Rolled back.
>    -- 2017-08-18 18:14:20.466  |  [ERROR]      Failed to complete:
> Script Failed in Item 4: UNIQUE constraint failed: demo.k
>    --
>
> ================================================================================================
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Enforcing uniqueness from multiple indexes

Nico Williams
On Sun, Aug 20, 2017 at 05:17:16PM +0000, Wout Mertens wrote:
> Oh wow, I didn't know about ON CONFLICT, thanks!
>
> Unfortunately the behavior on UPDATE (actually INSERT OR REPLACE in my
> case) is not the desired behavior, it removes the row with the same k but
> different id.

PostgreSQL has some unwieldy but very general "UPSERT" syntax where you
can specify what to do ON CONFLICT in an INSERT on a
per-constraint/colliding columns basis, and with WHERE clauses too.

  INSERT INTO .. (id, k, thing1, thing2) VALUES (..)
  ON CONFLICT (id) DO UPDATE SET thing1 = .. WHERE id != 5
  ON CONFLICT (k) DO UPDATE SET thing2 = .. WHERE k != 3;

That's very generic, but for the common case I'd really like:

  -- Insert or else update conflicting rows' columns to all the new
  -- values from the insert.
  INSERT OR UPDATE INTO .. (..) VALUES (..);

and

  INSERT INTO .. (..) VALUES (..)
  ON CONFLICT (..) DO UPDATE;     -- change all the columns of the
                                  -- conflicting row to the new values
                                  -- from the INSERT

Nico
--
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users