Page 1 of 1

How to check if constraint exists before adding

Posted: 10.08.2008, 14:50
by kerino
Hi, I'm using Zeos and Firebird.
I create a table with a constraint e.g.
'ALTER TABLE Table1 ADD CONSTRAINT FK_TEST1 FOREIGN KEY (Field1) REFERENCES Table2(Field2)'

When I run the program again, the constraint already exists so I get an error. How can I change the statement to avoid the error please? (I'm rather new to all this)

Many thanks
Kerin

Posted: 10.08.2008, 18:44
by mdaems
You can do a Query against the system tables to check if the constraint exists.
Alternatively, you could use TZSQLMetaData Component to get the constraints that are present in the database. You'll have to check how a foreign key constraint shows up there as I don't know that myself, however. Set Metadatatype property to mdImportedKeys , mdExportedKeys or mdCrossReference. One of these might reveal the existing constraint names so you could check.

Mark

Posted: 11.08.2008, 11:06
by kerino
Thanks Mark,
I am currently querying the RDB$REF_CONSTRAINTS table for the RDB$CONSTRAINT_NAME tosee if it exists.
I just thought there might be a more elegant way to do it in one SQL statement (using IF EXISTS or something).
Anyway, what I have seems to work, although it's not ideal.
I haven't used the TZSQLMetaData component, so I'll have a look at it.

Thanks again.