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 <clem...@ladisch.de 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 > 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
