mysql - 如何从 Postgresql 中检索外键信息

Question

我想完全复制 MySql 返回 FK 信息的方式，从 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

虽然这是我最好的 Postgres“副本”:

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 结果集中有一些空值。

TIA!

Answer 1

您正在为 subject_profiles_uq1 使用唯一索引而不是唯一约束。相当多的 RDBMS 支持这样的索引，这些索引通常不会以普通方式列在字典 View 中。例如，当您在 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) |

理想情况下，您不应该使用唯一索引，而应该使用唯一约束。 IE。定义 subject_profiles_uq1 如下:

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 |

如果您绝对需要使用唯一索引，那么您必须改为查询 pg_catalog:

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 标记了您的问题, 所以我假设你正在研究调试 jOOQ 3.11 的这个限制，它目前没有选择唯一索引作为唯一约束:https://github.com/jOOQ/jOOQ/issues/8286