How to check whether a unique constraint is defined

Cornel Rebegea wrote:

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.

Helen Borrie answers:

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