disabling all the constraints on a table.

[jayjaybigs]

Hello All,

I have several constraints on table. I intend to load big data into the table. However I wanted to disable all the costraints at once like the following:

ALTER TABLE STATE
DISABLE CONSTRAINT ALL;

OR

ALTER TABLE STATE
ENABLE CONSTRAINT ALL;

Could anyone advice if this is feasable.

 

[SantaMufasa]

JayJay,

Although Oracle's SQL does not support the commands you propose, you can accomplish the same results with the following scripts. You must save the code as scripts since they contain "accept" statements. I called the scripts "constraints_off.sql" and "constraints_on.sql".

Section 1 -- Confirmation of the constraints and their states for the s_emp table:

SQL> @stru
Enter table name: s_emp

Col Column             Data Type     [Constraint Type: Name: En-/Dis-abled]
 #  Name               and Length    and Enforcement
--- ------------------ ------------- -------------------------------------------
  1 ID                 NUMBER        [CK:S_EMP_ID_NN:[b]ENABLED[/b]] "ID" IS NOT NULL
                                     [PK:S_EMP_ID_PK:[b]ENABLED[/b]] NOT NULL/UNIQUE
  2 LAST_NAME          VARCHAR2(25)  [CK:S_EMP_LAST_NAME_NN:[b]ENABLED[/b]] "LAST_NAME"
                                     IS NOT NULL

  3 FIRST_NAME         VARCHAR2(25)
  4 USERID             VARCHAR2(8)   [UK:S_EMP_USERID_UK:[b]ENABLED[/b]] UNIQUE
  5 START_DATE         DATE
  6 COMMENTS           VARCHAR2(255)
  7 MANAGER_ID         NUMBER        [FK:S_EMP_MANAGER_ID_FK:[b]ENABLED[/b]] Match :
                                     S_EMP_ID_PK

  8 TITLE              VARCHAR2(25)  [FK:S_EMP_TITLE_FK:[b]ENABLED[/b]] Match :
                                     S_TITLE_TITLE_PK

  9 DEPT_ID            NUMBER        [FK:S_EMP_DEPT_ID_FK:[b]ENABLED[/b]] Match :
                                     S_DEPT_ID_PK

 10 SALARY             NUMBER
 11 COMMISSION_PCT     NUMBER        [CK:S_EMP_COMMISSION_PCT_CK:[b]ENABLED[/b]]
                                     commission_pct IN (10, 12.5, 15, 17.5, 20)
********************************************************************************

Section 2 -- Script to disable constraints:

accept tab prompt "Enter the name of the table to disable its constraints: "
set serveroutput on
declare
    sql_stm    varchar2(2000);
begin
    dbms_output.enable(1000000);
    for x in (select constraint_name
                    ,decode(constraint_type,'P',' cascade') casc
               from user_constraints
               where table_name = upper('&tab')) loop
        sql_stm := 'alter table &tab disable constraint '||
            x.constraint_name||x.casc;
        dbms_output.put_line(sql_stm);
        execute immediate sql_stm;
    end loop;
end;
/

Section 3 -- Invocation of constraint-disabling script:

SQL> @constraints_off
Enter the name of the table to disable its constraints: s_emp
alter table s_emp disable constraint S_EMP_ID_NN
alter table s_emp disable constraint S_EMP_LAST_NAME_NN
alter table s_emp disable constraint S_EMP_COMMISSION_PCT_CK
alter table s_emp disable constraint S_EMP_ID_PK cascade
alter table s_emp disable constraint S_EMP_USERID_UK
alter table s_emp disable constraint S_EMP_MANAGER_ID_FK
alter table s_emp disable constraint S_EMP_DEPT_ID_FK
alter table s_emp disable constraint S_EMP_TITLE_FK

PL/SQL procedure successfully completed.

Section 4 -- Confirmation of disabled constraints:

SQL> @stru
Enter table name: s_emp

Col Column             Data Type     [Constraint Type: Name: En-/Dis-abled]
 #  Name               and Length    and Enforcement
--- ------------------ ------------- ------------------------------------------
  1 ID                 NUMBER        [CK:S_EMP_ID_NN:[b]DISABLED[/b]] "ID" IS NOT NULL
                                     [PK:S_EMP_ID_PK:[b]DISABLED[/b]] NOT NULL/UNIQUE
  2 LAST_NAME          VARCHAR2(25)  [CK:S_EMP_LAST_NAME_NN:[b]DISABLED[/b]]
                                     "LAST_NAME" IS NOT NULL

  3 FIRST_NAME         VARCHAR2(25)
  4 USERID             VARCHAR2(8)   [UK:S_EMP_USERID_UK:[b]DISABLED[/b]] UNIQUE
  5 START_DATE         DATE
  6 COMMENTS           VARCHAR2(255)
  7 MANAGER_ID         NUMBER        [FK:S_EMP_MANAGER_ID_FK:[b]DISABLED[/b]] Match :
                                     S_EMP_ID_PK

  8 TITLE              VARCHAR2(25)  [FK:S_EMP_TITLE_FK:[b]DISABLED[/b]] Match :
                                     S_TITLE_TITLE_PK

  9 DEPT_ID            NUMBER        [FK:S_EMP_DEPT_ID_FK:[b]DISABLED[/b]] Match :
                                     S_DEPT_ID_PK

 10 SALARY             NUMBER
 11 COMMISSION_PCT     NUMBER        [CK:S_EMP_COMMISSION_PCT_CK:[b]DISABLED[/b]]
                                     commission_pct IN (10, 12.5, 15, 17.5, 20)

Section 5 -- Script to enable constraints:

accept tab prompt "Enter the name of the table to enable its constraints: "
set serveroutput on
declare
    sql_stm    varchar2(2000);
begin
    dbms_output.enable(1000000);
    for x in (select constraint_name
               from user_constraints
               where table_name = upper('&tab')) loop
        sql_stm := 'alter table &tab enable constraint '||
            x.constraint_name;
        dbms_output.put_line(sql_stm);
        execute immediate sql_stm;
    end loop;
end;
/

Section 6 -- Invocation of constraint-enabling script:

SQL> @constraints_on
Enter the name of the table to enable its constraints: s_emp
alter table s_emp enable constraint S_EMP_ID_NN
alter table s_emp enable constraint S_EMP_LAST_NAME_NN
alter table s_emp enable constraint S_EMP_COMMISSION_PCT_CK
alter table s_emp enable constraint S_EMP_ID_PK
alter table s_emp enable constraint S_EMP_USERID_UK
alter table s_emp enable constraint S_EMP_MANAGER_ID_FK
alter table s_emp enable constraint S_EMP_DEPT_ID_FK
alter table s_emp enable constraint S_EMP_TITLE_FK

PL/SQL procedure successfully completed.

Section 7 -- Confirmation of disabled constraints:

SQL> @stru
Enter table name: s_emp

Col Column             Data Type     [Constraint Type: Name: En-/Dis-abled]
 #  Name               and Length    and Enforcement
--- ------------------ ------------- -------------------------------------------
  1 ID                 NUMBER        [CK:S_EMP_ID_NN:[b]ENABLED[/b]] "ID" IS NOT NULL
                                     [PK:S_EMP_ID_PK:[b]ENABLED[/b]] NOT NULL/UNIQUE
  2 LAST_NAME          VARCHAR2(25)  [CK:S_EMP_LAST_NAME_NN:[b]ENABLED[/b]] "LAST_NAME"
                                     IS NOT NULL

  3 FIRST_NAME         VARCHAR2(25)
  4 USERID             VARCHAR2(8)   [UK:S_EMP_USERID_UK:[b]ENABLED[/b]] UNIQUE
  5 START_DATE         DATE
  6 COMMENTS           VARCHAR2(255)
  7 MANAGER_ID         NUMBER        [FK:S_EMP_MANAGER_ID_FK:[b]ENABLED[/b]] Match :
                                     S_EMP_ID_PK

  8 TITLE              VARCHAR2(25)  [FK:S_EMP_TITLE_FK:[b]ENABLED[/b]] Match :
                                     S_TITLE_TITLE_PK

  9 DEPT_ID            NUMBER        [FK:S_EMP_DEPT_ID_FK:[b]ENABLED[/b]] Match :
                                     S_DEPT_ID_PK

 10 SALARY             NUMBER
 11 COMMISSION_PCT     NUMBER        [CK:S_EMP_COMMISSION_PCT_CK:[b]ENABLED[/b]]
                                     commission_pct IN (10, 12.5, 15, 17.5, 20)

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Thargy]

Mufasa, JayJay,

W.R.T. your posting, I was wondering what you would advise about the whole idea of binning the constraints before a data load.

This presupposes that all the data is clean, otherwise the post-load enabling of constraints will fail. Then JayJay will have hours of endless fun querying for all the dirty data. In his big data table (sic) this might be a significant task.

Am I missing something here?

Regards

Tharg

 

[SantaMufasa]

No, Tharg, you are not missing anything...Constraints are in place to enforce business/integrity rules. If one disables them, then one runs the risk that they will end up with cruddy data that need fixing.

When JayJay asks for a method to disable constraints, his request comes with an implied warranty that he either knows what he is doing or he is willing to suffer the consequences of his actions.

I, on the otherhand, suffer another dilemma: if I refuse to help provide a solution on the grounds that the solution allows one to shoot one's self in the foot, (i.e., the solution is "bad form" for whatever reason), then I become a de facto "Relational Snob". So, one we go, walking the tightrope between "Providing Solutions for the People" and "Relational Snobbery".

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Thargy]

Mufasa,

thanks for the clarification. I thought I had lost the plot for a moment there.

I have gained a new appreciation of the invidious position in which you find yourself. Thanks for taking the time to explain to a newbie.

Regards

T

 

[jayjaybigs]

Thanks Mufasa,

I am aware of the need to safeguard table integrity by loading the table while the constraints are enabled.

My intention here is to do the following:

1. disable constraints in table test
2. Truncate the test table.
3. enable constraints in table test
4. insert records into table test

There is need for step 1 and 3 as step 2 will fail because there is a child table, test_child, that is referencing table test as parent table.

I am also sure there are other methods of deleting the records as alternative to step 2 without getting an error and not doing steps 2 and 3.