getting SQlite Column Constraints

Databases
1

I would like to find out in code if a column is specified as unique.

Looking at the Xoxo docs, SQLite docs on pragmas, the SQLite_Master and sqlite_sequence tables I can’t find a way to do it.

Is there a way to do that short of trying to parse the SQL create table statements? (of course a constrained added after wards as a unique index too…)

Thanks

  • Karen
2

Karen

I am not at my machine at present but I am pretty sure that the FieldSchema method returns a value for Unique items.

Hunt in the help for TableSchema and FieldSchema.

3

No it does not unfortunately. The columns that returns are:

ColumnName As String, FieldType As Integer, IsPrimary As Boolean, NotNull As Boolean and Length As Integer.

4

Oops, sorry.

I mixed up IsPrimary with Unique.

5

Yeah - short of parsing the create table / create index (in case its just a unique index) statements I don’t think there’s any built in way in sqlite or Xojo to get that.
I had a quick peek at SQLIte’s docs & I dont see anything

6

Thanks Norm, I was afraid of that.

I would guess SQLite provides a way to get that info using C calls, but not at the SQL level.

7

I’m not so sure

This API method certainly jumps out as one to call to get this information but it also doesn’t tell you http://sqlite.org/c3ref/table_column_metadata.html
However, the API is large & I have not examined 100% of the functions

8

The way I have done this is to use 2 sqlite Pragma calls. Pop the results of PRAGMA INDEX_LIST (table_name) into a record set, that will give you fields called ‘name’ and ‘unique’. Loop through the unique ones creating another record set using PRAGMA INDEX_INFO ( index_name) - then you will get the column names that make up the unique index.

9

I cannot work out how to call the pragmas you are describing!

Can you post a code snippet?

10

I used that pragma . It gives you the name of the index not the name of the column …and remember indexes can be across multiple columns.

11

[quote=31282:@Simon Berridge]I cannot work out how to call the pragmas you are describing!

Can you post a code snippet?[/quote]
Use SQLSelect for SQLite PRAGMA commands that return a value:

Dim rs As RecordSet rs = db.SQLSelect("PRAGMA INDEX_LIST('table')")

12

I am getting a nil object when I use the code:

[code] dim db As new SQLiteDatabase
db.DatabaseFile = f // f has been set earlier
dim rs As RecordSet

if db.Connect then
rs = db.SQLSelect(“pragma index_list(accounts)”)
if rs <> nil then
MsgBox “Ok”
end if
end if
[/code]

I get to the ‘rs =’ part but rs is nil after the call.

13

Also tried it this way:

if db.Connect then rs = db.SQLSelect("pragma index_list('ACCOUNTS')") if rs <> nil then MsgBox "Ok" end if end if
Same result, rs is returned as nil.

14

Do those tables have indexing on them? You should also check db.ErrorMessage to see what is says.

15

What an idiot I am!

The table did not have an index! When tested with a table with an index it works!

Sorry, guys!

16

I don’t know if the pragma index_list command returning nil if no indexes is found is a bug - sounds like one to me, but you can use the sqlite_master table instead:

select tbl_name, name from sqlite_master
where type=‘index’
and table_name = ‘table_name’

then use pragma index_info to find if the index is unique, then use pragma index_list to find the columns! It’s a bit of a faff, but I can’t think of any other way. You could of course look at table creation SQL but that wouldn’t give you indexes added after creation.