Delete a unique constant in a table column without knowing the name of the constraint

Question

In Oracle 10g, how can I remove a unique constraint for a column without knowing the constraint name (for example, a system-generated name that will not necessarily be the same for database instances)? Dropping and re-creating the table is not an option. Is it possible?

Answer 1

You can get the name of the constraint with:

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

You can, for example, 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: for example:

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;