Drop foreign key in some tables.

georgipa · #1 (**permalink**) 02-27-11, 15:04

Hi Colleagues,

Somebody can say me what in the best method to drop all foreign key in one or more tables in db2 9.5.

Thank you for advanced.

stolze · #2 (**permalink**) 02-28-11, 09:40

If you don't need the tables, just drop the tables. Otherwise, ALTER TABLE ... DROP CONSTRAINT ...

georgipa · #3 (**permalink**) 02-28-11, 15:28

I have database with 1000 tables, i need drop the foreign key for tables xxx, table yyy and table zzz. Not for all tables.

Thank you for advanced.

dr_te_z · #4 (**permalink**) 03-01-11, 06:43

You can run db2look and extract to FK-data. Or run this query:

Code:

select varchar(  
  'alter table  '  
|| lcase(rtrim(a.fktable_name))
|| space (32 - length(rtrim(a.fktable_name)))
|| 'drop constraint '  
|| lcase(rtrim(a.fk_name))
|| ';' 
,80) as cmdline 
from	sysibm.sqlforeignkeys a
where key_seq = 1
and	(   (pktable_schem = current_schema and pktable_name in ('XXX' , 'YYY'))
         or (fktable_schem = current_schema and fktable_name in ('XXX' , 'YYY'))
);

Internet Services

Web Development

Digital Marketing

Consumer Tech