At 02:18 AM 14/06/2010, Cornel Rebegea wrote:

>Hi,
>
>Is there a way to check if a unique contraint is defined on a table?
>
>This is the way I defined it
>
>ALTER TABLE BON_ANTET ADD CONSTRAINT UNQ_BON_ANTET UNIQUE (BON_ID);
>
>I need to a way to know if the constraint exists, and if not then just create it.

It depends on where and how you need to do this. In a regular DDL request, your app needs no more than to handle the exception unique_key_violation on the system table RDB$RELATION_CONSTRAINTS that would occur if there is already 'UNQ_BON_ANTET' in the column RDB$CONSTRAINT_NAME.

Another way could be to issue the query

select 1 from RDB$RELATION_CONSTRAINTS
where RDB$CONSTRAINT_NAME = 'UNQ_BON_ANTET'

and test the result. If you don't get a '1' then you can proceed with the DDL request, scripted or whatever.

Another way (which I don't recommend) is to do similarly inside a PSQL module or block and proceed to execute the DDL in an EXECUTE STATEMENT construct if the constraint doesn't exist, viz.,

if (not exists (select 1 from RDB$RELATION_CONSTRAINTS
where RDB$CONSTRAINT_NAME = 'UNQ_BON_ANTET')) then
begin
....
end

./heLen