Interbase DDL Drop Foreign Key

I produce a product that uses an IB database.  On the last release we included sql which created a FK on a table.  I now want to send sql that will remove the table.  To do so I need to drop the constraint.  I can find the name of the constraint with this sql:

select rdb$index_name
from rdb$indices i
inner join rdb$relation_constraints c on
(i.rdb$index_name = c.rdb$index_name)
where
  rdb$foreign_key in (
    select rdb$index_name
      from rdb$indices i
        inner join rdb$relation_constraints c on
        (i.rdb$index_name = c.rdb$index_name)
      where upper(rdb$relation_name) = 'MY_FIELD');

However, since this is a script, I haven't been able to use the retrieved info.  I thought to create a generic procedure to handle this situation (and any future ones that may arise), but it can't handle a variable for the table name:

SET TERM ^ ;

CREATE PROCEDURE DROP_FK (
    TABLE_NAME VARCHAR(255),
    FK_FIELD VARCHAR(255))
AS
DECLARE VARIABLE FK_NAME VARCHAR(255);
begin
  /* Procedure Text */
  select rdb$index_name
  from rdb$indices i
    inner join rdb$relation_constraints c on
    (i.rdb$index_name = c.rdb$index_name)
  where rdb$foreign_key in (
    select rdb$index_name
      from rdb$indices i
        inner join rdb$relation_constraints c on
        (i.rdb$index_name = c.rdb$index_name)
      where upper(rdb$relation_name) = :FK_FIELD)
  into :FK_Name;

  alter :TABLE_NAME drop constraint :FK_NAME;
end
^

SET TERM ; ^

Any one know how I can achieve the removal of this constraint without writing an app to fix it?  
LVL 2
DLancyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nick UpsonPrincipal Operations EngineerCommented:
do it in 2 stages

select 'drop constraint ' || rdb$index_name || ';' ...

redirecting the output into a file, which should wind up with just

drop constraint xxx;

then pass to the sql engine that, known, filename of sql/ddl statements

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kacorretiredCommented:
Hi DLancy,

for indexes, restrictions, constraints etc. you can add a name. If you know this name later you can easy delete, modify, reference it by using this name. The similar elements (for example secondary keys connected to the primary key) are deletable only if this connection will be deleted or no references are used in the database to this property.

Therefore if you'll create constraints in the future it would be advisable to rename it.

wbr

Janos
0
kacorretiredCommented:
If you have to find a restriction which you didn't give a name you'll find this kind of restrictions in the RDB$RELATION_CONSTRAINTS table. You set only a SELECT command from ISQL like this:

SLECT * from RDB$RELATION_CONSTRAINTS

The first column is the RDB$CONSTRAINTS_NAME and contains what you are looking for.

Janos
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

DLancyAuthor Commented:
Nick - Do you know if you can have isql run output to a file like that  from the command line?  Even if you can, it may be just as much work a writing a small app to do it.

Janos - If you read my question fully, you'd see I already covered that.  The problem is it will be run by clients on their independent systems when they receive the upgrade and unfortunately it was not named.  So for each client depending if they've added other foreign keys, I can't be positive of the name.  Also since I'm running this through DDL scripting, I need to find the name to drop.

Sounds as if there is no good SQL solution and it will be easier to write a quick app to handle this.  Thanks guys.
0
Nick UpsonPrincipal Operations EngineerCommented:
yes you can, just redirect the output of the isql command, "isql .... > my_file"
0
kacorretiredCommented:
Sorry DLancy, I overlooked this phrase

Janos
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.