OraFAQ Forum: SQL & PL/SQL » getting error on enabling constraints even using novalidate clause

Home » SQL & PL/SQL » SQL & PL/SQL » getting error on enabling constraints even using novalidate clause (Oracle ,10g,windows 7)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

getting error on enabling constraints even using novalidate clause [message #603965]

Sat, 21 December 2013 03:19

evivek
Messages: 6
Registered: December 2013
Location: mumbai

Junior Member

CREATE TABLE SCOTT.STUDENT_DETAILS
(
ROLLNO NUMBER,
NAME VARCHAR2(50 BYTE),
CITY VARCHAR2(20 BYTE),
MARKS NUMBER,
INVOICENO CHAR(1 BYTE)
)

select * from student_details

alter table student_details
add (constraint pk primary key(rollno),
constraint uk unique(invoiceno),
constraint ck check(marks>0))
modify name not null modify city default 'pune'

select * from all_constraints
where table_name='STUDENT_DETAILS'

insert into student_details(rollno,name,marks,invoiceno)
values(2,'Raj',100,'z')

alter table student_details
disable constraint ck

alter table student_details
modify name null modify city default null

insert into student_details(rollno,name,marks,invoiceno)
values(2,'Raj',100,'z')

alter table student_details
enable novalidate primary key

even I am using novalidate clause still getting error like below

ORA-02437: cannot validate (SCOTT.PK) - primary key violated

Report message to a moderator

Re: getting error on enabling constraints even using novalidate clause [message #603966 is a reply to message #603965]

Sat, 21 December 2013 03:53

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

This is, I presume, because of the fact that primary key constraint wants to use a unique index on the primary key column(s). A workaround is to a) create a non-unique index, b) create a primary key. Something like this:

SQL> CREATE TABLE SCOTT.STUDENT_DETAILS
  2  (
  3     ROLLNO      NUMBER,
  4     NAME        VARCHAR2 (50 BYTE),
  5     CITY        VARCHAR2 (20 BYTE),
  6     MARKS       NUMBER,
  7     INVOICENO   CHAR (1 BYTE)
  8  );

Table created.

SQL> CREATE INDEX i1_pk
  2     ON student_details (rollno);

Index created.

SQL> ALTER TABLE student_details
  2  ADD CONSTRAINT pk PRIMARY KEY(rollno);

Table altered.

SQL> ALTER TABLE student_details DISABLE CONSTRAINT pk;

Table altered.

SQL> INSERT INTO student_details (rollno,
  2                               name,
  3                               marks,
  4                               invoiceno)
  5       VALUES (2,
  6               'Raj',
  7               100,
  8               'z');

1 row created.

SQL>
SQL> INSERT INTO student_details (rollno,
  2                               name,
  3                               marks,
  4                               invoiceno)
  5       VALUES (2,
  6               'Raj',
  7               100,
  8               'z');

1 row created.

SQL> ALTER TABLE student_details ENABLE NOVALIDATE PRIMARY KEY;

Table altered.

SQL>

Report message to a moderator

Re: getting error on enabling constraints even using novalidate clause [message #603968 is a reply to message #603965]

Sat, 21 December 2013 04:44

evivek
Messages: 6
Registered: December 2013
Location: mumbai

Junior Member

thank you littlefoot
the way you have suggested is work around so what is exact issue as I have done every thing right
so why I am getting that error

second when I try to enable unique constraint with novalidate clause I am still a getting a same kind of error
for the below command

alter table student_details
enable novalidate unique(invoiceno)

and error like "ORA-02299: cannot validate (SCOTT.UK) - duplicate keys found"

Report message to a moderator

Re: getting error on enabling constraints even using novalidate clause [message #603969 is a reply to message #603968]

Sat, 21 December 2013 05:32

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Perhaps the same solution would apply.

Report message to a moderator

Re: getting error on enabling constraints even using novalidate clause [message #603970 is a reply to message #603968]

Sat, 21 December 2013 05:54

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Littlefoot wrote on Sat, 21 December 2013 10:53

This is, I presume, because of the fact that primary key constraint wants to use a unique index on the primary key column(s). A workaround is to a) create a non-unique index, b) create a primary key.

Report message to a moderator

Re: getting error on enabling constraints even using novalidate clause [message #603973 is a reply to message #603965]

Sat, 21 December 2013 09:44

martijn
Messages: 286
Registered: December 2006
Location: Netherlands

Senior Member

Hi Evivek,

Have a good look at the inserts:
evivek wrote on Sat, 21 December 2013 10:19

[size=2]...
...
insert into student_details(rollno,name,marks,invoiceno)
values(2,'Raj',100,'z')
...
...
insert into student_details(rollno,name,marks,invoiceno)
values(2,'Raj',100,'z')
...
...
ORA-02437: cannot validate (SCOTT.PK) - primary key violated

You are entering the exact same information twice.
You have created a primary key on rollno.
This means rollno must be:
- not null
- unique

You did enter the same number for rollno twice. So this is not unique.
In other words: the primary key is violated

HTH

Report message to a moderator

Re: getting error on enabling constraints even using novalidate clause [message #603976 is a reply to message #603965]

Sat, 21 December 2013 12:29

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

A good link to read.

Report message to a moderator

Re: getting error on enabling constraints even using novalidate clause [message #603977 is a reply to message #603973]

Sat, 21 December 2013 12:35

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Martiijn, read littlefoot's response, then read up on enable novalidate, you seem to have some confusion in this area, unless I have misunderstood your post.

Report message to a moderator

Re: getting error on enabling constraints even using novalidate clause [message #603978 is a reply to message #603977]

Sat, 21 December 2013 14:58

martijn
Messages: 286
Registered: December 2006
Location: Netherlands

Senior Member

I believe, in kind of pseudo code, the OP did the following:

1 - Create the table (no constraints)
2 - select all from table
3 - alter the table with following constraints:
- primary key on rollno
- unique constraint on invoiceno
- check constraint on check
- name not null (not a constraint)
- default city is pune (not a contraint)
4 - select from all constraints
5 - insert a row (with rollno=2)
6 - disable the check constraint
7 - some more alterations to the table
- name can be null
- city default is null
8 - once again add a row (with rollno=2)
Now the OP gets an error I would say
9 - Enable the primary key with novalidate (but which was already enabled)

So before the use of the novalidate clause in step 9 the OP already ran into a problem.
So I would still say that the problem of the OP is in the inserting of two same values for the PK.

The solution offered by littlefoot works like a charm, and demonstrates some nice/not-nice oracle behavior.
The novalidate clause is known to me. The creating of the non-unique index to solve the problem was in a dark and dusty corner of my brain, but...I did see it before.

To me the problem (in this case) still is in the inserting of the same data, and the misunderstanding of the novalidate clause by the OP.
I am under the impression that the OP taught (<--past tense of to think) that the novalidate clause works throughout the lifetime of the index.

But...I'm not known for my very good reading skills, so if I still misunderstand the problem (or my interpretation of it), I will try to shut up (in this post at least Smile

)

Report message to a moderator

Re: getting error on enabling constraints even using novalidate clause [message #603980 is a reply to message #603978]

Sat, 21 December 2013 16:48

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Aaaaaand that's the bit that I missed! Smile

Point 9, enabling the PK without disabling it i.e. the OP appears to be saying that they only disabled the check constraint, rather than disabling the pk constraint. If that were indeed the case then the failure would have occurred at the second insert, and the enable novalidate would in fact have worked. Instead, I think that the op has introduced a typo (ck for pk) or has missed out the disabling of the pk and uk constraints (more likely). Now if we take the second scenario (i.e the op actually disables the ck and pk constraints) then the code would fail at the 'enable novalidate' statement. It appears that the OP wants to be able to have rows that violate the constraints but still have a constraint enabled. In this way, only Littlefoot's post is relevant. Novalidate is extremely useful if you need to enforce subsequent dml on the table adheres to the constraints whilst you deal with the constraint exceptions (which you can get by using the exceptions clause in the alter table statement) but t can only be done if a non unique index is used (as LF showed us).

Report message to a moderator

Re: getting error on enabling constraints even using novalidate clause [message #604028 is a reply to message #603965]

Mon, 23 December 2013 03:25

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

evivek wrote on Sat, 21 December 2013 14:49

even I am using novalidate clause still getting error like below

ORA-02437: cannot validate (SCOTT.PK) - primary key violated

When you create a primary key, implicitly an unique index is created. And disabling the constraint will drop this unique index.

However, Oracle behaves differently when there already exists an index(non-unique). As Littlefoot demonstrated, now this non-unique index would be used and thus duplicates can be entered.

So, the correct way is :

SQL> drop TABLE STUDENT_DETAILS;
 
Table dropped
SQL> CREATE TABLE STUDENT_DETAILS
  2  (
  3  ROLLNO NUMBER,
  4  NAME VARCHAR2(50 BYTE),
  5  CITY VARCHAR2(20 BYTE),
  6  MARKS NUMBER,
  7  INVOICENO CHAR(1 BYTE)
  8  );
 
Table created
SQL> alter table student_details
  2  add (
  3  constraint uk unique(invoiceno),
  4  constraint ck check(marks>0))
  5  modify name not null modify city default 'pune';
 
Table altered
SQL> create index tbl_pk ON student_details(rollno);
 
Index created
SQL> alter table student_details add constraint pk primary key (rollno) disable keep index;
 
Table altered
SQL> insert into student_details(rollno,name,marks,invoiceno)
  2  values(2,'Raj',100,'z');
 
1 row inserted
SQL> alter table student_details
  2  disable constraint uk;
 
Table altered
SQL> alter table student_details
  2  disable constraint ck;
 
Table altered
SQL> alter table student_details
  2  modify name null modify city default NULL;
 
Table altered
SQL> insert into student_details(rollno,name,marks,invoiceno)
  2  values(2,'Raj',100,'z');
 
1 row inserted
SQL> SELECT * FROM student_details;
 
    ROLLNO NAME                                               CITY                      MARKS INVOICENO
---------- -------------------------------------------------- -------------------- ---------- ---------
         2 Raj                                                pune                        100 z
         2 Raj                                                                            100 z

A similar question was posted recently here

Report message to a moderator

Previous Topic:	Help required to understand sys refcursor
Next Topic:	Adding column to the table is taking long time

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 07:57:07 CDT 2024