Creating Foreign Keys With the ALTER TABLE Command

By: Borland Staff

Abstract: InterBase requires an exclusive lock on the database to create foreign key references
Problem:
Using the ALTER TABLE command to create a foreign key reference on a table in some 
cases can generate the error "object  is in use."

Solution:
InterBase requires an exclusive lock on the database (i.e only one connection to the database) to create foreign key references.

To create a foreign key reference have all users log off all of their connections to 
the database except for the one connection to be used to create the foreign key 
reference.  If there are some persistent connections this may require stopping and restarting InterBase.  Some sources of multiple connections that may not be obvious are:

- WISQL: The connection obtained in WISQL and the connection obtained 
  by running a script in WISQL are two separate connections.  Disconnect 
  from the database then run the script.

- Delphi and C++ Builder:  TDatabase, TQuery, TTable and TStoredProc 
  objects can connect to an InterBase database during design-time, and 
  during runtime.  These connections are separate connections.  Try 
  deactivating the design-time connections by setting the Active and 
  Connected properties to False and set them to True in the OnCreate 
  event of the form.  Also, if TQuery, TTable, and TStoredProc objects 
  do not connect through a TDatabase object they generate their own 
  separate connections.

After establishing a single connection issue the ALTER TABLE command in ISQL, 
Windows ISQL, Delphi, C++ Builder, or the appropriate host interface.

Published on: 7/24/2000 12:00:00 AM

Server Response from: ETNASC03

Copyright© 1994 - 2013 Embarcadero Technologies, Inc. All rights reserved.