How can I drop a unique constraint in a table which is already been created after checking if it exists or not? I would need to do this using one statement or even using a stored procedure.
Different approaches exist, depending on the details.
One trivial approach is suitable only when you know already the exact constraint name and table-schema+table-name, is to drop the constraint whilst ignoring the error if it does'nt exist (see example below).
Example of dropping a unique constraint , using compound-SQL that ignores the case when the constraint does not already exist. You can define other conditions also to ignore. This compound SQL can be executed directly at the CLP, or can be replicated inside a stored-procedure.
--#SET TERMINATOR @
begin
declare v_no_such_constraint integer default 0;
declare not_exists condition for sqlstate '42704';
declare continue handler for not_exists set v_no_such_constraint=1;
execute immediate('alter table user1.mytable drop constraint myuniqueconstraint') ;
end@
Note: as this is a table alteration, your code should check if the alteration puts the table into reorg-pending and then react accordingly. Additionally this assumes you also have tooling that will revalidate any objects (such as packages) that become invalid as a result of dropping this constraint.
When you don't know the constraint name, but only the table-name and constraint-type (e.g the impacted columns) then you can search the catalog views by joining syscat.tables, syscat.tabconst, syscat.checks and other views. See Knowledge Center for your version of Db2 for details.