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
