At 07:52 AM 17/08/2004 +1000, Fred Gordon wrote:

> > I would guess this is a newbie question.....
> >
> > ALTER TABLE cusmas ADD CONSTRAINT FK_CUSTOMER_SALESMAN_SALESMAN2 FOREIGN
> > KEY (c_salem2) REFERENCES smnmas (sm_sid)
> >
> >
> > I am getting this error:
> > Error: Resource Exception. unsuccessful metadata update
> > object SMNMAS is in use
> > Reason: unsuccessful metadata update
> > object SMNMAS is in use
> >
> > I shut the engine down and restarted it to be sure I was not connected
> > to the db with another app and somehow locking the SMNMAS table.
> >
> > By the way, I know my table and field names stink.... I am porting an
> > app that is 20 years old!!! It was originally written using ISAM files.
> >
> > What am I doing wrong?
> >
> > Thanks,
> > Fred.

Alan McDonald wrote:

>BTW - watch the long constraint name
>FK_CUSTOMER_SALESMAN_SALESMAN2
>it's 30 you only have 32 to play with - what charset is your db?

The charset of the db doesn't affect the limits on the lengths of
identifiers. Identifiers are unicode, a restricted "universal" subset of
characters that applies no matter want charset the db uses to store data.

Long identifiers, esp. on column names, can be a problem with some
connectivity layers, that need to apply prefixes or suffixes to parameter
names on the client side; but the engine happily accepts identifiers up to
31 (not 32) characters.

To eliminate your "Object in use" problem, make it a general rule to avoid
adding and removing constraints when the database is in use. Dependencies
can have a wide reach. If there are multiple users, perform a database
shutdown using gfix before you set out to do these jobs. A server shutdown
shouldn't be necessary.

If you are sure you are alone on the system, then complete, unprepare and
close any queries that are running over any of the tables affected by the
constraint and commit any outstanding transactions before you
begin. Alternatively, just log out of the tool you're using and log in
again, and don't open any "data browser" that shows you the data or
metadata of the affected tables.

And, of course, using multiple tools simultaneously when you want to do
this stuff will raise the likelihood that the object is in use.

/heLen