Subject Re: object is in use when creating foreign key
Author Adam
To do certain types of DDL such as creating a foreign key constraint,
if a transaction has performed a query on that table, then the
constraint will fail.

Two possible ideas.

1. Your upgrade script may be causing it. Do a commit immediately
before the create table and see if that helps.

2. The table has been used by an active transaction. Considering the
table is even called journal, this is not all that unlikely.

The only safe way I have found to make sure DDL is successful is to
be the only user. You can "shut down" the database. This prevents
everyone other than SYSDBA and I think the Owner of the database from
connecting, but you can read up on that. But if you are confident
that you are the only user, then why not use the embedded engine to
connect to the database to run the upgrades?


--- In, "julien_ferraro"
<j.ferraro@n...> wrote:
> Hello,
> I encounter a problem with Firebird. I'm unable to create foreign
> I try to create the following table :
> CREATE TABLE GuideSaisieLigne
> (
> Guide DGuideSaisie NOT NULL,
> Journal DJournal,
> JournalModifiable BOOLEAN,
> Compte VARCHAR(12),
> CompteModifiable BOOLEAN,
> Libelle VARCHAR(250),
> LibelleModifiable BOOLEAN,
> TypeSelectionReference SMALLINT,
> ReferenceLigne SMALLINT,
> ReferenceTypeDoc SMALLINT,
> ReferenceModifiable BOOLEAN,
> Direction SMALLINT,
> MontantFormule VARCHAR(250),
> MontantModifiable BOOLEAN,
> CONSTRAINT pk_GuideSaisieLigne PRIMARY KEY(Guide, Id),
> CONSTRAINT fk_GuideSaisieLigne_Journal FOREIGN KEY(Journal)
> REFERENCES Journal(Id)
> )
> I get the following error message :
> unsuccessful metadata update
> object JOURNAL is in use
> This happens almost everytime I try to create a foreign key. I did
> backup restore just before, and I'm sure there is no other conecion
> the database. I'm using the last stable release of Firebird
> on WinXP Pro, the database is on the same computer as the server.
> What is strange is that If I execute my script on an empty
> everything goes well.
> I first thought my database was corrupted. But that happens on all
> production databases (10 or so) so I don't think it's the case.
> Any idea ?
> Julien