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 >> >> <https://www.sqlite.org/lang_createtable.html#ckconst> 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 >> [email protected] >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > _______________________________________________ > sqlite-users mailing list > [email protected] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
