CODEWITHSUNDEEP
sqlsqlite
Mike
Mike

Reputation: 1008

How do I find out if a SQLite column is unique? (With SQL)

I have table:

CREATE TABLE test( i INTEGER UNIQUE, j INTEGER UNIQUE, k TEXT );

pragma table_info(test);
       cid = 0
      name = i
      type = INTEGER
   notnull = 0
dflt_value = 
        pk = 0
...

How can I find out is column "i" UNIQUE or not. Thanks

Upvotes: 4

Views: 3800

Answers (4)

Georgi Pashev
Georgi Pashev

Reputation: 61

With PRAGMA index_list(table_name); you get the names of all indexes.

If unique column has value 1, this is index of unique column. After that with PRAGMA index_info(index_name); you get the name of the corresponding column for the indexes.

If your column is in the list of column names, it is unique.

Upvotes: 5

Georgi Pashev
Georgi Pashev

Reputation: 61

This select grouping is not accurate. If there are no records in the table to group, you will be without an answer if according to the TABLE SCHEMA, the column is unique. Even if all groups have count(*)=1, it doesn't mean that according to the schema the column is unique.

Upvotes: 1

Atilla Ozgur
Atilla Ozgur

Reputation: 14701

SELECT COUNT(*),i FROM test
GROUP BY i
HAVING COUNT(*) > 1

-- select all rows from test table
-- group rows according to column i
-- find rows which are greater than 1

Upvotes: 1

Doug Currie
Doug Currie

Reputation: 41170

See this SO related question and answer

sqlite> PRAGMA INDEX_LIST('test');
0|sqlite_autoindex_test_2|1
1|sqlite_autoindex_test_1|1

Upvotes: 1

Related Questions