CODEWITHSUNDEEP
sqlitedatabase-indexes
Steve Lloyd
Steve Lloyd

Reputation: 949

How to get unique column indicator in sqlite

For some reason I am unable to include the "unique" column in my return list. Any suggestions?

This query works:

SELECT 
        m.tbl_name as table_name,
        il.name as key_name,
        ii.name as column_name,
        case il.origin when 'pk' then 1 else 0 END as is_primary,
        il.partial,
        il.seq as seq_in_index
    FROM sqlite_master AS m,
        pragma_index_list(m.name) AS il,
        pragma_index_info(il.name) AS ii
    WHERE 
        m.type = 'table'

But This query fails

SELECT 
        m.tbl_name as table_name,
        il.name as key_name,
        ii.name as column_name,
        case il.origin when 'pk' then 1 else 0 END as is_primary,
        case il.unique when 1 then 1 else 0 END as is_unique,
        il.partial,
        il.seq as seq_in_index
    FROM sqlite_master AS m,
        pragma_index_list(m.name) AS il,
        pragma_index_info(il.name) AS ii
    WHERE 
        m.type = 'table'

Upvotes: 1

Views: 41

Answers (1)

forpas
forpas

Reputation: 164099

For SQLite (and other databases) unique is a keyword (reserved word), so you must enclose it inside backticks or square brackets or double quotes:

case il.[unique] when 1 then 1 else 0 END as is_unique

but you could also write:

il.[unique] as is_unique

Upvotes: 1

Related Questions