Dear Hellen,

i have try your query but i get this error:

Unsuccessful metadata update
object M_ROLES is in use
Statement: ALTER TABLE M_ROLES
ADD CONSTRAINT M_ROLES_FK FOREIGN KEY (PARENT_ROLE_ID) REFERENCES
M_ROLES(ROLE_ID)

i have used ibconsole and ibaccess but i still get the same error.

Thanks and Regards,
Benny Setiawan T



-----Original Message-----
From: Helen Borrie [mailto:helebor@...]
Sent: Thursday, May 23, 2002 5:24 PM
To: ib-support@yahoogroups.com
Subject: Re: [ib-support] unsucessful metadata


At 02:14 PM 23-05-02 +0800, you wrote:

>Dear All,
>
>i have problem
>i use this query :
>CREATE TABLE M_ROLES(ROLE_ID INTEGER NOT NULL PRIMARY KEY,
> STATUS INTEGER,
> NAME VARCHAR(64),
> CREATED DATE,
> CLOSED DATE,
> NOTE VARCHAR(128),
> PARENT_ROLE_ID INTEGER ,
> FOREIGN KEY (PARENT_ROLE_ID) REFERENCES
>M_ROLES(ROLE_ID)
>);
>
>but error :
>unsucessful metadata object M_ROLES is in use
>
>does anyone knows about this error and why..??

Yes, and yes. At the time you are attempting to create the foreign key
constraint, the table is in use - because you have created it but not yet
committed it; and it needs to exist in order for the self-referencing
constraint to be created.

With self-referencing relations, you always need to take that little bit of
extra care to ensure that dependencies can be met. Create the object in
one step and the constraints one step at a time afterwards.

CREATE TABLE M_ROLES(ROLE_ID INTEGER NOT NULL,
STATUS INTEGER,
NAME VARCHAR(64),
CREATED DATE,
CLOSED DATE,
NOTE VARCHAR(128),
PARENT_ROLE_ID INTEGER)
commit;
ALTER TABLE M_ROLES
ADD CONSTRAINT M_ROLES_PK PRIMARY KEY(ROLE_ID);
commit;
ALTER TABLE M_ROLES
ADD CONSTRAINT M_ROLES_FK FOREIGN KEY (PARENT_ROLE_ID) REFERENCES
M_ROLES(ROLE_ID) ;
commit;

cheers,
Helen