3

I am new to PostgreSQL. I am struggling with a problem. Problem is that I want to drop foreign key constraint from tables which is referenced by given table . Following query is giving me the list of constraint.

  SELECT
    tc.ct_name, tc.table_name AS fg_tb_name, kcu.column_name AS fg_cn_name, 
    ccu.table_name AS tb_name,
    ccu.column_name AS cn_name,
FROM 
    information_schema.table_constraints AS tc 
    JOIN information_schema.key_column_usage AS kcu
      ON tc.constraint_name = kcu.constraint_name
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND ccu.table_name='dimension' AND ccu.table_schema='public';

It gives me list of tables and name of foreign key which are refrenced by 'dimension' table.

result of query is as follows : - 

ct_name         |   fg_tb_name       | fg_cn_name          | tb_name    | cn_name    | 
--------------------------------------------------------------------------------------
data_ver_fk_cur | data_ver           | cur                 | dimension  | dim_id     |
--------------------------------------------------------------------------------------
data_ver_fk_reg | data_ver           | reg                 | dimension  | dim_id     |
--------------------------------------------------------------------------------------
data_ver_fk_ven | data_ver           | ven                 | dimension  | dim_id     |
---------------------------------------------------------------------------------------

I want to drop all constraints which are present in resultant table. Please suggest me.

user2866134
  • 41
  • 1
  • 3
  • Maybe this helps: http://stackoverflow.com/questions/18271622/generate-sql-to-update-primary-key it takes the output of psql "\d table_name". – guettli Jun 10 '15 at 09:28

1 Answers1

3

check out Magnus Hagander's post offering a script for dropping and recreating all referential integrity. you can probably adopt it to your needs.

31 bit
  • 325
  • 1
  • 10