如何确定 SQLite 索引是否唯一?(使用 SQL) [英] How do I find out if a SQLite index is unique? (With SQL)

问题描述

我想通过 SQL 查询找出索引是否为 UNIQUE.我正在使用 SQLite 3.

I want to find out, with an SQL query, whether an index is UNIQUE or not. I'm using SQLite 3.

我尝试了两种方法:

SELECT * FROM sqlite_master WHERE name = 'sqlite_autoindex_user_1'

这将返回有关索引的信息(type"、name"、tbl_name"、rootpage"和sql").注意sqlite自动创建索引时sql列为空.

This returns information about the index ("type", "name", "tbl_name", "rootpage" and "sql"). Note that the sql column is empty when the index is automatically created by SQLite.

PRAGMA index_info(sqlite_autoindex_user_1);

这将返回索引中的列(seqno"、cid"和name").

This returns the columns in the index ("seqno", "cid" and "name").

还有其他建议吗?

上面的例子是自动生成的索引，但我的问题是关于索引的.例如，我可以创建一个带有CREATE UNIQUE INDEX index1 ON visit (user, date)"的索引.似乎没有 SQL 命令会显示我的新索引是否为 UNIQUE.

The above example is for an auto-generated index, but my question is about indexes in general. For example, I can create an index with "CREATE UNIQUE INDEX index1 ON visit (user, date)". It seems no SQL command will show if my new index is UNIQUE or not.

推荐答案

PRAGMA INDEX_LIST('table_name');

返回一个包含 3 列的表格:

Returns a table with 3 columns:

1. seq 索引的唯一数字 ID
2. name 索引名称
3. unique 唯一性标志(如果 UNIQUE 索引非零.)

1. seq Unique numeric ID of index
2. name Name of the index
3. unique Uniqueness flag (nonzero if UNIQUE index.)

编辑

从 SQLite 3.16.0 开始，您还可以使用表值 pragma 函数，其优点是您可以 JOIN 它们来搜索特定的表和列.请参阅@mike-scotty 的回答.

Since SQLite 3.16.0 you can also use table-valued pragma functions which have the advantage that you can JOIN them to search for a specific table and column. See @mike-scotty's answer.

这篇关于如何确定 SQLite 索引是否唯一?(使用 SQL)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！