Re: [sqlite] CHECK IGNORE?

[Richard Hipp]

On 7/9/18, R Smith  wrote:
> a - Does it affect the UPSERT statement's ON CONFLICT clause? i.e. does
> INSERT ON CONFLICT...  trigger the conflict resolution when the
> CHECK fails?

No UPSERT only works for uniqueness constraints (UNIQUE or PRIMARY KEY).


> b - If so, would it be real hard to make it so for column ... CHECK(...)
> ON CONFLICT... too?
>

We follow the lead of PostgreSQL.  If you can convince the PostgreSQL
devs to support upsert on check constraints, we will consider it.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CHECK IGNORE?

[R Smith]

On 2018/07/10 12:53 AM, Richard Hipp wrote:

CREATE TABLE t1(x INT, CHECK(x!=5) ON CONFLICT IGNORE);
However, the parser silently discards the ON CONFLICT clause.  I think
this has always been the case.


Well, I feel embarrassed, I have used this CHECK conflict clause a good 
number of times (usually the FAIL option) and simply never tested it for 
actual in-use failure, and I'm usually the first to warn about the 
dangers of assumption. :)


Two questions:
a - Does it affect the UPSERT statement's ON CONFLICT clause? i.e. does 
INSERT ON CONFLICT...  trigger the conflict resolution when the 
CHECK fails?
b - If so, would it be real hard to make it so for column ... CHECK(...) 
ON CONFLICT... too?


Thanks,
Ryan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CHECK IGNORE?

[Richard Hipp]

On 7/9/18, Thomas Kurz  wrote:
> Hello,
>
> is there a way to have Sqlite ignore check violations?
>
> I would like to do:
>
> CREATE TABLE  (name TEXT NOT NULL CHECK (name<>'') ON CONFLICT IGNORE
>
> but the "on conflict" is not accepted here.

It does not appear that SQLite has ever acted upon "ON CONFLICT"
exceptions on CHECK constraints.  Such clauses are accepted for CHECK
constraints not associated with a particular column.  For example:

   CREATE TABLE t1(x INT, CHECK(x!=5) ON CONFLICT IGNORE);

However, the parser silently discards the ON CONFLICT clause.  I think
this has always been the case.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CHECK IGNORE?

[Simon Slavin]

On 9 Jul 2018, at 10:21pm, Thomas Kurz  wrote:

> However, this is a bit confusing as e.g. this works fine:
> 
> CREATE TABLE test (col1 TEXT UNIQUE ON CONFLICT IGNORE);

There is actually a way to do what you want.  You do it with a TRIGGER which 
does the test and drops the offending row using RAISE (IGNORE) .



However, this may be horribly confusing to anyone else who has to understand 
your programming.  They may be trying to insert a row and magically nothing 
happens.  It may be better to have the IGNORE command in the INSERT command 
that they will be looking at rather than in a part of the schema they may not 
be thinking about.

On the other hand, it might better suit the way your software works.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CHECK IGNORE?

[Simon Slavin]

On 9 Jul 2018, at 10:21pm, Thomas Kurz  wrote:

> However, this is a bit confusing as e.g. this works fine:
> 
> CREATE TABLE test (col1 TEXT UNIQUE ON CONFLICT IGNORE);

By 'works fine' do you mean you didn't get a syntax error, or do you mean that 
the clause actually does what you want, both to accept and reject new rows ?  
The diagrams on



suggest that your clause is not handled correctly.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CHECK IGNORE?

[Thomas Kurz]

> Yes.  The ON CONFLICT clause goes in the INSERT command.

Ok, thank you very much.

However, this is a bit confusing as e.g. this works fine:

CREATE TABLE test (col1 TEXT UNIQUE ON CONFLICT IGNORE);

May I suggest the appropriate extension for CHECK ON CONFLICT IGNORE or is 
there a thoughtful decision for not having this construct?

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CHECK IGNORE?

[Simon Slavin]

On 9 Jul 2018, at 10:00pm, Thomas Kurz  wrote:

> I get a "syntax error near on":
> CREATE TABLE test (col1 TEXT CHECK (col1<>'') ON CONFLICT IGNORE);
> 
> Am I using a wrong syntax?

Yes.  The ON CONFLICT clause goes in the INSERT command.

CREATE TABLE test (col1 TEXT CHECK (col1<>''));
INSERT OR IGNORE INTO test (col1) VALUES ('hello');

An alternative, recently added so available only in recent versions of SQLite3, 
is to do this:

CREATE TABLE test (col1 TEXT CHECK (col1<>''));
INSERT INTO test (col1) VALUES ('hello') ON CONFLICT DO NOTHING;

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CHECK IGNORE?

[Thomas Kurz]

Thank you, but I think there is a misunderstanding. I do want the constraint to 
be checked, but I want to ignore the attempt to insert values that would 
violate the check constraint.

According to the "on conflict" documentation 
(https://sqlite.org/lang_conflict.html), "The ON CONFLICT clause applies to 
UNIQUE, NOT NULL, CHECK, and PRIMARY KEY constraints."

But I when I try this, I get a "syntax error near on":
CREATE TABLE test (col1 TEXT CHECK (col1<>'') ON CONFLICT IGNORE);

Am I using a wrong syntax? Or am I misinterpreting the documentation?

Kind regards,
Thomas


- Original Message - 
From: Richard Hipp 
To: SQLite mailing list 
Sent: Monday, July 9, 2018, 22:03:29
Subject: [sqlite] CHECK IGNORE?

On 7/9/18, Thomas Kurz  wrote:
> is there a way to have Sqlite ignore check violations?


https://www.sqlite.org/pragma.html#pragma_ignore_check_constraints



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CHECK IGNORE?

[Richard Hipp]

On 7/9/18, Thomas Kurz  wrote:
> is there a way to have Sqlite ignore check violations?
>

https://www.sqlite.org/pragma.html#pragma_ignore_check_constraints



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users