Check constraints not showing for SQLite databases

Hello all,

First, that's my first post here. I would also like to say that I really like HeidiSQL. That's a great tool for me as I have to manage a few databases on almost all the engines supported. Its features are really good and all of it is free! By the way I made my donation about a week ago! ;-)

I have a question. This is maybe a bug. For my SQLite databases, HeidiSQL is not showing their check constraints at all. Is this a limitations? I have not read something about it anywhere but maybe I am missed it. Any details anybody?

Thank you

Jonathan

Thanks for your appreciation!

I implemented support for check constraints in MySQL/MariaDB, and recently for MSSQL and PostgreSQL as well. I didn't knew SQLite also supports check constraints.

Do you have some SQL query for retrieving the check constraints of a table?

Also, if there is some way for SQLite, you could create a feature request in the bugtracker.

Hello Ansgar !

Nice to have the software author himself answering my question !

So one way to retrieve the check constraints of a table is to get the SQL that was used to create the table, like the following using sqlite_master :

SELECT sql
FROM sqlite_master
WHERE type='table' AND name='<table_name>';

... replacing "<table_name>" by the real table name.

Thanks!

Ok, that would require parsing the returned SQL code - could be prone to errors if not carefully done.

MySQL/MSSQL/PostgreSQL constraints are currently retrieved from information_schema.CHECK_CONSTRAINTS, which is quite stable and efficient. SQLite has no information_schema, and I can't find some pragma statement to retrieve them.

PRAGMA index_list('<tablename>') does not return constraints.

Related: https://stackoverflow.com/questions/9636053/is-there-a-way-to-get-the-constraints-of-a-table-in-sqlite/12142499

Hi Ansgar,

Yes, I am a programmer myself so I know that parsing strings are not ideal in most situations. Unfortunately, for SQLite, for that particular case, we need to go that way.

You can very easily parse the result returned by the above SQL query using the following regular expression pattern :

(?<=check\().+?(?<=\))

Then for example with that SQL string (returned by the query above) :

CREATE TABLE [table](
  [column1], 
  [column2], 
  check(column1 >= 0 AND column1 <= 9), 
  check(column2 IN (0, 1, 2)))

... the matches, as you expected, would be :

column1 >= 0 AND column1 <= 9)
column2 IN (0, 1, 2)

Let me know if it makes sense for you.

Jonathan

That expression is not compatible to the TRegExpr implementation, for some reason it shows an "unexpected modifier at pos 11". I modified it like this: \bcheck\s*\((.+)\).

But it can still be easily tricked by other occurrences of "check xyz" in the code.

Also, these checks are incompatible to the code in MySQL/MariaDB/MSSQL and PostgreSQL - they don't have a name, the DROP syntax is different, the ALTER code is also different.

Ok good, if you changed the RegEx that way and it works, great! I don't know TRegExpr and how it differs from the other implementation.

How a CREATE DDL statement of a table in SQLite can contains other types occurences of "check(...)", other than the check constraints we are looking for?

Yes, check constraints definitions are different in SQLite than in MSSQL by example, but is it really a problem?

SQLite does not support adding or removing constraints from a table directly (i.e. from ALTER TABLE). https://www.sqlite.org/lang_altertable.html