On 23 Feb 2019, at 6:43am, Rocky Ji <rockyji3...@gmail.com> 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
