CODEWITHSUNDEEP
sqlpostgresql
user2866134
user2866134

Reputation: 41

To remove foreign key constraint from tables which are refrenced by given table in postgreSQL

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.

Upvotes: 3

Views: 5609

Answers (1)

31 bit
31 bit

Reputation: 335

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

Upvotes: 3

Related Questions