I would like to replicate exactly the way MySql returns informations on FKs extracting them from Information_schema tables.
I have these tables on 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);
And their replica on 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);
Now, this query on 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
Returns:
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
While this my best Postgres "replica":
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'
Returns:
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>
So, finally, in which way I should rewrite my Postgres query in order to retrieve all the informations about referenced tables and columns?
I believe there is something I'm missing as there are some nulls in the Postgresql result set.
TIA!
You're using a unique index instead of a unique constraint for subject_profiles_uq1. Quite a few RDBMS support such indexes, which are usually not listed in the ordinary way in the dictionary views. For example, when you run this query on your PostgreSQL database:
SELECT constraint_name, unique_constraint_name
FROM information_schema.referential_constraints;
You should get something like this:
| 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) |
Ideally, you should not use unique indexes, but unique constraints. I.e. define subject_profiles_uq1 as such:
ALTER TABLE test.subject_profiles
ADD CONSTRAINT subject_profiles_uq1
UNIQUE (id_subject, id_profile);
In case of which your query will work. SQL Fiddle here, producing:
| 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 |
If you absolutely need to use a unique index, then you have to query the pg_catalog instead:
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'
You tagged your question with jooq, so I'm assuming you're looking into debugging this limitation of jOOQ 3.11, which currently does not pick up unique indexes as unique constraints: https://github.com/jOOQ/jOOQ/issues/8286