I finally figured out how to do this, and actually make it work. The occasion is a
major module enhancement where we had both text and integer ids in a client table,
with the text column as the PK referenced from three other tables, but have about a
dozen other much larger tables with the integer reference value in them.
We wanted to switch the primary key to the integer number. The text references would
be left, using a rule to handle the FK equivalent. The module creation script creates
the new keys based on the integer column and the rules to handle the text columns.
An outer cursor on the primary keys would allow one to drop all foreign an primary
keys in a database systematically, relieving a whole pile of typing.
-- drop a primary key and all its dependent foreign keys automatically
-- Albert Berry 06/2003
SET VAR vPKTable TEXT = 'Client'
SET VAR vPKName TEXT
SET VAR vPKNum INTEGER
SET VAR vFKName TEXT
SET VAR vFKTable TEXT
SELECT SYS_INDEX_NAME,sys_index_id INTO vPKName, vPKNum +
FROM SYS_INDEXES +
WHERE SYS_PRIMARY_KEY = 1 AND SYS_TABLE_ID = +
(SELECT SYS_TABLE_ID +
FROM SYS_TABLES +
WHERE SYS_TABLE_NAME = .vPKTable)
DECLARE c1 CURSOR FOR +
SELECT SYS_TABLE_NAME, SYS_INDEX_NAME +
FROM SYS_INDEXES, SYS_TABLES +
WHERE SYS_INDEXES.SYS_TABLE_ID = SYS_TABLES.SYS_TABLE_ID +
AND sys_foreign_key = .vPKNum
OPEN c1 RESET
FETCH c1 INTO vFKTable,vFKName
WHILE SQLCODE <> 100 THEN
ALTER TABLE &vFKTable DROP CONSTRAINT &vFKName
FETCH c1 INTO vFKTable,vFKName
ENDWHILE
DROP CURSOR c1
ALTER TABLE .vPKTable DROP CONSTRAINT &vPKName
RETURN
--
Albert Berry
Full Time Consultant to
PSD Solutions
350 West Hubbard, Suite 210
Chicago, IL 60610
312-828-9253 Ext. 32
__________________________________________________________________
McAfee VirusScan Online from the Netscape Network.
Comprehensive protection for your entire computer. Get your free trial today!
http://channels.netscape.com/ns/computing/mcafee/index.jsp?promo=393397
Get AOL Instant Messenger 5.1 free of charge. Download Now!
http://aim.aol.com/aimnew/Aim/register.adp?promo=380455
