| Subject | Re: [ib-support] sql error code -607 |
|---|---|
| Author | Helen Borrie |
| Post date | 2002-01-21T00:32:52Z |
At 10:22 PM 20-01-02 +0100, you wrote:
At worst, it could mean you have been caught in a dependency trap. It looks as if you have a foreign key that depends on the primary key's uniqueness. If you don't have a foreign key, then the dependency is between the pri key and the index on the same table.
It's an InterBase wart that has caught many, teaching us the hard way that primary key and foreign key constraints create their own indexes and that duplicate indexes are to be avoided.
AFAIK, the only robust way to unravel this is to drop the table and start again. If a foreign key is involved, you'll have to drop that first. This will be quite time-consuming if you have data that you can't regenerate, as you will have to create some temporary plain vanilla tables in which to store the data whilst you are dropping and recreating.
Some people fiddle with the system tables when they want to make magic fixes to this problem....some people don't believe in life insurance, either.
All for Open and Open for All
Firebird Open SQL Database ยท http://firebirdsql.org
_______________________________________________________
>I have a lookup table which should have the primary key only.At best, it could mean that you currently have the browser open, which means the table is involved in a transaction and is therefore in use. If that's the case, close the browser, check the Transaction page to ensure that you have no other open transactions, then try again.
>
>I have created a 2nd index by mistake and want to delete it.
>
>When I write
>
>drop index TB_MEZ_CODMEZ, I get the error message
>
>"statement failed, sql error code -607"
>
>with the further explanation:
>
>"unsuccessful metadata update: object index is in use"
>
>This is weird, as nobody is using the index.
>
> From IB_SQL, in the page Depend, I can see the following:
>
>Dependent Name Field Name Dependent Type Depended on Type
>Check_57 COD_MEZ 2 0
>Check_57 2 0
>Check_58 COD_MEZ 2 0
>Check_58 2 0
>
>
>What does it mean ?
At worst, it could mean you have been caught in a dependency trap. It looks as if you have a foreign key that depends on the primary key's uniqueness. If you don't have a foreign key, then the dependency is between the pri key and the index on the same table.
It's an InterBase wart that has caught many, teaching us the hard way that primary key and foreign key constraints create their own indexes and that duplicate indexes are to be avoided.
AFAIK, the only robust way to unravel this is to drop the table and start again. If a foreign key is involved, you'll have to drop that first. This will be quite time-consuming if you have data that you can't regenerate, as you will have to create some temporary plain vanilla tables in which to store the data whilst you are dropping and recreating.
Some people fiddle with the system tables when they want to make magic fixes to this problem....some people don't believe in life insurance, either.
All for Open and Open for All
Firebird Open SQL Database ยท http://firebirdsql.org
_______________________________________________________