Как узнать, уникален ли индекс SQLite? (С SQL)

Я хочу узнать с помощью SQL-запроса, является ли индекс UNIQUE или нет. Я использую SQLite 3.

Я пробовал два подхода:

SELECT * FROM sqlite_master WHERE name = 'sqlite_autoindex_user_1'

Возвращает информацию об индексе ( "тип", "имя", "tbl_name", "корневая страница" и "sql" ). Обратите внимание, что столбец sql пуст, когда индекс автоматически создается SQLite.

PRAGMA index_info(sqlite_autoindex_user_1);

Это возвращает столбцы в индексе ( "seqno", "cid" и "name" ).

Любые другие предложения?

Изменить: Приведенный выше пример предназначен для автоматически сгенерированного индекса, но мой вопрос касается индексов в целом. Например, я могу создать индекс с "CREATE UNIQUE INDEX index1 ON visit (user, date)". Кажется, что никакая команда SQL не покажет, будет ли мой новый индекс UNIQUE или нет.

Ответы

Ответ 1

PRAGMA INDEX_LIST('table_name');

Возвращает таблицу с тремя столбцами:

  • seq Уникальный цифровой идентификатор индекса
  • name Название индекса
  • unique Флаг уникальности (отличный от нуля, если unique index.)

Затем просто зациклируйте результирующие строки, пока не увидите имя индекса, который вы хотите запросить (к сожалению, вы не можете иметь предложение WHERE в инструкции PRAGMA).

Ответ 2

Поскольку никто не придумал хорошего ответа, я думаю, что лучшим решением является следующее:

  • Если индекс начинается с "sqlite_autoindex", это автоматически сгенерированный индекс для одного столбца UNIQUE

  • В противном случае найдите ключевое слово UNIQUE в столбце sql в таблице sqlite_master, с чем-то вроде этого:

    SELECT * FROM sqlite_master WHERE type = 'index' И sql LIKE '% UNIQUE%'

Ответ 3

вы можете программно построить инструкцию select, чтобы увидеть, указывают ли какие-то кортежи более чем на одну строку. Если вы вернетесь к трем столбцам, foo, bar и baz, создайте следующий запрос

select count(*) from t
group by foo, bar, baz
having count(*) > 1

Если это возвращает любые строки, ваш индекс не является уникальным, так как более чем одна строка соответствует указанному кортежу. Если sqlite3 поддерживает производные таблицы (у меня еще есть необходимость, поэтому я не знаю, что с рук), вы можете сделать это еще более кратким:

select count(*) from (
    select count(*) from t
    group by foo, bar, baz
    having count(*) > 1
)

Это возвращает единичный результирующий набор, обозначающий количество дубликатов наборов кортежей. Если положительный, ваш индекс не уникален.

Ответ 4

Вы близко:

1) Если индекс начинается с "sqlite_autoindex", это автоматически сгенерированный индекс для первичного ключа. Однако это будет в таблицах sqlite_master или sqlite_temp_master в зависимости от того, является ли индексируемая таблица временной.

2) Вам нужно следить за именами таблиц и столбцами, которые содержат подстроку unique, поэтому вы хотите использовать:

SELECT * FROM sqlite_master WHERE type = 'index' AND sql LIKE 'CREATE UNIQUE INDEX%'

Смотрите документацию на сайте sqlite на Создать индекс

Ответ 5

Начиная с sqlite 3.16.0 вы также можете использовать функции pragma:

SELECT distinct il.name
  FROM sqlite_master AS m,
       pragma_index_list(m.name) AS il,
       pragma_index_info(il.name) AS ii
 WHERE m.type='table' AND il.[unique] = 1;

В приведенном выше описании будут перечислены все имена уникальных индексов.

SELECT DISTINCT m.name as table_name, ii.name as column_name
  FROM sqlite_master AS m,
       pragma_index_list(m.name) AS il,
       pragma_index_info(il.name) AS ii
 WHERE m.type='table' AND il.[unique] = 1;

Вышеприведенный оператор возвращает все таблицы и их столбцы, если столбец является частью уникального индекса.

Из документов:

Табличнозначные функции для функции PRAGMA были добавлены в SQLite версии 3.16.0 (2017-01-02). Предыдущие версии SQLite не могут использовать эту функцию.