Member Avatar for drako900

Is there a way to drop a PK from a table by selecting the name into a variable from all_constraints, I need to run this script on multi databases that the actual name of the PK could possibly be different.

I can get the name in a variable but don't know how to execute,
since an ALTER TALBE is not valid in the execute section.


DECLARE
PKNAME CHAR(50);
CMD CHAR(300);

BEGIN
SELECT CONSTRAINT_NAME INTO PKNAME FROM ALL_CONSTRAINTS WHERE TABLE_NAME = 'APQUICKD' AND CONSTRAINT_TYPE LIKE 'U';

----can I build and execute a string here????

END;

  • 2 Contributors
  • 2 Replies
  • 187 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by drako900

Dani AI

Generated

Good call from — use dynamic SQL. For a portable script that runs across schemas/instances, query the data dictionary (USER_CONSTRAINTS / ALL_CONSTRAINTS) for the primary-key constraint name (use CONSTRAINT_TYPE = 'P') and build an ALTER TABLE ... DROP CONSTRAINT ... string to run with EXECUTE IMMEDIATE. Note that Oracle stores unquoted identifiers in uppercase, so use UPPER(table_name) or pass uppercase names. (docs.oracle.com)

A compact, safe pattern (adapt as needed) looks like this:

DECLARE
  v_owner VARCHAR2(30);
  v_table VARCHAR2(30) := 'APQUICKD';
  v_pk    VARCHAR2(128);
  v_sql   VARCHAR2(4000);
BEGIN
  SELECT owner, constraint_name
    INTO v_owner, v_pk
    FROM all_constraints
   WHERE table_name = UPPER(v_table)
     AND constraint_type = 'P'
     AND rownum = 1;

  v_sql := 'ALTER TABLE ' || v_owner || '.' || v_table ||
           ' DROP CONSTRAINT ' || v_pk || ' DROP INDEX';

  EXECUTE IMMEDIATE v_sql;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    NULL; -- nothing to drop
  WHEN OTHERS THEN
    RAISE;
END;

When dropping a PK you can control whether Oracle also removes the supporting index (KEEP INDEX / DROP INDEX) or remove dependent constraints together with CASCADE; use those clauses deliberately and test them first. (docs.oracle.com)

Watchouts: you cannot drop a primary key while other tables have foreign-key references (you’ll get ORA-02449), so either drop or script the child FKs first (or use cascade behavior with extreme caution). Also remember DDL is not transactional in Oracle — ALTER TABLE (DDL) issues implicit commits, so changes are permanent. Test on a copy and log actions when running across multiple databases. (docs.oracle.com)

As noted, moving from T‑SQL style scripts to PL/SQL requires these small pattern changes (dynamic SQL + dictionary lookups). The above handles the variable constraint name, includes basic exception handling, and points out PK vs unique (P vs U) and index/foreign-key implications.

Recommended Answers

All 2 Replies

Member Avatar for Kate Albany

Look into the 'execute immediate' command. You can use it to drop your constraint using the value in PKNAME.

Member Avatar for drako900

Thanks for the suggestion, I was able figure it out.
Not much fun converting MS SQL scripts to PL/SQL scripts,

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.