[All]
Creating Foreign Keys With the ALTER TABLE Command
Abstract: Creating Foreign Keys With the ALTER TABLE Command
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.