I do not remember seeing that…excellent information. Javier, Javier Valencia, PE O: 913-829-0888 H: 913-397-9605 C: 913-915-3137 From: 'James Bentley' via RBASE-L [mailto:[email protected]] Sent: Wednesday, September 28, 2016 10:59 AM To: [email protected] Subject: Re: [RBASE-L] - How to drop a foreign key in a table using SQL Did you now that as of RBase X and RBase XE the Column SYS_INDEX_TYPE contains a numeric value that
tell you the type of the index in question. Here is the meaning of the values 1 =INDEX 2 =UNIQUE INDEX 3 =UNIQUE CASE INDEX 4 =DBF INDEX 5 =PRIMARY KEY 6 =PRIMARY KEY CASE 7 =UNIQUE KEY 8 =UNIQUE KEY CASE 9 =FOREIGN KEY Thus you can include a test "WHERE SYS_INDEX_TYPE=9" to test that it is a foreign key. Jim Bentley, American Celiac Society 1-504-305-2968 _____ From: karentellef via RBASE-L <[email protected]> To: [email protected] Sent: Wednesday, September 28, 2016 9:34 AM Subject: Re: [RBASE-L] - How to drop a foreign key in a table using SQL 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. -- 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.
