CODEWITHSUNDEEP
oracle-databaseplsqlconstraints
Polina
Polina

Reputation: 41

pl/sql can't modify constraint defferable

I want to make constraint deferrable so I've wrote this code:

alter table life_cycle_phases modify constraint SOME_T_NAME_UNIQUE INITIALLY DEFERRED DEFERRABLE;

but orace returns mistake:

00933. 00000 -  "SQL command not properly ended"

what am i doing wrong?

UPDATE: ok, i see, once i've created not deferrable constraint, i can't check it's state, but i need to! my problem is: i need to disable all constraints, add row in table,which was random chosen(in fact i don't know in which table the row will be inserted) then enable all constraints.enable novalidate doesn't work, it validates rows, deferrable also doesn't work, what should do?

Upvotes: 1

Views: 3211

Answers (1)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

In general, MODIFY constraint is to just change the state of it. For instance, enable it, disable it..

From docs.. You cannot change the state of a NOT DEFERRABLE constraint to INITIALLY DEFERRED.

Restrictions on Modifying Constraints Modifying constraints is subject to the following restrictions:

•You cannot change the state of a NOT DEFERRABLE constraint to INITIALLY DEFERRED.

•If you specify this clause for an index-organized table, then you cannot specify any other clauses in the same statement.

•You cannot change the NOT NULL constraint on a foreign key column of a reference-partitioned table, and you cannot change the state of a partitioning referential constraint of a reference-partitioned table.

So, drop the constraint first and recreate it. You may use the below to generate the DDL of the constraint.

--For referential integrity constraints.
DBMS_METADATA.GET_DDL('REF_CONSTRAINT',CONSTRAINT_NAME,OWNER)
--For other kinds of constraints.
DBMS_METADATA.GET_DDL('CONSTRAINT',CONSTRAINT_NAME,OWNER)

Or try this.. ( Courtesy : https://gist.github.com/sdeming/869717 ...I haven't tested!!)

select 'alter table ' || source_table || ' add constraint ' || constraint_name || ' foreign key (' || con_columns || ') references ' || target_table || ' (' || ind_columns || ') enable' data 
  from (select constraint_name, source_table, target_index, target_table, con_columns, wm_concat(column_name) ind_columns
          from (select a.constraint_name, a.source_table, a.target_index, b.table_name target_table, a.con_columns, b.column_name, b.column_position
                  from (select a.constraint_name, a.source_table, a.target_index, wm_concat(a.column_name) con_columns
                          from (select a.constraint_name, 
                                       a.table_name source_table, 
                                       a.r_constraint_name target_index, 
                                       b.column_name,
                                       b.position
                                  from user_constraints a
                                 inner join user_cons_columns b on (b.constraint_name = a.constraint_name)
                                 where a.constraint_type = 'R'
                                   and a.constraint_name = 'LIFE_CYCLE_PHASES_NAME_UNIQUE'
                                 order by a.constraint_name, b.position) a
                        group by constraint_name, source_table, target_index) a
                  inner join user_ind_columns b on (b.index_name = a.target_index)
                  order by constraint_name, b.column_position) 
          group by constraint_name, source_table, target_index, target_table, con_columns);

And then DROP it,

alter table OWNER.life_cycle_phases drop constraint LIFE_CYCLE_PHASES_NAME_UNIQUE ;

Finally recreate the constraint only using the generated DDL.

Upvotes: 3

Related Questions