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