A CHECK constraint is just a special trigger that allows you to raise an error and only runs on INSERT and UPDATE.
It has nothing to do with internal database structure (pragma integrity_check) or with foreign keys (pragma foreign_key_check; you have to enable foreign key checking first anyway). If you are disabling CHECK constraints for a specific reason, what causes that reason to go away? -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Dominique Devienne Gesendet: Mittwoch, 22. Februar 2017 14:04 An: General Discussion of SQLite Database <sqlite-users@mailinglists.sqlite.org> Betreff: [sqlite] No way to check for CHECK constraint violations a posteriori Neither {{pragma integrity_check}} nor {{pragma foreign_check_check}} checks CHECK constraints. Given that there's {{pragma ignore_check_constraints = 1}} which allows to bypass CHECK constraints, that seems like a surprising oversight. I even tried {{pragma ignore_check_constraints = 0}} in case it was re-validate CHECK constraint on that mode change, but apparently not. Could we please have a new {{pragma check_constraints_check}} pragma, or alternatively have {{pragma integrity_check}} actually check CHECK constraint? Thanks, --DD PS: Also note that {{pragma integrity_check}} (or quick_check) and {{pragma foreign_check_check}} differ in behavior, one return ok, the other nothing. Ideally they'd be consistent. C:\Users\ddevienne>sqlite3 SQLite version 3.10.2 2016-01-20 15:27:19 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table t (v CHECK (typeof(v) = 'integer')); insert into t sqlite> values (1); insert into t values ('two'); Error: CHECK constraint failed: t sqlite> pragma ignore_check_constraints = 1; insert into t values sqlite> ('two'); pragma integrity_check; ok sqlite> pragma foreign_key_check; sqlite> pragma ignore_check_constraints = 0; pragma quick_check; ok _______________________________________________ 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 FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
