Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is there a way to get the constraints of a table in SQLite?

Tags:

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"?

like image 893
fran Avatar asked Mar 09 '12 14:03

fran


People also ask

How do I find constraints on a table in SQL?

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.

How do I get table information in SQLite?

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.

How do I get a list of constraints on a table in SQL Server?

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.

Does SQLite support check constraints?

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.


2 Answers

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) 
like image 134
jftuga Avatar answered Oct 21 '22 19:10

jftuga


There's also pragma index_list('tablename')

See http://sqlite.org/pragma.html#pragma_index_list

like image 29
Nabab Avatar answered Oct 21 '22 18:10

Nabab