In ODC I want to know the primary key and all foreign keys of a table. To do that I use this query:
SELECT
col.column_name,
col.data_type,
tc.constraint_type,
tc.constraint_name,
col_description(col.table_name::regclass, col.ordinal_position)
FROM
information_schema.columns AS col
LEFT JOIN
information_schema.key_column_usage AS kcu
ON col.table_name = kcu.table_name
AND col.column_name = kcu.column_name
AND col.table_schema = kcu.table_schema
information_schema.table_constraints AS tc
ON kcu.constraint_name = tc.constraint_name
AND kcu.table_schema = tc.table_schema
information_schema.constraint_column_usage AS ccu
ON tc.constraint_name = ccu.constraint_name
AND tc.table_schema = ccu.table_schema
WHERE
col.table_name = 'decla_ekxjkapfgu7dlmuc0dhjp0w5'
The table has 1 primary key and 14 foreign keys. The result set of the query gives my 1 primary key and 5 foreign keys. So I miss 9 foreign keys. It is noticeable that only the ‘foreign keys’ with a ‘delete rule’ of ‘protected’ are displayed. The foreign keys with a ‘delete rule’ of ‘ignore’ are not displayed. Is there a way to see all foreign keys.
Hi @Danny Eeraerts ,
With Delete Rule = Ignore, no physical foreign key constraint is created in the database, which explains the missing references.
As a workaround, you could create a non-unique index on each ignored reference attribute and use a consistent naming convention, such as Ignore_IndexName.
The query below finds indexes whose physical names contain “ignore” on the specified table and returns their columns’ names, data types, and database comments, like in your original query. It excludes system columns and dropped columns: