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.
-->