DLancy
asked on
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
SLECT * from RDB$RELATION_CONSTRAINTS
The first column is the RDB$CONSTRAINTS_NAME and contains what you are looking for.
Janos
ASKER
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.
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.
yes you can, just redirect the output of the isql command, "isql .... > my_file"
Sorry DLancy, I overlooked this phrase
Janos
Janos
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