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 FailedENDIF
SET VAR pDFK_TableID INT = NULLSELECT SYS_TABLE_ID INTO pDFK_TableID vI FROM
SYS_TABLES WHERE SYS_TABLE_NAME = .pDFK_TableIF pDFK_TableID IS NULL THEN GOTO
FailedENDIF
SET VAR pDFK_IndexName TEXT = NULLSET VAR pDFK_IndexCount INT = 0SELECT
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_NAMEIF pDFK_IndexName IS NULL OR pDFK_Count
<> 1 THEN GOTO FailedENDIF
ALTER TABLE &pDFK_Table DROP CONSTRAINT &pDFK_IndexName
RETURN 'Y'
LABEL FailedCLEAR 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.
