如何查找给定列是否存在唯一的键约束

I am working on a perl script, where i need to run update queries. But I need to check if the update sql command does not violate unique key constraint. So if I have a table tb(C1,C2,C3) and my update query is like:

我正在处理perl脚本，需要在该脚本中运行更新查询。但是我需要检查update sql命令是否没有违反唯一键约束。如果我有一个表tb(C1,C2,C3)我的更新查询是:

update tb set C1='b1' where C2='a1' ;

Is there a way to find if a unique key constraint exist for column C1,C2 before trying to update? Ie: UNIQUE(C1,C2).

是否有一种方法可以在尝试更新之前查找列C1、C2是否存在唯一的键约束?即:独特(C1,C2)。

2 个解决方案

#1

You can query the system catalogs for unique constraints, in particular pg_constraint and pg_attribute:

您可以查询系统编目以获得唯一的约束，特别是pg_constraint和pg_attribute:

SELECT c.conname, pg_get_constraintdef(c.oid)
FROM   pg_constraint c
JOIN  (
   SELECT array_agg(attnum::int) AS attkey
   FROM   pg_attribute
   WHERE  attrelid = 'tb'::regclass  -- table name optionally schema-qualified
   AND    attname  = ANY('{c1,c2}') 
   ) a ON c.conkey::int[] <@ a.attkey AND c.conkey::int[] @> a.attkey
WHERE  c.contype  = 'u'
AND    c.conrelid = 'tb'::regclass;

• The object identifer type regclass helps to unambiguously identify your table.

  对象标识符类型regclass有助于明确地标识表。

• The system catalog information function pg_get_constraintdef() gets you nicely formatted information, which is not strictly necessary for your request.

  系统目录信息函数pg_get_constraintdef()为您提供格式良好的信息，这对您的请求是不需要的。

• Also using array operators <@ and @> to make sure the arrays match completely. (The order of columns is unknown.) The system columns are smallint and smallint[] respectively. Cast to integer to make it work with those operators.

  也使用数组操作符<@和@>来确保数组完全匹配。(列的顺序是未知的。)系统列分别是smallint和smallint[]。转换为integer以使它与这些操作符一起工作。

• Column names are case sensitive when looking them up in the system catalog directly. If you didn't double-quote C1 and C2 at creation time, you have to use c1 and c2 in this context.

  当在系统目录中直接查找时，列名是区分大小写的。如果在创建时没有双引号C1和C2，在此上下文中必须使用C1和C2。

• There could also be a multicolumn primary key constraint enforcing uniqueness. To cover that in the query use instead:

  还可以有一个多色主键约束强制惟一性。要在查询中涵盖这一点，请改为:

  WHERE  c.contype IN ('u', 'p')
  

Building on @Roman's fiddle, this one also demonstrates the pk case:

以@Roman的小提琴为基础，这个例子也展示了pk案例:

->SQLfiddle

- > SQLfiddle

Unique index

Both of the above (unique & pk constraints) are implemented by way of a unique index. In addition there can also be unique indices doing effectively the same as formally declared unique constraint. To catch all of them query the system catalog pg_index instead, in a similar fashion:

上述两个约束(unique & pk约束)都是通过唯一索引实现的。此外，还可以有唯一指标有效地执行与正式声明的唯一约束相同的操作。要捕获它们，请以类似的方式查询系统目录pg_index:

SELECT c.relname AS idx_name
FROM  (
   SELECT indexrelid, string_to_array(indkey::text, ' ')::int[] AS indkey
   FROM   pg_index
   WHERE  indrelid = 'tb'::regclass
   AND    indisunique                    -- contains "indisprimary"
   ) i
JOIN  (
   SELECT array_agg(attnum::int) AS attkey
   FROM   pg_attribute
   WHERE  attrelid = 'tb'::regclass
   AND    attname  = ANY('{c1,c2}')
   ) a ON i.indkey <@ a.attkey AND i.indkey @> a.attkey
JOIN   pg_class c ON c.oid = i.indexrelid;

Special difficulty here is the internal type int2vector. I deal with it by casting text and converting to int[].

这里的特别困难是内部类型int2vector。我通过转换文本和转换为int[]来处理它。

Be aware that implementation of catalog tables might change across major. Unlikely that these queries break, but possible.

请注意，目录表的实现可能会在各个主要方面发生变化。这些查询不太可能中断，但可能。

#2

You can find out whether a constraint exists (non-portably) by inspecting the pg_catalog.pg_constraint table, but that won't tell you whether the constraint would be violated by your insert, and it would be prone to races even if it could.

通过检查pg_catalog，可以发现是否存在约束(不能移植)。pg_constraint表，但这不能告诉您插入是否会违反约束，即使可能，它也很容易导致竞争。

The correct thing to do is to try the insert and handle a failure appropriately.

正确的做法是尝试插入并适当地处理失败。