今天需要一个查询Oracle某张表全部唯一约束字段的功能,上网查了一下,找到一段sql,查询的user_constraints表,试了下查询不到我要的表的唯一约束,接着查发现全都是复制粘贴的一样的,后来找了下JDBC的DatabaseMetaData类,发现了一个getIndexInfo方法,是可以查到唯一约束的,但是会有一个null,而且type是0和1,出于好奇翻了下Oracle的JDBC驱动源码,发现Oracle并不是查询的user_constraints表,下面是Oracle驱动中的sql
select null as table_cat,
owner as table_schem,
table_name,
0 as NON_UNIQUE,
null as index_qualifier,
null as index_name, 0 as type,
0 as ordinal_position, null as column_name,
null as asc_or_desc,
num_rows as cardinality,
blocks as pages,
null as filter_condition
from all_tables
where table_name = '表名'
and owner = 'schema名'
union
select null as table_cat,
i.owner as table_schem,
i.table_name,
decode (i.uniqueness, 'UNIQUE', 0, 1),
null as index_qualifier,
i.index_name,
1 as type,
c.column_position as ordinal_position,
c.column_name,
null as asc_or_desc,
i.distinct_keys as cardinality,
i.leaf_blocks as pages,
null as filter_condition
from all_indexes i, all_ind_columns c
where i.table_name = '表名'
and i.owner = 'schema名'
and i.uniqueness = 'UNIQUE'
and i.index_name = c.index_name
and i.table_owner = c.table_owner
and i.table_name = c.table_name
and i.owner = c.index_owner
order by non_unique, type, index_name, ordinal_position
可以看到它把表作为一个index与UNIQUE约束的查询结果union后做了个排序,所以JDBC中永远会查到一个列名为null的结果,由于我个人不需要这个,因此去掉union半部分和order by即可