Thanks for sharing this great little bit of code, Albert! David Blocker
----- Original Message ----- From: "Albert Berry" <[EMAIL PROTECTED]> To: "RBASE-L Mailing List" <[EMAIL PROTECTED]> Sent: Thursday, June 19, 2003 6:03 PM Subject: [RBASE-L] - Drop a PK and associated FKs automatically! > 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 > >
