Skip to Main Content
  • Questions
  • ENABLE NOVALIDATE validating existing data

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Sajid.

Asked: March 25, 2003 - 7:52 am UTC

Answered by: Tom Kyte - Last updated: October 10, 2012 - 4:42 pm UTC

Category: Database - Version: 9.0.2

Viewed 100K+ times! This question is

You Asked

Hi Tom,

Simple question about CONSTRAINT STATE for you this time.
This is what i am doing:


SQL> create table t (a number, constraint uniq_a UNIQUE(a));

Table created.

SQL> alter table t disable constraint uniq_a;

Table altered.

SQL> insert into t values(&a);
Enter value for a: 1
old 1: insert into t values(&a)
new 1: insert into t values(1)

1 row created.

SQL> /
Enter value for a: 1
old 1: insert into t values(&a)
new 1: insert into t values(1)

1 row created.

SQL> /
Enter value for a: 2
old 1: insert into t values(&a)
new 1: insert into t values(2)

1 row created.

SQL> select * from t;

A
----------
1
1
2


SQL> alter table t enable novalidate constraint uniq_a;
alter table t enable novalidate constraint uniq_a
*
ERROR at line 1:
ORA-02299: cannot validate (TUSKERDEV.UNIQ_A) - duplicate keys found

Here is what Oracle 9i (Release 2) Document says :

Constraint States

You can enable or disable integrity constraints at the table level using the
CREATE TABLE or ALTER TABLE statement. You can also set constraints to VALIDATE
or NOVALIDATE, in any combination with ENABLE or DISABLE, where:

ENABLE ensures that all incoming data conforms to the constraint
DISABLE allows incoming data, regardless of whether it conforms to the constraint
VALIDATE ensures that existing data conforms to the constraint
NOVALIDATE means that some existing data may not conform to the constraint

In addition:

- ENABLE VALIDATE is the same as ENABLE. The constraint is checked and is guaranteed
to hold for all rows.
- ENABLE NOVALIDATE means that the constraint is checked, but it does not have to be
true for all rows. This allows existing rows to violate the constraint, while ensuring
that all new or modified rows are valid.

In an ALTER TABLE statement, ENABLE NOVALIDATE resumes constraint checking on
disabled constraints without first validating all data in the table.

DISABLE NOVALIDATE is the same as DISABLE.
The constraint is not checked and is not necessarily true.

DISABLE VALIDATE disables the constraint, drops the index on the constraint,
and disallows any modification of the constrained columns.


According to Oracle
"ENABLE NOVALIDATE means that the constraint is checked, but it does not have to be
true for all rows. This allows existing rows to violate the constraint, while ensuring
that all new or modified rows are valid."

Why am i getting error then?


Thanks in advance for your help.

Regards,
Sajid Anwar
London

and we said...

By default a non-deferrable UNIQUE or PRIMARY KEY constraint will attempt to create a unqiue index. That is what is happening here.

You have a choice

a) create the constraint deferrable. This will create a non-unique constraint. When you disable the constraint, you have a nice side effect that the INDEX stays in place.


ps$tkyte@ORA920> create table t (a number, constraint uniq_a UNIQUE(a) DEFERRABLE );
Table created.

ops$tkyte@ORA920> select index_name from user_indexes where table_name = 'T';

INDEX_NAME
------------------------------
UNIQ_A

ops$tkyte@ORA920> alter table t disable constraint uniq_a;
Table altered.

ops$tkyte@ORA920> select index_name from user_indexes where table_name = 'T';

INDEX_NAME
------------------------------
UNIQ_A

ops$tkyte@ORA920> insert into t values ( 1 );
1 row created.

ops$tkyte@ORA920> insert into t values ( 1 );
1 row created.

ops$tkyte@ORA920> alter table t enable novalidate constraint uniq_a;
Table altered.


Or, you can create the constraint as normal, lose the index (which may be equally as desirable as keeping it depending on your circumstances of course) and create a NON-Unique index manually:


ops$tkyte@ORA920> create table t (a number, constraint uniq_a UNIQUE(a));
Table created.

ops$tkyte@ORA920> select index_name from user_indexes where table_name = 'T';

INDEX_NAME
------------------------------
UNIQ_A

ops$tkyte@ORA920> alter table t disable constraint uniq_a;
Table altered.

ops$tkyte@ORA920> select index_name from user_indexes where table_name = 'T';
no rows selected

ops$tkyte@ORA920> insert into t values ( 1 );
1 row created.

ops$tkyte@ORA920> insert into t values ( 1 );
1 row created.

ops$tkyte@ORA920> alter table t enable novalidate constraint uniq_a;
alter table t enable novalidate constraint uniq_a
*
ERROR at line 1:
ORA-02299: cannot validate (OPS$TKYTE.UNIQ_A) - duplicate keys found


ops$tkyte@ORA920> create index t_idx on t(a);
Index created.

ops$tkyte@ORA920> alter table t enable novalidate constraint uniq_a;
Table altered.




and you rated our response

  (36 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Thanks a million

March 25, 2003 - 10:59 am UTC

Reviewer: Sajid Anwar from London

You are THE STAR, Tom.
Brilliant answer and to the point.

Thanks once again.

Regards,
Sajid Anwar
London.

perfect

April 11, 2003 - 7:19 pm UTC

Reviewer: Dave from San Diego, CA

I think from here on out I'll be using asktom in the same way that I use google to find out how to fix my problems...type in the error message and see what happens. With google, you'll end up with oracle docs in russian that mention the command you're using. At asktom you get real life examples of what to do.

I've been thrust in to the role of pl/sql web programmer. So far Tom's advice has saved me time, reinforced things that I thought I was doing right but I wasn't sure, and reminded me that guessing isn't the same thing as experimentation.

Tom, you rock

Website...

June 27, 2003 - 2:38 pm UTC

Reviewer: kashif from Houston, TX

Tom,

Do you know of a website similar to yours for Java developers and Oracle Java developers? Thanks.

kashif

Tom Kyte

Followup  

June 27, 2003 - 2:54 pm UTC

</code> http://devtrends.oracle.com/ <code>



Does enable constraint validate?

August 13, 2004 - 1:29 pm UTC

Reviewer: Arun Gupta from Harrisburg, PA USA

Tom,
The Oracle 9i Database Administrator's Guide says "Enabling a constraint does not require validation." Is this statement correct? If I disable RI constraint, load orphaned data into child table and try to enable the RI, I always get an error saying that parent keys not found. If Oracle is not trying to validate on enable, then why I get this error?

The second question is we have a situation where because of changing business need, we need to create a FK constraint on one of the table columns. The column will also have a NOT NULL constraint. The existing data in this column does contain some NULL values. Is there some way to enforce NOT NULL only on future data but not on existing data?

Thanks

Tom Kyte

Followup  

August 13, 2004 - 6:02 pm UTC

see the original answer above?  i demonstrated this exact example....

you can 

ops$tkyte@ORA9IR2> create table t ( x int );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( NULL );
 
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter table t modify x not null enable novalidate;
 
Table altered.
 
ops$tkyte@ORA9IR2> insert into t values ( NULL );
insert into t values ( NULL )
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("OPS$TKYTE"."T"."X")
 
 
ops$tkyte@ORA9IR2> select decode(x,null,'Null','Not Null') from t;
 
DECODE(X
--------
Null
 


*caveat emptor* 

More questions

August 19, 2004 - 11:13 am UTC

Reviewer: A reader

1. Quick question, what is the reason for your "caveat emptor" in the earlier followup? Having some data obey the constraint and some data not obey it is the whole point of ENABLE NOVALIDATE, right?

"DISABLE VALIDATE disables the constraint, drops the index on the constraint, and disallows any modification of the constrained columns"

2. Can you please demonstrate what DISABLE VALIDATE means exactly? Under what circumstances can/should it be used?

3. I have a lookup table that is heavily used as the FK by lots of other tables i.e. dozens of tables have a FK pointing to this small lookup table.

I add a new row to this lookup table. It is not yet "used" by any of the child tables. Business requirements change and I need to UPDATE this lookup table.

To prevent the massive row-level locking that would happen if I just go ahead and update the lookup table, I disable and re-enable all the FK constraints.

Re-enabling takes a very long time because it tries to 'enable validate'

But because the constraint was 'enable validate' until now, I know that existing data is fine. No new data has been added that uses the new value I just added.

So I go ahead and 'enable novalidate' which is very fast.

Question:

Is this a recommended usage of this feature?

The 'enable novalidate' would still enforce the constraint on new rows added to the child tables, right?

What if I UPDATE a existing child record such that it doesnt obey the constraint? Would Oracle still enforce the constraint?

I guess what I am really asking is it "safe" to use ENABLE NOVALIDATE and what are the ramifications of doing so?

Thanks

Tom Kyte

Followup  

August 19, 2004 - 4:55 pm UTC

1) caveat emptor is because you have a constraint that you know is "not 100% enforced" -- you get what you got.

2) </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:950829834856#21106374932242 <code>

3) how long does it take to update this row? really? if the fkeys are unindexed -- it is going to take a boatload of time as ALL OF THE TABLES that have the unindexed fkey must be full scanned. forget about the row locking.

seems to me, if this is a common recurring problem -- you need to use a surrogate key on this table, so you NEVER change the "primary key"


More questions

August 19, 2004 - 5:04 pm UTC

Reviewer: A reader

Yes, most of the FK columns pointing to this table are not indexed and that is the main reason it takes so long. They are not indexed because there is no need to index them, the FK is purely for data validation, the FK columns are not accessed by index (except in this case, of course)

ENABLE NOVALIDATE works well for me, I just wanted to get your take if is is "safe" to do so.

i.e. it will just skip checking existing data during the ENABLE, but it will check new inserts/updates, right?

So, as long as I am certain that no "bad data" gets in while the constraints are disabled, I can safely "enable novalidate", right? Once it is enabled, my constraints will be obeyed.

Its just a little disconcerting to see 'NOT VALIDATED' in the *_CONSTRAINTS view!

Comments? Thanks

Tom Kyte

Followup  

August 19, 2004 - 8:09 pm UTC

...there is no need
to index them,...

apparently -- there is! unless you want this horribly expensive process - or you use a surrogate key.

No, enable novalidate isn't safe to me, not unless you shutdown the database and do this in single user mode.

not validated can affect the optimizer unless you set query rewrite integrity.

to me the only right answer (really) is

a) index them

or

b) use a surrogate key -- you obviously don't have a primary key here! you update it.

More questions

August 19, 2004 - 8:57 pm UTC

Reviewer: A reader

"not validated can affect the optimizer unless you set query rewrite integrity"

How exactly does 'not validated' affect the optimizer?

Where is this documented?

What should I set 'query_rewrite_integrity' to? Trusted? This is required anyway for FBIs, right? If I am not mistaken, I have this set to TRUSTED in my init.ora, so thats not an issue.

Unless you mean some other setting than TRUSTED?

"a) index them"

Yes, I will consider this, but I really dont want to index all the dozens of columns to benefit only this one-off process.


"b) use a surrogate key -- you obviously don't have a primary key here! you update it"

You misunderstand. This is NOT a PK, it is a UK. The PK is never updated. This is one of 2 UKs on the table. And all the dozens of FKs point to one of these 2 UKs.

Thanks

Tom Kyte

Followup  

August 19, 2004 - 10:48 pm UTC

in query rewrites, in trusting "not null" means "not null", in trusting that a foreign key from emp to dept -- given that emp(deptno) is not null -- really truly MEANS "emp related to dept in a m to 1 NON optional fashion".

it comes into play with query rewrite and things like MV's and "getting the right answer"

it is documented every where "enable but don't validate" is.


IMO you have two reasonable, realistic, safe, correct choices. I would not go down the path you are, it not in that list preceding.


then the UK is not a UK -- look you are updating a thing that by your definition "dozens of columns point to". if you update it, you are doing something *wrong*. period.

More questions

August 20, 2004 - 10:39 am UTC

Reviewer: A reader

"then the UK is not a UK -- look you are updating a thing that by your definition "dozens of columns point to". if you update it, you are doing something *wrong*. period."

I agree with everything you are saying but you are missing one small nuance.

Yes, dozens of columns point to that UK, but I am talking about adding a new value to the UK by mistake or business changes their mind.

Since this is a new value, apps havent started to use it yet, so even though the FK exists, in reality, no data really points to this new row.

Given this situation, I just want to disable the FKs, make my UK change and enable novalidate them, no harm done.

I guess I can get rid of the NOT VALIDATED at a later, off peak time, by

alter table modify constraint c enable validate;

Tom Kyte

Followup  

August 21, 2004 - 10:26 am UTC

"business changes their mind."

the value is definitely changing, therefore surrogate is the efficient/effective approach (broken record, I know, but hey -- you have dozens of tables, unindexed, pointing to this thing and you are asking "how to update it" -- my answer is use a surrogate)

disable/re-enabled the constraints on a recurring basis is going to be "not only a bummer but will give you data integrity issues"

More questions

August 21, 2004 - 10:48 am UTC

Reviewer: A reader

"use a surrogate"

Not sure what you mean by a surrogate in this context. Can you please elaborate?

"data integrity issues"

Dont know why you keep bringing this up. I am exposed to data integrity issues only for the duration of time the constraint is disabled, right? [I can guarantee that no data is updated during this period].

Once I ENABLE NOVALIDATE the constraint, I am back in business, right? Oracle will enforce the constraint for all inserts/updates from that point on? What am I missing?

The other issues you mentioned were regarding query rewrites and MVs. I dont have any MVs in my environment.

If ENABLE NOVALIDATE is such a no-no in your opinion, why does this feature exist? Under what circumstances does Oracle expect customers to use this?

Thanks

Tom Kyte

Followup  

August 21, 2004 - 12:19 pm UTC

use a sequence number -> code.

code changes, cool -- no problem. sequence number it NEVER changes.


you cannot guarantee anything since you will move onto another project at some point in your life.

enable novalidate is used in data warehouse environments where the only writes of the data are the loads and the loads went through a scrub.

but at the end of the day -- do what you will, you asked for my opinion and based on my experience I would change the data model to accomidate updates to this value naturally instead of going through huge administrative processes. 2 years from now, you won't be there -- someone else will. someone else will be living with this process and maybe they'll understand the implications, maybe not.

RELY

August 21, 2004 - 7:24 pm UTC

Reviewer: A reader

Yes, I asked for your opinion and you gave it to me, but I am having difficulty understanding your position to use ENABLE NOVALIDATE sparingly.

Yes, ideally I should change my data model such that these kind of UK updates are not needed, but changing data models mid-stream in a production system is very difficult.

Yes, I understand that for the time my constraint is disabled, I risk data integrity. But if my operational processes are setup such that I can guarantee no data updates during this brief period, I am good. Once I ENABLE NOVALIDATE, I am good again.

Yes, the NOVALIDATE will affect query rewrite and MVs, but even that can be addressed by using the RELY attribute. Quoting from the docs

<quote>
RELY and NORELY are valid only when you are modifying an existing constraint (that is, in the ALTER TABLE ... MODIFY constraint syntax). These parameters specify whether a constraint in NOVALIDATE mode is to be taken into account for query rewrite. Specify RELY to activate an existing constraint in NOVALIDATE mode for query rewrite in an unenforced query rewrite integrity mode. The constraint is in NOVALIDATE mode, so Oracle does not enforce it. The default is NORELY
<quote>

If you are still with me so far, can you explain why the docs say above (in bold) that Oracle does NOT enforce constraints in NOVALIDATE mode? Is this a doc bug because the NOVALIDATE just applies to existing data when the constraint is first placed in ENABLE NOVALIDATE mode. Once enabled, Oracle does enforce it, right?

Thanks

Tom Kyte

Followup  

August 21, 2004 - 8:19 pm UTC

You have unindexed foreign keys
You intend to update the primary key value
You intend to do this more than once
You know you are going to do this
You are planning on doing this on a recurring basis.

Therefore, IMHO the corrrect answer is DON'T UPDATE THE UNIQUE/PRIMARY KEY value -- use a surrogate.


The enable novalidate, maybe once -- because you didn't intend on doing this, got caught, must do it quickly, are willing to validate it later.

I'm always told by people "no time to do it right" but I find time and time again there always seems to be time to do it over.

You know "it'll work", you know "you can probably get away with it". I know "it'll become your standard operating procedure", I know "you'll never actually fix it", I know that at some point "it'll get messed up" cause you won't be there.

It is speaking (the part in bold) about the data that is in there -- oracle did not enforce the data actually matches the constraint. It is speaking about query rewrite.



Thanks!

August 21, 2004 - 9:05 pm UTC

Reviewer: A reader

"The enable novalidate, maybe once -- because you didn't intend on doing this, got caught, must do it quickly, are willing to validate it later"

Thats what I was getting at. Whew, at last we are on the same page.

I agree 100% with your comments about not having time to do it right but having time to do it over or get deeper into the "not right" approach. I also agree with your comment about a "shortcut" quickly becoming "standard operating procedure"

But, and maybe I am sounding like a broken record, but all this notwithstanding, NOT VALIDATED and RELY kind of cancel each other out, right?

NOT VALIDATED simply indicates that at some point in time, Oracle turned on the constraint without checking all the data, but the RELY says, ignore this and go ahead and use the constraint in query rewrite and MVs as if all the data meets the constraint.

So, given the RELY feature, is NOT VALIDATED still a "bad thing" as far as the optimizer is concerned?

[It is kind of like the DETERMINISTIC keyword in FBIs, the user is asking the database to "take my word for it". If the user lies and the function is not deterministic, query results might be wrong. Same here. If the constraint is marked RELY and some data doesnt obey the constraint, the user risks getting incorrect results]

Thanks

Tom Kyte

Followup  

August 22, 2004 - 7:59 am UTC

IF the query rewrite integrity is set to "trust me", yes:

ops$tkyte@ORA9IR2> create table emp as select * from scott.emp;
 
Table created.
 
ops$tkyte@ORA9IR2> create table dept as select * from scott.dept;
 
Table created.
 
ops$tkyte@ORA9IR2> alter session set query_rewrite_enabled=true;
 
Session altered.
 
ops$tkyte@ORA9IR2> create materialized view emp_dept
  2  build immediate
  3  refresh on demand
  4  enable query rewrite
  5  as
  6  select dept.deptno, dept.dname, count (*)
  7    from emp, dept
  8   where emp.deptno = dept.deptno
  9   group by dept.deptno, dept.dname
 10  /
 
Materialized view created.
 
ops$tkyte@ORA9IR2> insert into emp (empno,deptno) values ( 1, 1 );
 
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_mview.refresh( 'EMP_DEPT' );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter table dept
  2  add constraint dept_pk primary key(deptno)
  3  RELY enable NOVALIDATE
  4  /
 
Table altered.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter table emp
  2  add constraint emp_fk_dept
  3  foreign key(deptno) references dept(deptno)
  4  RELY enable NOVALIDATE
  5  /
 
Table altered.
 
ops$tkyte@ORA9IR2> alter table emp modify deptno not null;
 
Table altered.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter session set optimizer_goal=all_rows;
 
Session altered.
 
ops$tkyte@ORA9IR2> set autotrace on explain
ops$tkyte@ORA9IR2> alter session set query_rewrite_integrity=enforced;
 
Session altered.
 
ops$tkyte@ORA9IR2> select count(*) from emp;
 
  COUNT(*)
----------
        15
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=3 Card=654)
 
 
 
ops$tkyte@ORA9IR2> alter session set query_rewrite_integrity=trusted;
 
Session altered.
 
ops$tkyte@ORA9IR2> select count(*) from emp;
 
  COUNT(*)
----------
        14
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=13)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'EMP_DEPT' (Cost=2 Card=82 Bytes=1066)
 
 
 
ops$tkyte@ORA9IR2> set autotrace off



<b>but notice the different answers</b>
 

More questions

August 22, 2004 - 9:22 am UTC

Reviewer: A reader

"IF the query rewrite integrity is set to "trust me", yes:
...
but notice the different answers"

Um, sorry I went all over the place in my previous comments. I lost track of what I asked you to respond to! Sorry.

What are you saying "yes" to above?

What point did you illustrate in your example above?

Thanks

Tom Kyte

Followup  

August 22, 2004 - 4:48 pm UTC

novalidate and rely do not cancel eachother
unless
query rewrite integrity is trust me
and then
you must be prepared to get wrong answers if you lied.

ENABLE NOVALIDATE

August 22, 2004 - 1:45 pm UTC

Reviewer: Reader from US

One can have a primary or a uniq constraint based on a non-unique index as demonstrated in the begining of this thread.
The constraint is unique but based on a non-unique index .

What are the advantages of having such a constraint and When are they really useful ?

Can you site some examples ?


Tom Kyte

Followup  

August 22, 2004 - 5:05 pm UTC

the constraint can be dropped -- but the index remains.

the constraint can be deferred, meaning during the course of the transaction the table constains "duplicates" but by the time you commit -- it doesn't (all constraints on MV's should be deferrable - the refresh process issues "set constraints all deferred" -- this allows the MV to have constraints, but doesn't make it so the constraint fails the refresh -- refreshes are done transactionally consistent, but each statement performed by the refresh could be "inconsistent")

deferrable constraints were predominatly implemented for constraints on snapshots (mv's)

Can't seem to get the syntax right? am I barking up the wrong tree?

September 17, 2004 - 8:26 am UTC

Reviewer: Mike from England

I have a requirement to enable a customer to slowly sort out some existing data, while ensuring that the situation does not get any worse...

The rule is that for a given con_site_id (a FK to a parent table) and con_gen_tech_type not more than 1 row in this table can have a con_status_id of 238.

I thought it would be best to put a unique Function Based Index on this and enable no validate it, job done. but the blighter won't go on initially in a disabled state.

--
-- SET UP
--
CREATE TABLE MY_CONFIGURATIONS
(
CON_CONFIGURATION_ID NUMBER NOT NULL,
CON_SITE_ID NUMBER,
CON_STATUS_ID NUMBER,
CON_GEN_TECH_TYPE VARCHAR2(50)
)

alter table my_configurations add constraint con_pk primary key(con_configuration_id)
/

alter table my_configurations add constraint con_ck1 check (con_gen_tech_type in ('900','1800','3G'))
/

insert into my_configurations
values
(1,2,238,'900')
/
--
insert into my_configurations
values
(2,2,238,'900')
/
--
insert into my_configurations
values
(3,2,238,'1800')
/
--
insert into my_configurations
values
(4,2,237,'900')
/
--
-- END OF SET UP
--
-- Existing data stops it just being"popped on"
create unique index con_uk on my_configurations (decode(con_status_id,238,(con_site_id||' '||con_gen_tech_type),null))
/
-- Not valid syntax
--
create unique index con_uk on my_configurations (decode(con_status_id,238,(con_site_id||' '||con_gen_tech_type),null)) enable novalidate
/
--
create unique index con_uk on my_configurations (decode(con_status_id,238,(con_site_id||' '||con_gen_tech_type),null)) deferred
/
-- nor as a constraint (But I don't think you can do a FBC
alter table my_configurations add constraint con_uk unique (decode(con_status_id,238,(con_site_id||' '||con_gen_tech_type),null))
/
-- Eeek!

Where am I going so massively wrong??

Thanks,

Mike.


Tom Kyte

Followup  

September 17, 2004 - 9:48 am UTC

well, the index is either

a) there
b) not there


it is not like a constraint at all.

The only thing I could think of off the top of my head is, that while they are "cleaning up", you would use an IOT as your "unique index" and a trigger to maintain it.

warning: code not "run and tested", the concept is here only.

create table con_uk
( CON_SITE_ID number, CON_GEN_TECH_TYPE varchar2(50),
primary key(con_site_id,CON_GEN_TECH_TYPE) )
organization index ;


create or replace package my_pkg
as
procedure i( p_status_id in number, p_site_id in number,
p_gen_tech_type in varchar2 );
procedure u( p_nstatus_id in number, p_nsite_id in number,
p_ngen_tech_type in varchar2,
p_ostatus_id in number, p_osite_id in number,
p_ogen_tech_type in varchar2 );
procedure d( p_status_id in number, p_site_id in number,
p_gen_tech_type in varchar2 );
end;
/
create or replace package body my_pkg
as


procedure i( p_status_id in number, p_site_id in number, p_gen_tech_type in varchar2 )
is
begin
if (p_status_id=238)
then
insert into con_uk (CON_SITE_ID,CON_GEN_TECH_TYPE)
values (p_site_id,p_gen_tech_type);
end if;
end;

function equal( x in varchar2, y in varchar2 ) return boolean
is
begin
return x = y OR (x is null and y is null);
end;


procedure u( p_nstatus_id in number, p_nsite_id in number,
p_ngen_tech_type in varchar2,
p_ostatus_id in number, p_osite_id in number,
p_ogen_tech_type in varchar2 )
is
begin
if (NOT equal( p_nstatus_id, p_ostatus_id )
OR
NOT equal(p_osite_id,p_nsite_id)
OR
NOT equal(p_ogen_tech_type,p_ngen_tech_type))
AND
(p_nstatus_id = 238 OR p_ostatus_id = 238)
then
if ( p_ostatus_id = 238 )
then
d( p_ostatus_id, p_osite_id, p_ogen_tech_type );
end if;
if ( p_nstatus_id = 238 )
then
i( p_nstatus_id, p_nsite_id, p_ngen_tech_type );
end if;
end if;
end;


procedure d( p_status_id in number, p_site_id in number, p_gen_tech_type in varchar2 )
is
begin
if (p_status_id=238)
then
delete from con_uk where CON_SITE_ID = p_site_id and CON_GEN_TECH_TYPE = p_gen_tech_type;
end if;
end;

end;
/


create or replace trigger con_uk_trigger
after insert or update or delete on MY_CONFIGURATIONS
for each row
begin
if ( inserting ) then my_pkg.i(:new.con_status_id, :new.con_site_id, :new.con_gen_tech_type );
elsif( updating ) then my_pkg.u(:new.con_status_id, :new.con_site_id, :new.con_gen_tech_type,
:old.con_status_id, :old.con_site_id, :old.con_gen_tech_type );
else my_pkg.d(:old.con_status_id, :old.con_site_id, :old.con_gen_tech_type );
end if;
end;
/




concept = add a new row upon insert of 238, delete old when deleting 238, during update, check to see if any of the fields changed and if so -- if either status ids were 238 -- invoke D and I as needed.




Thanks - Can I just check that...

September 21, 2004 - 11:57 am UTC

Reviewer: Mike from England

I know this smacks of "Just let it go" but I just wanted to make sure that my understnading is correct.

I thought of the following... (Psuedo of sorts)

create table back_up as select * from my_table
/

alter child_table disable fk_constraint
/

alter table my_table disable all triggers
/

truncate table my_table
/

create unique index my_idx on my_table (decode(con_status_id,238,con_site_id||' '||con_gen_tech_type, null))

alter my_index disable
/

alter my_index unusable
/

alter session set skip_unusable_indexes=true
/

insert into my_table select * from back_up
/

alter index my_idx enable no validate
/

Which.... I can't do. And I understand this is my fault, I have to mark it as unusable and it won't skip it as it is unquie, Fair Enough. But there isn't some other way of disabling / marking it duff so that I can stick the data back in and then enable no validate it is there? In fact you can't even enable no validate an index which makes sense. I guess I just can't do this can I?

The only reason I ask is that instead of the suggestion above I could do the old multi triggers with the mutating table catch. This seems less implementation effort to me?

Any Chance of Oracle 32Z implementing Function Base Constraints?

Thanks as ever,

Mike.

Tom Kyte

Followup  

September 21, 2004 - 12:00 pm UTC

no validate is for constraints, it doesn't apply to an index.

Impact of noValidate constraint

December 28, 2004 - 3:01 pm UTC

Reviewer: A reader from Houston

Tom,
I have joined this new place where they have a OLTP DB wth a couple of 100 tables. There are a handful of FK's between the tables even though they are many columns that can be made FK's. These columns that can be made FK's have currently have a lot of invalid data.
I would like to know your suggestions on the following
If we create a foreign key with noValidate constraint are they going to be any problems with
1a) SQL performance 1b) Indexes and 1c) Materialized views
2) Does anythig change because an FK has a noValidate constraint.
3) Is it bad to have a lot of FK's on a given table wrt performance.

Thanks and Regards






Tom Kyte

Followup  

December 28, 2004 - 3:47 pm UTC

why bother having a fkey constraint at all if

a) the data is dirty
b) you know it is dirty

an fkey means "you can rely on this data, if you join, the parent record will be here, no problem"

an fkey on dirty data is just plain "misleading"

1) won't affect normal sql, indexes have nothing to do with fkeys (other than you want to add one of course if you ever DELETE from parent or UPDATE parent prmiary key), MV's will be not as useful -- since the 'fact' that every child has a parent is not true, so rewrite will not be working fully.

2) yes, your perception of the truth. As an end user, or an ad-hoc query tool, or whatever -- you have just lied to us.

3) real question is "is it bad to have no data integrity, remember data correctness doesn't come in 'shades' -- you either HAVE IT or you DON'T". A foreign key is the fastest, least resource intensive all around best way to ensure data integrity.


Suggest you go through a data cleansing operation, not an attempt to hide the issue under the rug.

noValidate constraint

December 28, 2004 - 4:40 pm UTC

Reviewer: A reader

Tom,
I agree with every single word of what you have said.
I tried to get the data cleaned, but it is a huge effort. So while the data is being cleaned I would like to set up the noValidate constraint.
I just needed a confirmation from you before I could chew up the developers who put us in this mess.

Thank you


Tom Kyte

Followup  

December 28, 2004 - 6:10 pm UTC

as they fix the data, you best go back and do the enable VALIDATE

A little trap with NOVALIDATE

December 30, 2004 - 12:36 am UTC

Reviewer: Richard Foote from Australia

Hi Tom,

I just had a quick read of this thread but didn't see this point so I just thought I'll mention it as it can be a little trap for the unwary when using novalidate constraints and it doesn't involve query rewrites.

Very simple demo to hightlight:

SQL> show parameter query_rewrite_enabled

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled                string      FALSE

SQL> create table bowie as select * from dba_objects;

Table created.

SQL> create index bowie_status_idx on bowie(status);

Index created.

SQL> alter table bowie modify status not null;

Table altered.

SQL> exec dbms_stats.gather_table_stats(ownname=>'BOWIE', tabname=> 'BOWIE', cascade=>true);

PL/SQL procedure successfully completed.

SQL> set autotrace on
SQL> select * from bowie where status is null;

no rows selected


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=93)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'BOWIE' (Cost=1 Card=1 Bytes=93)

   2    1     INDEX (RANGE SCAN) OF 'BOWIE_STATUS_IDX' (NON-UNIQUE) (Cost=1 Card=1)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        918  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

Note that the optimizer is "clever" enough to know that there can't be a null value for the status column due to the not null constraint and so has avoided an unnecessary FTS. A quirky example of where a b*tree index can be useful on a low cardinality column if it's possible for users to select data that can't exist.

However, if one enables the constraint with novalidate ...

SQL> alter table bowie modify status null;

Table altered.

SQL> alter table bowie modify status not null novalidate;

Table altered.

SQL> select * from bowie where status is null;

no rows selected


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=39 Card=1 Bytes=93)
   1    0   TABLE ACCESS (FULL) OF 'BOWIE' (Cost=39 Card=1 Bytes=93)

Statistics
----------------------------------------------------------
        185  recursive calls
          0  db block gets
        404  consistent gets
          0  physical reads
          0  redo size
        918  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          0  rows processed

Now the optimizer has no choice but to perform the FTS as it can't know for certain that there are no nulls as the constraint has not been validated. Similar problems can be encountered for other check constraints as well. 

Strangely, attempting to select data that can't exist or the requirement for an empty set is more common than many realize. In many scenarios, a validated constraint can provide the optimizer with useful/vital information.

Cheers

Richard Foote 

January 19, 2005 - 6:33 pm UTC

Reviewer: A reader


Need to enable unique for future rows

February 22, 2005 - 1:02 pm UTC

Reviewer: Anurag Mehrotra

I have a table with approx 200,000 rows. I need to create a unique constraint on two of the columns that should be enforced for any future rows. I am using Oracle 8.1.7

I am using the following command:

ALTER TABLE XYZ
ADD CONSTRAINT UNQ_A_B
UNIQUE (A, B)
ENABLE NOVALIDATE;

I get the ORA-02299 error (duplicate keys found). I do not want to create the index, I would rather rely on Oracle features, since the manual says this is a better way of creating unique constraints.

How can I enforce the constraint only for new rows? I cannot clean up the table and get rid of duplicate data.


Tom Kyte

Followup  

February 22, 2005 - 5:11 pm UTC

*seems very very very fishy*

hmmm, why? to what end? what would this solve? I mean, you have duplicates already - tables do not have a "percentage of data integrity", they either

a) are
b) are not

and in the future, how do you determine "rows that are OK to be non-unique" versus everything else?

any timestamp field on this table?
any sequences used to create kesy on this table?
something in this table that says "this row was created after this point in time?"

Need to enable unique for future rows

February 22, 2005 - 5:46 pm UTC

Reviewer: Anurag Mehrotra

Yes, I do have a primary key for this table being generated by a sequence.

I can always pinpoint a unique row based on this primary key, however I need to establish a new constraint with two columns as unique (the columns are not part of primary key, but are Foreign key to another table).

Is there a way around this short of getting rid of duplicate records?



Tom Kyte

Followup  

February 23, 2005 - 1:54 am UTC

this is the worst idea I've ever heard of and seem rather pointless, but hey -- it is your data....


create unique index on t (
case when id > N then col1 end,
case when id > N then col2 end
);

where id is your primary key column that is based on the sequence.
N is the current value (or the value that which you want all rows unique)

col1, col2 are the columns in question.  would work and would leave behind a breadcrumb for the people that come behind you and find this "impossible condition" (at least the dictionary would tell them "hey -- we allow dups for id <= N on purpose -- believe it, or not!")

Although you can use a constraint as well, you need to use deferrable so it uses a non-unique constraint, but expect heartburn and hassle into the future for simple things like "reorg" and "export import" will be affected by this evermore.

Again, I put this on the list of things to "not do in real life", but hey:

ops$tkyte@ORA9IR2> create table t ( x int primary key, c1 int, c2 int );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( 1, 2, 2 );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into t values ( 2, 2, 2 );
 
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter table t add constraint c1_c2_unique
  2  unique(c1,c2) deferrable
  3  enable novalidate;
 
Table altered.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( 3, 3, 3 );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into t values ( 4, 3, 3 );
insert into t values ( 4, 3, 3 )
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.C1_C2_UNIQUE) violated
 
 
ops$tkyte@ORA9IR2> insert into t values ( 5, 2, 2 );
insert into t values ( 5, 2, 2 )
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.C1_C2_UNIQUE) violated
 
 

Foreign Key documentation in the database?

June 17, 2005 - 9:50 am UTC

Reviewer: Duke Ganote from THE FAR EAST (side of Cincinnati)

I've got a data warehouse and marts where the referential integrity is ETL-enforced.  However, it's a pain to reverse-engineer the data structures in ERwin (or any other data modeling tool) because the RI is not documented in the database.  I'm thinking that establishing DISABLED RELY NOVALIDATE would be appropriate for documentation purposes.  My example set up is:

CREATE TABLE DIM ( DIM# INTEGER NOT NULL );
ALTER TABLE DIM ADD  ( PRIMARY KEY (DIM#) ) ;
CREATE TABLE F ( DIM# INTEGER NULL );
ALTER TABLE F ADD constraint FK_D_TO_F
    FOREIGN KEY (DIM#) REFERENCES DIM
    RELY DISABLE NOVALIDATE;

Which "works", that is:
1. it doesn't impede the ETL because the constraint is DISABLEd
    SQL> insert into f values ( 0 );
    1 row created.
    SQL> insert into dim values ( 1 );
    1 row created.
2. is read by ERwin as a constraint
3. could work with MVs because it's RELY

Would this be your recommended approach, and is there anyplace in Oracle to add free-form text comments on why the constraints are disabled? 

Tom Kyte

Followup  

June 17, 2005 - 3:45 pm UTC

You can comment on tables - using the comments command and on columns.


Not sure I'd use RELY unless the data was sure to be clean, and it is not sure to be clean..... ETL tool might be programmed wrong.

invalidated after enable

August 25, 2005 - 9:12 am UTC

Reviewer: A reader from Z├╝rich

Hi Tom.

Running an "alter table T enable constraint C", under what circumstances could the constraint be NOT VALIDATED and ENABLED (as the default behaviour of ENABLE also validates the constraint, does it not)?

Tom Kyte

Followup  

August 25, 2005 - 2:11 pm UTC

are you say you have a constraint that is enabled, not validated after an "enable" as you have typed?



A problem after recovery

February 15, 2006 - 8:00 am UTC

Reviewer: A. Semenov from Ukraine

Hi Tom.
Could you help me with my problem...
I use Oracle9i DB in archivelog mode. And after next system crash the DB was recovered. Everything seemed good but then I found the following problem.
I have a table with Primary key constraint:
create table OUTINVOICES
(
OINV_ID NUMBER not null,
OINV_AG_ID NUMBER not null,
OINVDATE DATE,
OINV_GRP_ID NUMBER not null,
);
alter table OUTINVOICES add constraint OINV_ID primary key (OINV_ID);

There are tens of thousands records in it. And it was noticed that the two last inserted before crash records were found "absent" with existing corresponding details to these records with foreign key constraint on this field.
I.e. when I just do

select count(*)
from OUTINVOICES t
where t.OINV_ID in (26213, 26214);

COUNT(*)
----------
0
I get nothing.

But if I do

select count(*)
from outinvoices o,
outinvoices o2
where o.oinv_id=o2.oinv_id(+)
and (o.oinv_id = 26213 or
o.oinv_id = 26214);

COUNT(*)
----------
2
I get 2 records.

If I do

select count(*)
from outinvoices o,
outinvoices o2
where o.oinv_id=o2.oinv_id(+)
and o.oinv_id = 26213

COUNT(*)
----------
0
I get nothing again.

So I get 2 records only when I use (+) relation and
(o.oinv_id = 26213 or o.oinv_id = 26214)

In place of o2 table could be any other table:
select count(*)
from outinvoices o,
user_objects o2
where o.oinv_id=o2.object_id(+)
and (o.oinv_id = 226213 or
o.oinv_id = 226214);

COUNT(*)
----------
2

How could it be fixed?
Thank you.

Tom Kyte

Followup  

February 15, 2006 - 9:41 am UTC

analyze the index that is on the primary key fields with "validate structure". anything interestnig pop out of that?

A problem after recovery

February 15, 2006 - 12:47 pm UTC

Reviewer: A. Semenov from Ukraine

SQL> analyze index OINV_ID validate structure;

Index analyzed 

Tom Kyte

Followup  

February 15, 2006 - 1:20 pm UTC

output of:

set autotrace on explain
select oinv_id
from outinvoices o
where (o.oinv_id = 226213 or
o.oinv_id = 226214);

select /*+ FULL( o ) */ oinv_id
from outinvoices o
where (o.oinv_id = 226213 or
o.oinv_id = 226214);
set autotrace off


is what?

A problem after recovery

February 15, 2006 - 12:58 pm UTC

Reviewer: A. Semenov from Ukraine

SQL> analyze table oda.outinvoices validate structure cascade

ORA-01499 table/index cross reference failure - see trace file
 

Tom Kyte

Followup  

February 15, 2006 - 1:22 pm UTC

ok, index is out of sync with table.

please contact support for further diagnoses.

A problem after recovery

February 15, 2006 - 2:45 pm UTC

Reviewer: A. Semenov from Ukraine

Will it work if I do following steps:
1. Get the list of dependents FK
select t.*
from user_constraints t;
where t.r_constraint_name='OINV_ID';

2. Invalidate these constraints;

3. create table copy_outinvoices as
select * from outinvoices;

4. drop table outinvoices;

5. create table outinvoices...;

6. insert into outinvoices
select from copy_outinvoices;

7. Create all the constrains on outinvoices;

8. Validate all dependent constrains;

Thank you...

Tom Kyte

Followup  

February 15, 2006 - 2:49 pm UTC

please contact support for further diagnoses.

How to avoid validation in novalidate?

May 25, 2006 - 5:55 pm UTC

Reviewer: AVS from India

Tom,

I've a single child table consisting on 159 million records. It does lookup on 40 master tables before inserting data in child table. This lookup is done through trigger on child table. Now DBA suggested that trigger slows down the performance hence they are not to be used. I've got window on just 3 hours on weekends in which I can drop the trigger and add the actual foreign key constraint between child and master tables. A addition of one foreign key takes 30-35 minutes. I know my data is correct which I've also validated using some queries. So how can I add add the foreign key constraints without validating the current state of data but going forward should start to validate.

Cheers,
AVS


Tom Kyte

Followup  

May 26, 2006 - 8:26 am UTC

Did you know that in addition to being "slow", using the trigger means you DID IT WRONG. Unless of course the trigger included "lock table" commands - which I seriously doubt it did. You cannot enforce referential integrity in a trigger without using lock table/big select for updates - and no one does.

add the constraint with rely enable novalidate options.


</code> http://asktom.oracle.com/Misc/stuck-in-rut.html <code>

PK "DISABLE VALIDATE" and Read-only

November 06, 2006 - 12:30 pm UTC

Reviewer: Arck from PA

ALTER TABLE myemp MODIFY CONSTRAINT pk_myemp
DISABLE VALIDATE;

SQL> INSERT INTO myemp (empno) VALUES (21);

ORA-25128: No insert/update/delete on table with constraint (auser.PK_myemp) disabled and validated
========================================================================
Is this a "proper" way to make a table *temporarily* READ-ONLY ? and if not, what is ?
what are the caveats of doing it this way ?

thanks !
 

Tom Kyte

Followup  

November 06, 2006 - 12:35 pm UTC

it is a WAY.


there are other ways.


o put a trigger on the table before insert/update/delete that always fails (can be bypassed by someone that can disable the trigger, just like the above however)

o make the tablespace the table is in READ ONLY (not good if other read write objects exist of course)

o use dbms_fga to create a policy "with check" that prevents modification/creation of any new row

o create unique index t_idx on t(x) unusable;
will prevent modifications as well - that index cannot be skipped



In most cases, the trigger is probably more than sufficient and can raise a meaningful error message:

begin
raise_application_error( -20001, 'Table is read only!' );
end;

ETL-enforced, but optimizer-useable RI in datamarts

October 27, 2007 - 11:02 am UTC

Reviewer: Duke Ganote from Amelia, Ohio USA

I'm not sure if there was a change from 9iR2 to 10gR2 (or I missed something), but I was checking my example from above:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8806498660292#42846909746173
and found I needed to specify RELY for the dimensional primary key.

In order to get ETL-enforced, but optimizer-useable-for-rewrites and documented-in-the-database referential integrity, a revised example is:

##########################################
CREATE TABLE dim
( dim# INTEGER
, dim_name VARCHAR2(10)
)
;
ALTER TABLE dim
ADD CONSTRAINT dim_pk
PRIMARY KEY (dim#)
RELY -- I had to add this for the RELY FK later
;
CREATE TABLE fact
( dim# INTEGER
CONSTRAINT fact_dim#_nn NOT NULL
, additive_measure$ NUMBER
)
;
CREATE BITMAP INDEX fact_dim#_bit
ON fact ( dim# )
;
ALTER TABLE fact
ADD CONSTRAINT fact_to_dim_fk
FOREIGN KEY (dim#) REFERENCES DIM
RELY DISABLE NOVALIDATE
;
--
-- "authorized" dimensional values, static
--
INSERT INTO dim VALUES ( 1, 'something' )
;
INSERT INTO dim VALUES ( 2, 'else' )
;
--
-- now we load the daily fact records
--
INSERT INTO fact VALUES ( 1, 23.5 )
;
INSERT INTO fact VALUES ( 3, 42.1 ) -- but wait, there's something awry
;
--
-- and backtrack to enforce integrity
--
INSERT INTO dim ( dim#, dim_name )
SELECT dim#, 'unknown'
FROM fact
MINUS
SELECT dim#, 'unknown'
FROM dim
;
##########################################
sql> SELECT constraint_name, constraint_type
2 FROM user_constraints
3 WHERE table_name IN ( 'DIM', 'FACT')
4 ;

CONSTRAINT_NAME C
------------------------------ -
DIM_PK P -- all named constraints
FACT_DIM#_NN C -- even the NOT NULL
FACT_TO_DIM_FK R

March 26, 2011 - 1:35 am UTC

Reviewer: A reader

thanks tom. i was very confused about changing CONSTRAINT STATE. now its clear to me. Thank you again for being with us.

regards,
rabi

DEFERRABLE clause missed at CREATE table

February 26, 2012 - 5:42 am UTC

Reviewer: Praveen Ray from Kolkata, India

Hi Tom,

Say, I have a very bulky table with triggers/partition/comments etc. Now, after 10 yrs, we realize if a column would have defined as DEFERRABLE, things can be much easier.

ALTER TABLE...DEFERRABLE doesn't work. How can we achieve this?

Praveen Ray.
Tom Kyte

Followup  

February 28, 2012 - 6:11 am UTC

you have to drop and recreate the constraint, that is the only way.

alter table and deferrable

February 28, 2012 - 6:19 am UTC

Reviewer: Praveen Ray from Kolkata, India

please, confirm table or constraint. i don't think you are saying constraint, i know.
Tom Kyte

Followup  

February 28, 2012 - 7:30 am UTC

since the only thing that can be deferrable is a constraint, I meant constraint. There is no such thing as a deferrable column or table.

alter table and deferrable

February 28, 2012 - 10:27 am UTC

Reviewer: Praveen Ray from Kolkata, India

...
that means we can create the constraint without DROPping the table? I don't think so, I tried but it seems alter table doesn't have DEFERRABLE clause.

The documentation even says so:

"You cannot alter the deferrability of a constraint. Whether you specify either of these parameters, or make the constraint NOT DEFERRABLE implicitly by specifying neither of them, you cannot specify this clause in an ALTER TABLE statement. You must drop the constraint and re-create it."

http://docs.oracle.com/cd/E11882_01/server.112/e26088/clauses002.htm#i1002273

Here, "You must drop the constraint and re-create it." means drop the table and recreate the constraint at the time of creating the table.

That implicitly, says drop the TABLE. Right?

If you can do so, withhout dropping the table, please give one simple example. It will be really very helpful.

Thank you.
Tom Kyte

Followup  

February 28, 2012 - 10:54 am UTC

I'm not following you at all.


tables are *not* deferrable.

the only defferrable things are constraints.

I do not know what you are talking about at all.



And to make a constraint deferrable - you drop the CONSTRAINT (not the table) and then add it in the deferrable mode.

ops$tkyte%ORA11GR2> create table t ( x int constraint t_pk primary key, y int );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> alter table t drop constraint t_pk;

Table altered.

ops$tkyte%ORA11GR2> alter table t add constraint t_pk primary key(x) deferrable;

Table altered.


Perfect

May 28, 2012 - 3:18 am UTC

Reviewer: Raiber from UAE

Perfect .....

October 10, 2012 - 4:16 pm UTC

Reviewer: A reader

We are upgrading a database from 10g to 11g. We exported and imported the data and while creating the constraints we noticed that one of the table had duplicate rows so we could not create PK constraints but the constraint is enabled and running in current production with duplicate rows. How is that possible ?

CREATE TABLE role
(
dt_col DATE NULL,
BUSINESS_CD VARCHAR2(30) NULL,
SKILL_GROUP_ID VARCHAR2(30) NULL,
ENTERPRISE_SKILL_ID VARCHAR2(30) NULL,
ROLE_NM VARCHAR2(50) NULL
)

insert into role values ('23-JUN-10', 'RS', 2666, 5550);

If we insert this twice, it should fail if there is a PK constraint right ? Is there some scenario where this would work ?
Is there some difference between 10g and 11g in the enable novalidate ?


Tom Kyte

Followup  

October 10, 2012 - 4:42 pm UTC

We are upgrading a database from 10g to 11g. We exported and imported the data

I thought you said you were upgrading. export and import play no role in an upgrade. that isn't upgrading that is creating a brand new database that never existed before. (you should just upgrade...)


if you truly have duplicate rows in a table with an enabled validated primary key - please contact support.


if the primary key is enabled novalidate - then sure you could have duplicates.

I'm not sure what your situation is, it is not clear from the above