Re: [sqlite] How to refer to `this` table?

[Abroży Nieprzełoży]

2019-02-23 10:03 GMT+01:00, Rocky Ji:
> I went from
>
> https://sqlite.org/lang_createtable.html
>
> to
>
> https://sqlite.org/syntax/table-constraint.html
>
> to
>
> https://sqlite.org/syntax/expr.html
>
> and figured expr of `check` in table constraint may contain a nested select
> after `not in`.

These diagrams describe the general syntax recognized by the parser
but a parsable statement can still be rejected at a later compilation
step.



>
> On Sat, Feb 23, 2019, 1:24 PM Clemens Ladisch wrote:
>
>> Rocky Ji wrote:
>> > CREATE TABLE Aliases (
>> >   alias_id INTEGER PRIMARY KEY AUTOINCREMENT,
>> >   real_name TEXT NOT NULL,
>> >   aka TEXT NOT NULL,
>> >   CONSTRAINT xyz UNIQUE (real_name, aka),
>> >   CONSTRAINT noCircularRef_A CHECK (
>> > real_name NOT IN (SELECT aka FROM Aliases)
>> >   ),
>> >   CONSTRAINT noCircularRef_B CHECK (
>> > aka NOT IN (SELECT real_name FROM Aliases)
>> >   )
>> > );
>> >
>> > Error: no such table: Aliases
>>
>>  says:
>> | The expression of a CHECK constraint may not contain a subquery.
>>
>> You'd have to write triggers to check this:
>>
>> CREATE TRIGGER noCircularRef_insert
>> AFTER INSERT ON Aliases
>> FOR EACH ROW
>> WHEN NEW.real_name IN (SELECT aka FROM Aliases)
>>   OR NEW.aka IN (SELECT real_name FROM Aliases)
>> BEGIN
>>   SELECT RAISE(FAIL, "circular reference");
>> END;
>> -- same for AFTER UPDATE OF real_name, aka
>>
>>
>> Regards,
>> Clemens
>> ___
>> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to refer to `this` table?

[Peter da Silva]

As an aside, this schema seems to be violating 
https://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/
 particularly rule 21.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to refer to `this` table?

[Rocky Ji]

I went from

https://sqlite.org/lang_createtable.html

to

https://sqlite.org/syntax/table-constraint.html

to

https://sqlite.org/syntax/expr.html

and figured expr of `check` in table constraint may contain a nested select
after `not in`.

On Sat, Feb 23, 2019, 1:24 PM Clemens Ladisch  Rocky Ji wrote:
> > CREATE TABLE Aliases (
> >   alias_id INTEGER PRIMARY KEY AUTOINCREMENT,
> >   real_name TEXT NOT NULL,
> >   aka TEXT NOT NULL,
> >   CONSTRAINT xyz UNIQUE (real_name, aka),
> >   CONSTRAINT noCircularRef_A CHECK (
> > real_name NOT IN (SELECT aka FROM Aliases)
> >   ),
> >   CONSTRAINT noCircularRef_B CHECK (
> > aka NOT IN (SELECT real_name FROM Aliases)
> >   )
> > );
> >
> > Error: no such table: Aliases
>
>  says:
> | The expression of a CHECK constraint may not contain a subquery.
>
> You'd have to write triggers to check this:
>
> CREATE TRIGGER noCircularRef_insert
> AFTER INSERT ON Aliases
> FOR EACH ROW
> WHEN NEW.real_name IN (SELECT aka FROM Aliases)
>   OR NEW.aka IN (SELECT real_name FROM Aliases)
> BEGIN
>   SELECT RAISE(FAIL, "circular reference");
> END;
> -- same for AFTER UPDATE OF real_name, aka
>
>
> Regards,
> Clemens
> ___
> 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] How to refer to `this` table?

[Amit Yaron]

Try creating a trigger(https://sqlite.org/lang_createtrigger.html) 
instead of the constraint "noCircularRef_when the table already exists.


On 23.2.2019 8:43, Rocky Ji wrote:

If I do

CREATE TABLE Sample (
   id INTEGER PRIMARY KEY AUTOINCREMENT,
   parent_id INTEGER,
   CONSTRAINT p FOREIGN KEY (parent_id) REFERENCES Sample (id)
);

I don't get any errors and the schema behaves as expected. But if I try

CREATE TABLE Aliases (
   alias_id INTEGER PRIMARY KEY AUTOINCREMENT,
   real_name TEXT NOT NULL,
   aka TEXT NOT NULL,
   CONSTRAINT xyz UNIQUE (real_name, aka),
   CONSTRAINT noCircularRef_A CHECK (
 real_name NOT IN (SELECT aka FROM Aliases)
   ),
   CONSTRAINT noCircularRef_B CHECK (
 aka NOT IN (SELECT real_name FROM Aliases)
   )
);

I am getting an `Error: no such table: Aliases` error. So how do I
implement this constraint? Are there any special keywords, like NEW and OLD
of trigger statements, to refer to current table?
___
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] How to refer to `this` table?

[Clemens Ladisch]

Rocky Ji wrote:
> CREATE TABLE Aliases (
>   alias_id INTEGER PRIMARY KEY AUTOINCREMENT,
>   real_name TEXT NOT NULL,
>   aka TEXT NOT NULL,
>   CONSTRAINT xyz UNIQUE (real_name, aka),
>   CONSTRAINT noCircularRef_A CHECK (
> real_name NOT IN (SELECT aka FROM Aliases)
>   ),
>   CONSTRAINT noCircularRef_B CHECK (
> aka NOT IN (SELECT real_name FROM Aliases)
>   )
> );
>
> Error: no such table: Aliases

 says:
| The expression of a CHECK constraint may not contain a subquery.

You'd have to write triggers to check this:

CREATE TRIGGER noCircularRef_insert
AFTER INSERT ON Aliases
FOR EACH ROW
WHEN NEW.real_name IN (SELECT aka FROM Aliases)
  OR NEW.aka IN (SELECT real_name FROM Aliases)
BEGIN
  SELECT RAISE(FAIL, "circular reference");
END;
-- same for AFTER UPDATE OF real_name, aka


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

Re: [sqlite] How to refer to `this` table?

[Simon Slavin]

On 23 Feb 2019, at 6:43am, Rocky Ji  wrote:

> CREATE TABLE Aliases (
>  alias_id INTEGER PRIMARY KEY AUTOINCREMENT,
>  real_name TEXT NOT NULL,
>  aka TEXT NOT NULL,
>  CONSTRAINT xyz UNIQUE (real_name, aka),
>  CONSTRAINT noCircularRef_A CHECK (
>real_name NOT IN (SELECT aka FROM Aliases)
>  ),
>  CONSTRAINT noCircularRef_B CHECK (
>aka NOT IN (SELECT real_name FROM Aliases)
>  )
> );
> 
> I am getting an `Error: no such table: Aliases` error. So how do I
> implement this constraint?

You can't implement a SELECT of the same table inside a CONSTRAINT.  But you 
can inside a TRIGGER.  So implement the last two CONSTRAINTs as TRIGGERs, 
returning RAISE(FAIL)

BEGIN
SELECT RAISE(ABORT, 'aka matches old real_name.')
WHERE EXISTS (
SELECT 1 FROM Aliases WHERE OLD.real_name = NEW.aka
);
END;

The above should work.  The following, which looks better, may work too:

BEGIN
SELECT RAISE(ABORT, 'aka matches old real_name.')
FROM Aliases
WHERE OLD.real_name = NEW.aka;
SELECT RAISE(ABORT, 'real_name matches old aka.')
FROM Aliases
WHERE OLD.aka = NEW.real_name;
END;

Perform your own tests.

You should define these for UPDATE as well as INSERT.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users