| Subject | Re: [firebird-support] I can't drop foreign key |
|---|---|
| Author | 渡辺 |
| Post date | 2016-02-18T06:13:33Z |
Thank you !!! Mark !!!!!!
Resolved!
One procedure referenced the Index;
The procedure uses "Plan Sort(Hachu Index(FKHACHUTOPARTS))"
in Select SQL.
I create other Index HachuP on Hachu(partsid).
And I alter the procedure ,not to use plan the FKHACHUTOPARTS
to use plan with new Index HachuP.
I could drop the constraint.
Maybe I wrote the procedure over 10years ago,
and I forgot that the procedure used PLAN with Index.
Hachu table has PARTSID column,
and Hachu table has many column,
then I didn't wrote all column.
And I forgot the important column "PARTSID",sorry.
I thank for your advice very much.
Nobuo Watanabe
On 2016/02/17 17:12, Mark Rotteveel mark@... [firebird-support] wrote:
On 16-2-2016 22:05, watanove@... [firebird-support] wrote:
> I am using FireBird 2.5.3
> and a Database is updated from 2.2, and the database was updated from 1.5.
>
> The database have about 150 tables and just one foreign key.
>
> The Foreign key is from "hachu"Table to "parts"Table
>
> This is part of definition of the tables.
> ISQL display these informations.
>
> SQL> show table parts;
> OPERATIONCODE VARCHAR(100) Nullable
> PARTSID INTEGER Not Null
> PRODUCTCODE VARCHAR(100) Nullable
> CONSTRAINT INTEG_152:
> Primary key (PARTSID)
>
> SQL> show table hachu;
> HACHUDATE &n bsp; DATE Nullable
> HACHUID INTEGER Not Null
> HACHUNO VARCHAR(10) Nullable
> HSU INTEGER Nullable
> CONSTRAINT FKHACHUTOPARTS:
> Foreign key (PARTSID) References PARTS (PARTSID) On Delete Set Null
Interesting that this table doesn't appear to have a column PARTSID, so
this constraint shouldn't be able to exist.
> I tried to drop the foreign key,but I could not.
> SQL> alter table hachu drop constraint fkhachutoparts;
>
> Statement failed, SQLSTATE = 42000
> unsuccessful metadata update
> -cannot delete*-INDEX FKHACHUTOPARTS
> -there are 1 dependencies
>
> I deleted all records of "parts" and "hachu".
> I deleted all triggers of "parts" and "hachu".
>
> But "there are 1 dependencies"
Do you have stored procedures referencing this table?
> I can't also drop these tables.
>
> SQL> drop table hachu;
> Statement failed, SQLSTATE = 42000
> unsuccessful metadata update
> -cannot delete
> -INDEX FKHACHUTOPARTS
> -there are 1 dependencies
>
> SQL> drop table parts;
> Statement failed, SQLSTATE = 42000
> unsuccessful metadata update
> -ERASE RDB$RELATION_CONSTRAINTS failed
> -action cancelled by trigger (1) to preserve data integrity
> -Cannot delete PRIMARY KEY being used in FOREIGN KEY definition.
>
> I don't understand why i can't drop the foreign key.
> But i want to drop the foreign key.
Could you run gifx -v -full -n <database-name> on your database and
report the output?
If all else fails, you could also consider creating a new database, and
pumping over the data.
Mark
--
Mark Rotteveel
-- /////ﾉﾍﾞﾙｯﾄ/////////////////////////////////////////////// 株式会社 ノベルット ソフトウェア インダストリー 代表取締役 渡辺 伸雄 〒144-0043 東京都 大田区 羽田２－１２－３ Tel:03-5705-2595 Fax:03-6423-9505 ＦＡＸ番号が変わりました！ mobile-phone:080-3430-2595 070-5582-6540 Email:watanove@... ///////////////////////////////////////////////ﾉﾍﾞﾙｯﾄ/////