Given a table title along with a column title, I am attempting to dynamically drop an Oracle constraint that I'm not sure the title of in advance.
I'm able to discover the constraint title with this particular 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 utilize a subquery, but that does not work to cause 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)
To date, the only real working solution I've may be the following, however 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; /
It is possible to method of doing this having a subquery, when i initially intended? Otherwise, can anybody advise a more concise method of doing this?
You can't. SQL and DDL are essentially two separated languages. Your option would be correct.