Given a table name and a column name, I'm trying to dynamically drop an Oracle constraint that I don't know the name of ahead of time.
I can find the constraint name with this query:
SELECT CONSTRAINT_NAME
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = 'MyTable' AND
COLUMN_NAME='MyColumn' AND POSITION IS NULL
My first thought was to use a subquery, but that doesn't work and results in an ORA-02250 error:
ALTER TABLE MyTable
DROP CONSTRAINT (
SELECT CONSTRAINT_NAME
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = 'MyTable' AND
COLUMN_NAME='MyColumn' AND POSITION IS NULL)
So far, the only working solution I have is the following, but it feels unnecessarily complex:
DECLARE
statement VARCHAR2(2000);
constr_name VARCHAR2(30);
BEGIN
SELECT CONSTRAINT_NAME INTO constr_name
FROM USER_CONS_COLUMNS
WHERE table_name = 'MyTable' AND
column_name = 'MyColumn' AND position is null;
statement := 'ALTER TABLE MyTable DROP CONSTRAINT '|| constr_name;
EXECUTE IMMEDIATE(statement);
END;
/
Is there a way to do this with a subquery, as I originally intended? If not, can anyone suggest a more concise way to do this?