Java 查找数据库表的唯一约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1674223/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find a Database table's unique constraint
提问by Nils
I'm trying to find the unique constraints of a table using Java (on an Oracle Database, but that should make no difference).
我正在尝试使用 Java 查找表的唯一约束（在 Oracle 数据库上，但这应该没有区别）。
I found a way to discover the Primary Keys of a table, thanks to DatabaseMetaData's getPrimaryKeys(....); However I was unable to find the unique constaints of the tables, and the Internet was not able to help me, therefore I'm ending here asking my question :)
感谢 DatabaseMetaData 的 getPrimaryKeys(....); 我找到了一种发现表主键的方法；但是我无法找到表格的唯一约束，互联网也无法帮助我，因此我在这里结束提问:)
Is there a clean way to find the unique constraints (or, rather, the name of the columns that must be unique for a table.. Well you get it hehe) of a table ? Best regards,
有没有一种干净的方法来查找表的唯一约束（或者，更确切地说，表的列名必须是唯一的......好吧，你明白了，呵呵）？此致，
Nils
尼尔斯
回答by Vincent Malgrat
you can query the data dictionary:
您可以查询数据字典：
SQL> SELECT cc.*
2 FROM all_constraints c
3 JOIN all_cons_columns cc ON (c.owner = cc.owner
4 AND c.constraint_name = cc.constraint_name)
5 WHERE c.constraint_type = 'U'
6 AND c.table_name = 'T';
OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION
---------- ----------------- -------------- ------------- ----------
VNZ UNIQUE_COL T COLUMN1 1
VNZ UNIQUE_COL T COLUMN2 2
VNZ UNIQUE_COL2 T COLUMN2 1
回答by JRL
If Oracle creates indexes for unique constraints (I don't know if it does, you need to check) than you could find out about your constraints via getIndexInfo()
如果 Oracle 为唯一约束创建索引（我不知道是否这样做，您需要检查）那么您可以通过以下方式找到您的约束 getIndexInfo()
回答by Adam Hawkes
Unique constraints are usually enforced by a index. Perhaps use DatabaseMetaData.getIndexInfo()to find the indexes where the non-unique is false?
唯一约束通常由索引强制执行。也许用于DatabaseMetaData.getIndexInfo()查找非唯一性为假的索引？
回答by Silveri
Since most databases store these constraints as an index, you can use DatabaseMetaData.getIndexInfo()as previously mentioned. This worked well for me when using Postgresql.
由于大多数数据库将这些约束存储为索引，因此您可以使用前面提到的DatabaseMetaData.getIndexInfo()。使用Postgresql时，这对我很有效。
It's only important to call getIndexInfo()with the 4th parameter as trueas the documenation says:
正如文档所说getIndexInfo()，使用第四个参数调用是很重要的true：
unique- when true, return only indices for unique values; when false, return indices regardless of whether unique or not
unique- 当为真时，只返回唯一值的索引；当为假时，无论是否唯一都返回索引
With the following code:
使用以下代码：
// Class to combine all columns for the same index into one object
public static class UniqueConstraint {
public String table;
public String name;
public List<String> columns = new ArrayList<>();
public String toString() {
return String.format("[%s] %s: %s", table, name, columns);
}
}
public static List<UniqueConstraint> getUniqueConstraints(Connection conn, String schema, String table) throws SQLException {
Map<String, UniqueConstraint> constraints = new HashMap<>();
DatabaseMetaData dm = conn.getMetaData();
ResultSet rs = dm.getIndexInfo(null, schema, table, true, true);
while(rs.next()) {
String indexName = rs.getString("index_name");
String columnName = rs.getString("column_name");
UniqueConstraint constraint = new UniqueConstraint();
constraint.table = table;
constraint.name = indexName;
constraint.columns.add(columnName);
constraints.compute(indexName, (key, value) -> {
if (value == null) { return constraint; }
value.columns.add(columnName);
return value;
});
}
return new ArrayList<>(constraints.values());
}
you can call:
你可以打电话：
getUniqueConstraints(conn, "public", tableName);
and get back a list of all the unique constraints for a given table. The constraints are grouped by index since one index can cover multiple columns if they are only unique in combination.
并获取给定表的所有唯一约束的列表。约束按索引分组，因为一个索引可以覆盖多个列，如果它们仅在组合中是唯一的。
