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 values1
=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 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.
--
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.
