Drop Unique Constraint on Table Column without Knowing the Constraint Name

Question

In Oracle 10g, how can I drop a unique constraint on a column without knowing the name of the constraint (e.g. a system generated name, which won't necessarily be the same across database instances)? Dropping and recreating the table isn't an option. Is it possible?

Answer 1

You can retrieve the constraint's name with:

SELECT CONSTRAINT_NAME
FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'xxx'
AND CONSTRAINT_TYPE = 'U'

You can for instance create a stored procedure that executes the previous sql, stores its result in a variable and uses this variable in ALTER TABLE DROP CONSTRAINT

EDIT: e.g.:

BEGIN
  FOR r IN (
    SELECT TABLE_NAME, CONSTRAINT_NAME
    FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'xxx'
    AND CONSTRAINT_TYPE = 'U') LOOP
    EXECUTE IMMEDIATE REPLACE(REPLACE(
      'ALTER TABLE #TABLE# DROP CONSTRAINT #CON#'
      ,'#TABLE#',r.TABLE_NAME)
      ,'#CON#',r.CONSTRAINT_NAME);
  END LOOP;
END;