Use a trigger and make it raise an exception, something like (not tested, just an example of how it might work):
CREATE TRIGGER <name> BEFORE INSERT ON <table> WHEN NEW.parent == NEW.child BEGIN RAISE(FAIL, 'connecting to self not allowed') END; You may also require a similar BEFORE UPDATE trigger to avoid changes that violate the condition. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Shane Dev Gesendet: Mittwoch, 20. Dezember 2017 07:30 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: [EXTERNAL] [sqlite] Can we create a table where the value of one particular column <> another column? Let's say we have nodes and edges tables - sqlite> .sch nodes CREATE TABLE nodes(id integer primary key, description text); sqlite> .sch edges CREATE TABLE edges(parent references nodes, child references nodes); Can we restrict the edges table so that inserting or updating a row where edges.parent = edges.child is not allowed and ideally would produce an error message? sqlite> insert into nodes select 1, 'node1'; insert into nodes select 2, sqlite> 'node2'; insert into edges select 1, 2; insert into edges select sqlite> 1, 1; -- should be an error here _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
