[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Interbase DDL Drop Foreign Key

Posted on 2004-11-24
8
Medium Priority
?
2,429 Views
Last Modified: 2013-12-09
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?  
0
Comment
Question by:DLancy
  • 3
  • 2
6 Comments
 
LVL 19

Accepted Solution

by:
NickUpson earned 500 total points
ID: 12673828
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
 
LVL 10

Expert Comment

by:kacor
ID: 12673916
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
 
LVL 10

Expert Comment

by:kacor
ID: 12673985
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 2

Author Comment

by:DLancy
ID: 12689176
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
 
LVL 19

Expert Comment

by:NickUpson
ID: 12689259
yes you can, just redirect the output of the isql command, "isql .... > my_file"
0
 
LVL 10

Expert Comment

by:kacor
ID: 12698243
Sorry DLancy, I overlooked this phrase

Janos
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What we learned in Webroot's webinar on multi-vector protection.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question