Drop all foreign keys

[shsandeep]

What is the quickest way to drop all the foreign keys in a database?
Only FKs, not PKs.

Cheers,
San.

[memmerto]

shsandeep wrote:

What is the quickest way to drop all the foreign keys in a database?
Only FKs, not PKs.

You'll have to do this with some manual processing.

The following query will give you a list of all foreign key constraints
in the database:

select tabname, constname from syscat.tabconst where type='F';

Then you can issue a "ALTER TABLE <tabnameDROP FOREIGN KEY
<constname>" for each key listed.

[Knut Stolze]

me******@yahoo. com wrote:

>
shsandeep wrote:

>What is the quickest way to drop all the foreign keys in a database?
Only FKs, not PKs.

You'll have to do this with some manual processing.

The following query will give you a list of all foreign key constraints
in the database:

select tabname, constname from syscat.tabconst where type='F';

Then you can issue a "ALTER TABLE <tabnameDROP FOREIGN KEY
<constname>" for each key listed.

You could wrap this in a stored procedure and issue automatically execute
the DDL statements inside a loop that goes through the result of your above
query. Then you don't have any manual processing any more.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[esmith2112]

shsandeep wrote:

What is the quickest way to drop all the foreign keys in a database?
Only FKs, not PKs.

Cheers,
San.

I do this routinely for certain types of database maintenance. Here's
my UNIX shell script to do so. It generates a file that contains all of
the alter table statements to drop the FKs on the table. (One word of
caution, make sure you have a a copy of the FK defintions elsewhere in
the case you need to reapply them. The output of db2look can come in
handy here.) You'll have to replace some of the variables with info
pertaining to your database.

Regards,
Evan

############### ############### ############### ########
#!/bin/ksh

outfile=fkdrop. ddl

db2 connect to $yourdb user $youruserid using $yourpasswd

db2 "export to fkgen.dat of del \
select constname, tabschema, tabname, reftabschema, reftabname,
fk_colnames, pk_
colnames \
from syscat.referenc es "

echo "-- Foreign Keys as of $(date)" $outfile

cat fkgen.dat |while read line
do
IFS=","
let i=0
for token in $line
do
arr[$i]=$token
let i=$i+1
done

print "\nALTER TABLE ${arr[1]}.${arr[2]}" >$outfile
print "DROP CONSTRAINT ${arr[0]} ;" >$outfile
done

[Brian Tkatch]

me******@yahoo. com wrote:

shsandeep wrote:

What is the quickest way to drop all the foreign keys in a database?
Only FKs, not PKs.

You'll have to do this with some manual processing.

The following query will give you a list of all foreign key constraints
in the database:

select tabname, constname from syscat.tabconst where type='F';

Then you can issue a "ALTER TABLE <tabnameDROP FOREIGN KEY
<constname>" for each key listed.

Or:

SELECT 'ALTER TABLE' || tabname || 'DROP FOREIGN KEY' || constname ||
';' FROM SysCat.TabConst WHERE Type='F';

Though, i usually wrap the names so the 128 characters don't push
things off the screen.

SELECT 'ALTER TABLE' || VARCHAR(TabName , 30) || 'DROP FOREIGN KEY' ||
VARCHAR(ConstNa me, 30) || ';' FROM SysCat.TabConst WHERE Type='F';
B.

[Lennart]

shsandeep wrote:

What is the quickest way to drop all the foreign keys in a database?
Only FKs, not PKs.

Cheers,
San.

I use a pythonscript with the following sql (it also droppes
alternative keys and check constraints):

[...]
sql = """
select tabname, constname from syscat.tabconst
where tabschema = ? and type <'P'
order by type
"""
c1.execute(sql, (schema))
for row in c1.fetchall():
table = row[0]
const = row[1]
drop = """
alter table %s.%s drop constraint %s
""" % (schema,table,c onst)
try:
c2.execute(drop )
except Exception, DB2.error:
print "Warning: %s" % (DB2.error)
pass
[...]

[Knut Stolze]

Brian Tkatch wrote:

me******@yahoo. com wrote:

>shsandeep wrote:

What is the quickest way to drop all the foreign keys in a database?
Only FKs, not PKs.

You'll have to do this with some manual processing.

The following query will give you a list of all foreign key constraints
in the database:

select tabname, constname from syscat.tabconst where type='F';

Then you can issue a "ALTER TABLE <tabnameDROP FOREIGN KEY
<constname>" for each key listed.

Or:

SELECT 'ALTER TABLE' || tabname || 'DROP FOREIGN KEY' || constname ||
';' FROM SysCat.TabConst WHERE Type='F';

Though, i usually wrap the names so the 128 characters don't push
things off the screen.

SELECT 'ALTER TABLE' || VARCHAR(TabName , 30) || 'DROP FOREIGN KEY' ||
VARCHAR(ConstNa me, 30) || ';' FROM SysCat.TabConst WHERE Type='F';

Some spaces are missing. Besides, using RTRIM would be much safer because
it doesn't cause truncation if there are longer table/constraint names.

SELECT 'ALTER TABLE ' || RTRIM(TabName) || ' DROP FOREIGN KEY ' ||
RTRIM(ConstName ) || ';' FROM SysCat.TabConst WHERE Type='F';

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[shsandeep]

Thanks to everyone for replying!!

Cheers,
San.

[Brian Tkatch]

Knut Stolze wrote:

Brian Tkatch wrote:

me******@yahoo. com wrote:

shsandeep wrote:
What is the quickest way to drop all the foreign keys in a database?
Only FKs, not PKs.

You'll have to do this with some manual processing.

The following query will give you a list of all foreign key constraints
in the database:

select tabname, constname from syscat.tabconst where type='F';

Then you can issue a "ALTER TABLE <tabnameDROP FOREIGN KEY
<constname>" for each key listed.

Or:

SELECT 'ALTER TABLE' || tabname || 'DROP FOREIGN KEY' || constname ||
';' FROM SysCat.TabConst WHERE Type='F';

Though, i usually wrap the names so the 128 characters don't push
things off the screen.

SELECT 'ALTER TABLE' || VARCHAR(TabName , 30) || 'DROP FOREIGN KEY' ||
VARCHAR(ConstNa me, 30) || ';' FROM SysCat.TabConst WHERE Type='F';

Some spaces are missing. Besides, using RTRIM would be much safer because
it doesn't cause truncation if there are longer table/constraint names.

SELECT 'ALTER TABLE ' || RTRIM(TabName) || ' DROP FOREIGN KEY ' ||
RTRIM(ConstName ) || ';' FROM SysCat.TabConst WHERE Type='F';

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Oops,iforgotabo utthosespaces!

RTRIM() is nice, but, if the command are to be copied off the screen,
there will be a lot of white space to sift through. Being the command
generates a warning on truncation, and i do such things manually, i use
VARCHAR() instead, and modify the length as required.

B.