Menu
The command pragma table_info('tablename') lists the columns information and pragma foreign_key_list('tablename') the foreign keys. How can I display other constraints (check, unique) of a table? Only parsing the field "sql" of the table "sqlite_master"?
893
select COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_COLUMN_NAME, REFERENCED_TABLE_NAME from information_schema. KEY_COLUMN_USAGE where TABLE_NAME = 'yourTableName'; To display all constraints on a table, implement the above syntax.
If you are running the sqlite3 command-line access program you can type ". tables" to get a list of all tables. Or you can type ". schema" to see the complete database schema including all tables and indices.
To list SQL Server table constraints you'll need to use the sys. default_constraints catalogs view. Avoid using the sysobjects view , which is now deprecated. The query returns the table name, column name, constraint name and constraint definition.
SQLite allows you to define a CHECK constraint at the column level or the table level. In this syntax, whenever a row is inserted into a table or an existing row is updated, the expression associated with each CHECK constraint is evaluated and returned a numeric value 0 or 1.
I think the only way is to do this is the way you suggested, parse the sql column of the sqlite_master database.
Python code to do this:
import sqlite3 con = sqlite3.connect("example.sqlite3") cur = con.cursor() cur.execute("select sql from sqlite_master where type='table' and name='example_table'") schema = cur.fetchone() con.close() entries = [ tmp.strip() for tmp in schema[0].splitlines() if tmp.find("constraint")>=0 or tmp.find("unique")>=0 ] for i in entries: print(i) There's also pragma index_list('tablename')
See http://sqlite.org/pragma.html#pragma_index_list
29
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
