CHECK IGNORE?

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

CHECK IGNORE?

Thomas Kurz
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.

_______________________________________________
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: CHECK IGNORE?

Richard Hipp-3
On 7/9/18, Thomas Kurz <[hidden email]> wrote:
> is there a way to have Sqlite ignore check violations?
>

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



--
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: 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 <[hidden email]>
To: SQLite mailing list <[hidden email]>
Sent: Monday, July 9, 2018, 22:03:29
Subject: [sqlite] CHECK IGNORE?

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


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



--
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: CHECK IGNORE?

Simon Slavin-3
On 9 Jul 2018, at 10:00pm, Thomas Kurz <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: CHECK IGNORE?

Simon Slavin-3
On 9 Jul 2018, at 10:21pm, Thomas Kurz <[hidden email]> 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

<https://sqlite.org/lang_createtable.html>

suggest that your clause is not handled correctly.

Simon.
_______________________________________________
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: CHECK IGNORE?

Simon Slavin-3
In reply to this post by Thomas Kurz
On 9 Jul 2018, at 10:21pm, Thomas Kurz <[hidden email]> 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) .

<https://sqlite.org/lang_createtrigger.html>

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: CHECK IGNORE?

Richard Hipp-3
In reply to this post by Thomas Kurz
On 7/9/18, Thomas Kurz <[hidden email]> 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
[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: CHECK IGNORE?

R Smith-2
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: CHECK IGNORE?

Richard Hipp-3
On 7/9/18, R Smith <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users