Member Avatar for Behseini

Hi every body,
Can you please let me know how can I modify following SQL algorithm to Disable/Enable all constraints existing in an specific table?

alter table (table name) disable constraint (constraint name)

For example if I have a table called "GIS_Data" how I can disable all constraints of it in one line command

alter table GIS_Data disable constraint .....//.......(constraint name)
Edited by Behseini because: Typo
  • 3 Contributors
  • 2 Replies
  • 94 Views
  • 14 Hours Discussion Span
  • Latest Post Latest Post by debasisdas

Dani AI

Generated

— and are correct: Oracle does not provide a single built‑in command to disable every constraint on one table by name. The usual, reliable approach is to query Oracle's constraint metadata and either generate the ALTER statements or have a small PL/SQL loop execute them for you.

A minimal generator (run as the table owner or use ALL/DBA views if needed) will print the DDL you can review and run:

SELECT 'ALTER TABLE "'||table_name||'" DISABLE CONSTRAINT "'||constraint_name||'";'
FROM user_constraints
WHERE table_name = 'GIS_DATA' AND status = 'ENABLED';

To run changes immediately, use a PL/SQL loop that builds and EXECUTE IMMEDIATEs the statements (adjust to ALL_CONSTRAINTS/DBA_CONSTRAINTS and include owner if you are not the table owner):

BEGIN
  FOR r IN (SELECT table_name, constraint_name FROM user_constraints
            WHERE table_name = 'GIS_DATA' AND status = 'ENABLED')
  LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE "'||r.table_name||'" DISABLE CONSTRAINT "'||r.constraint_name||'"';
  END LOOP;
END;
/

Practical notes: use USER_CONSTRAINTS for the current schema, ALL_CONSTRAINTS/DBA_CONSTRAINTS if you need cross‑schema results (and have privileges). Check for dependent foreign keys using the R_CONSTRAINT_NAME/R_OWNER columns before mass disabling. Disabling constraints stops enforcement and can create data integrity gaps — plan how/when to re‑enable (you can choose ENABLE VALIDATE or ENABLE NOVALIDATE depending on whether you want Oracle to recheck existing rows). Also remember you need ALTER privilege on the target table(s). GUI tools like SQL Developer can disable constraints one-by-one if you prefer a visual workflow.

Recommended Answers

All 2 Replies

Member Avatar for Member #647493

I don't really think that you can.
I would write a script to create a second script which has all of your ALTER commands.
Then, run the second script.
SQL Developer may be able to, but...

Member Avatar for debasisdas

yes you need to create the commands using a script.

to conform, this can't be done in a single line command.

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.