我想复制MySql返回FKs信息的方式，从Information-schema表中提取它们。
我在MySql上有这些表：
create table test.subjects (
ID_SUBJECT bigint NOT NULL AUTO_INCREMENT,
FULL_NAME varchar(200) NOT NULL,
PRIMARY KEY (ID_SUBJECT)
);
create table test.request_state (
ID_REQUEST_STATE char(3) NOT NULL,
DESCRIPTION varchar(80) NOT NULL,
PRIMARY KEY (ID_REQUEST_STATE)
);
create table test.subject_profiles (
ID_SUBJECT_PROFILES bigint NOT NULL AUTO_INCREMENT,
ID_SUBJECT bigint NOT NULL,
ID_PROFILE bigint,
PRIMARY KEY (ID_SUBJECT_PROFILES)
);
CREATE UNIQUE INDEX subject_profiles_uq1
ON test.subject_profiles (ID_SUBJECT, ID_PROFILE);
ALTER TABLE test.subject_profiles add
CONSTRAINT subject_profiles_fk1
foreign key (ID_SUBJECT)
REFERENCES test.subjects (ID_SUBJECT) on delete cascade;
create table test.demand (
ID_DEMAND bigint NOT NULL AUTO_INCREMENT,
ID_SUBJECT bigint NOT NULL,
DEMAND_STATE char(3) not null,
ID_PROFILE bigint,
PRIMARY KEY (ID_DEMAND)
);
ALTER TABLE test.demand add
CONSTRAINT demand_fk1
foreign key (ID_SUBJECT)
REFERENCES test.subjects (ID_SUBJECT) on delete cascade;
ALTER TABLE test.demand add
CONSTRAINT demand_fk2 foreign key (DEMAND_STATE) REFERENCES test.request_state (ID_REQUEST_STATE);
alter table test.demand
add CONSTRAINT demand_fk3
foreign key (ID_SUBJECT, ID_PROFILE)
REFERENCES test.subject_profiles (ID_SUBJECT, ID_PROFILE);
以及他们在Postgresql上的复制品：
create table test.subjects (
ID_SUBJECT bigint NOT NULL,
FULL_NAME varchar(200) NOT NULL,
PRIMARY KEY (ID_SUBJECT)
);
create table test.request_state (
ID_REQUEST_STATE char(3) NOT NULL,
DESCRIPTION varchar(80) NOT NULL,
PRIMARY KEY (ID_REQUEST_STATE)
);
create table test.subject_profiles (
ID_SUBJECT_PROFILES bigint NOT NULL,
ID_SUBJECT bigint NOT NULL,
ID_PROFILE bigint,
PRIMARY KEY (ID_SUBJECT_PROFILES)
);
CREATE UNIQUE INDEX subject_profiles_uq1
ON test.subject_profiles (ID_SUBJECT, ID_PROFILE);
ALTER TABLE test.subject_profiles add
CONSTRAINT subject_profiles_fk1
foreign key (ID_SUBJECT)
REFERENCES test.subjects (ID_SUBJECT) on delete cascade;
create table test.demand (
ID_DEMAND bigint NOT NULL,
ID_SUBJECT bigint NOT NULL,
DEMAND_STATE char(3) not null,
ID_PROFILE bigint,
PRIMARY KEY (ID_DEMAND)
);
ALTER TABLE test.demand add
CONSTRAINT demand_fk1
foreign key (ID_SUBJECT)
REFERENCES test.subjects (ID_SUBJECT) on delete cascade;
ALTER TABLE test.demand add
CONSTRAINT demand_fk2
foreign key (DEMAND_STATE) REFERENCES test.request_state (ID_REQUEST_STATE);
alter table test.demand
add CONSTRAINT demand_fk3
foreign key (ID_SUBJECT, ID_PROFILE)
REFERENCES test.subject_profiles (ID_SUBJECT, ID_PROFILE);
现在，这个MySql查询：
SELECT
CONCAT(table_name) AS table_name, CONCAT(column_name) AS column_name,
CONCAT(referenced_table_name) AS referenced_table_name,
CONCAT(referenced_column_name) AS referenced_column_name
FROM
INFORMATION_SCHEMA.key_column_usage
WHERE
referenced_table_schema = 'subjects_data'
and referenced_table_name IS NOT NULL
and table_name = 'demand'
ORDER BY table_name, column_name
返回：
table_name column_name referenced_table_name referenced_column_name
---------------------------------------------------------------------
demand DEMAND_STATE request_state ID_REQUEST_STATE
demand ID_PROFILE subject_profiles ID_PROFILE
demand ID_SUBJECT subjects ID_SUBJECT
demand ID_SUBJECT subject_profiles ID_SUBJECT
尽管这是我最好的“复制品”：
SELECT
tc.table_name,
kcu.column_name,
ccu.table_name as references_table,
ccu.column_name as references_field
FROM
information_schema.table_constraints tc
LEFT JOIN information_schema.key_column_usage kcu
ON tc.constraint_catalog = kcu.constraint_catalog
AND tc.constraint_schema = kcu.constraint_schema
AND tc.constraint_name = kcu.constraint_name
LEFT JOIN information_schema.referential_constraints rc
ON tc.constraint_catalog = rc.constraint_catalog
AND tc.constraint_schema = rc.constraint_schema
AND tc.constraint_name = rc.constraint_name
LEFT JOIN information_schema.constraint_column_usage ccu
ON rc.unique_constraint_catalog = ccu.constraint_catalog
AND rc.unique_constraint_schema = ccu.constraint_schema
AND rc.unique_constraint_name = ccu.constraint_name
WHERE
tc.constraint_catalog = 'subjects_db'
and tc.constraint_schema = 'test'
and tc.table_name = 'demand'
and tc.constraint_type = 'FOREIGN KEY'
返回：
table_name column_name referenced_table_name referenced_column_name
---------------------------------------------------------------------
demand id_subject subjects id_subject
demand demand_state request_state id_request_state
demand id_subject <null> <null>
demand id_profile <null> <null>
所以，最后，我应该用哪种方式重写Postgres查询，以便检索有关被引用表和列的所有信息？
我相信我遗漏了一些东西，因为Postgresql结果集中有一些空值。
蒂亚！
最佳答案
对于subject_profiles_uq1，您使用的是唯一索引而不是唯一约束。相当多的RDBMS支持这样的索引，这些索引通常不会以普通的方式列在字典视图中。例如，在PostgreSQL数据库上运行此查询时：
SELECT constraint_name, unique_constraint_name
FROM information_schema.referential_constraints;
你应该得到这样的东西：
| constraint_name | unique_constraint_name |
|-----------------------|------------------------|
| deferred_17_aba21_ref | deferred_check_pkey |
| subject_profiles_fk1 | subjects_pkey |
| demand_fk1 | subjects_pkey |
| demand_fk2 | request_state_pkey |
| demand_fk3 | (null) |
理想情况下，不应使用唯一索引，而应使用唯一约束。即定义为：
ALTER TABLE test.subject_profiles
ADD CONSTRAINT subject_profiles_uq1
UNIQUE (id_subject, id_profile);
在这种情况下，您的查询将起作用。SQL Fiddle here，生产：
| table_name | column_name | references_table | references_field |
|------------|--------------|------------------|------------------|
| demand | id_subject | subjects | id_subject |
| demand | demand_state | request_state | id_request_state |
| demand | id_subject | subject_profiles | id_profile |
| demand | id_subject | subject_profiles | id_subject |
| demand | id_profile | subject_profiles | id_profile |
| demand | id_profile | subject_profiles | id_subject |
如果绝对需要使用唯一索引，则必须查询
subject_profiles_uq1：SELECT fc.relname, fa.attname, uc.relname, ua.attname
FROM pg_catalog.pg_constraint f
JOIN pg_namespace fn ON f.connamespace = fn.oid
JOIN pg_catalog.pg_class fc ON f.conrelid = fc.oid
JOIN pg_attribute fa ON fa.attrelid = fc.oid AND fa.attnum = ANY(f.conkey)
JOIN pg_catalog.pg_class uc ON f.confrelid = uc.oid
JOIN pg_attribute ua ON ua.attrelid = uc.oid AND ua.attnum = ANY(f.confkey)
WHERE f.contype = 'f'
AND fc.table_name = 'demand'
AND fn.nspname = 'test'
关于乔克
您用jooq标记了您的问题，所以我假设您正在调试jOOQ 3.11的这个限制，它目前没有将唯一索引作为唯一约束：https://github.com/jOOQ/jOOQ/issues/8286