Thursday, September 13, 2012

List all duplicate Foreign Key constraint in PostgreSQL





Below SQL query will return all the referential integrity (Foreign Key ) constraint  in a current database.

This statement return  all the foreign key constraint including duplicate foreign key constraint that's differ in  constraint name for the same   reference (Parent)  table & column for the child table.

example :

 CONSTRAINT fk964cbaa6283cf475 FOREIGN KEY (str_fa_request_id)
      REFERENCES panmydesk4400.d_fa_indent_form (str_fa_request_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT m_cash_purchase_str_fa_request_id_fkey FOREIGN KEY (str_fa_request_id)
      REFERENCES panmydesk4400.d_fa_indent_form (str_fa_request_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,

above example clearly shows that both foreign key constraint ( fk964cbaa6283cf475 and m_cash_purchase_str_fa_request_id_fkey )

 defined in child table of column
str_fa_request_id
refers the same reference table & column.
panmydesk4400.d_fa_indent_form (str_fa_request_id)


SELECT
    pc.conname as constraint_name, 
    --conrelid as child_table_id,   
    pclsc.relname as child_table,
    --pc.conkey as child_column_id,
    pac.attname as child_column,
    --confrelid as parent_table_id,
    pclsp.relname as parent_table,
    --pc.confkey as parent_column_id,
    pap.attname as parent_column,   
    nspname as schema_name
FROM 
    (
    SELECT
         connamespace,conname, unnest(conkey) as "conkey", unnest(confkey)
          as "confkey" , conrelid, confrelid, contype
     FROM
        pg_constraint
    ) pc
    JOIN pg_namespace pn ON pc.connamespace = pn.oid
    -- and pn.nspname = 'panmydesk4400'
    JOIN pg_class pclsc ON pc.conrelid = pclsc.oid
    JOIN pg_class pclsp ON      pc.confrelid = pclsp.oid
    JOIN pg_attribute pac ON pc.conkey = pac.attnum    and pac.attrelid =       pclsc.oid
    JOIN pg_attribute pap ON pc.confkey = pap.attnum and pap.attrelid = pclsp.oid

ORDER BY pclsc.relname

note : 
  •       line start with -- indicate commented 
run the above statement  would return the following result  as output,

it list out all the FK constrain in PostgreSQL

3 comments:

Rajagopalan said...

Hi bro,
i used a function like, select pg_ls_dir('C:\\online\\');, but it shows error as "absolute path not allowed". how can i solve this?

dataanalytics said...

can read the files only from the data directory path, if you would like to know your data directory path use
SHOW data_directory
It would return like "/var/lib/postgresql/9.1/main"

Veronica Davenport said...

Good reading your poost