Yes, whoever created that code in the first place did not include anything to test if a column is involved in >1 index in that table. I don't think I've ever myself used a column as both an individual index and also involved as part of a multi-column index.
Karen -----Original Message----- From: 'Lawrence Lustig' via RBASE-L <[email protected]> To: rbase-l <[email protected]> Sent: Wed, Sep 28, 2016 9:30 am Subject: Re: [RBASE-L] - How to drop a foreign key in a table using SQL Okay, I took Karen's code and encapsulated it in a Stored Procedure so that I could automate this. It only handles the simple case as discussed in my previous message (the column is used in only a single index and that index is the foreign key) but this will probably cover 90% of the cases. To use, SET VAR vDeleted = (CALL DropForeignKey('MyTable', 'MyColumn')) and vDeleted will contain Y or N depending on whether it succeeded. ------------------------------------------------------------------- -- DropForeignKey: Given a table and column names, drops the foreign -- key on that column. Returns 'Y' if successful, otherwise 'N'. -- Will fail (return 'N') in complex cases -- PUT DropForeignKey.PRC AS DropForeignKey pDFK_Table TEXT(18) pDFK_Column TEXT(18) RETURN TEXT(1) ------------------------------------------------------------------- IF pDFK_Table IS NULL OR pDFK_Column IS NULL THEN GOTO Failed ENDIF SET VAR pDFK_TableID INT = NULL SELECT SYS_TABLE_ID INTO pDFK_TableID vI FROM SYS_TABLES WHERE SYS_TABLE_NAME = .pDFK_Table IF pDFK_TableID IS NULL THEN GOTO Failed ENDIF SET VAR pDFK_IndexName TEXT = NULL SET VAR pDFK_IndexCount INT = 0 SELECT MAX(SYS_INDEX_NAME), COUNT(*) INTO pDFK_IndexName, pDFK_Count + FROM SYS_INDEXES WHERE SYS_TABLE_ID = .pDFK_TableID AND SYS_COLUMN_NAME = .pDFK_Column + GROUP BY SYS_INDEX_NAME IF pDFK_IndexName IS NULL OR pDFK_Count <> 1 THEN GOTO Failed ENDIF ALTER TABLE &pDFK_Table DROP CONSTRAINT &pDFK_IndexName RETURN 'Y' LABEL Failed CLEAR VAR pDFK_% RETURN 'N' -- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
