Home » SQL & PL/SQL » SQL & PL/SQL » Constraint Issue (merged) (Oracle 9i)
Constraint Issue (merged) [message #444658] Tue, 23 February 2010 05:38 Go to next message
ramesh55.sse
Messages: 262
Registered: December 2008
Location: Hyderabad
Senior Member
Hi All,


sno sname
1    Ram
2    Sym
     Kishor
     Sarat
5   Suresh
6   Raj
    Ramesh
    Ramana
8   Kumar
9   Ram



In this data sno column is having Null values.
Now i want to add Not NULL constraint on this column.
i.e. In the future the SNO column won't accept NULL values.

PLease help me.
Thanks in advance.
Re: Constraint [message #444659 is a reply to message #444658] Tue, 23 February 2010 05:41 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Update the null values to non-null values.
Add constraint.

What's the problem?
Re: Constraint [message #444661 is a reply to message #444658] Tue, 23 February 2010 05:56 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And before you explain what is the problem, prepare a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.

Regards
Michel
Re: Constraint [message #444662 is a reply to message #444658] Tue, 23 February 2010 06:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If what you're looking for is a way to prevent additional nulls being stored in the SNO column, without requiring you to fix the data that you've got in there, then this is a way of doing it:
create table test_145 (col_1 number, col_2 varchar2(30));

insert into test_145 values (1 ,   'Ram');
insert into test_145 values (2 ,   'Sym');
insert into test_145 values ('' ,   'Kishor');
insert into test_145 values ('' ,   'Sarat');
insert into test_145 values (5 ,   'Suresh');
insert into test_145 values (6 ,   'Raj');
insert into test_145 values ('' ,   'Ramesh');
insert into test_145 values ('' ,   'Ramana');
insert into test_145 values (8 ,   'Kumar');
insert into test_145 values (9 ,   'Ram');

alter table test_145 add (constraint test_145_nn01 check(col_1 is not null) enable novalidate);

insert into test_145 values (null,'John');
Re: Constraint [message #444666 is a reply to message #444662] Tue, 23 February 2010 07:05 Go to previous messageGo to next message
ramesh55.sse
Messages: 262
Registered: December 2008
Location: Hyderabad
Senior Member
Thanks for your responce it's working fine.
Re: Constraint [message #444667 is a reply to message #444666] Tue, 23 February 2010 07:12 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You clearly see that you have data that do not satisfy the constraint, don't you.

Regards
Michel
Re: Constraint [message #444668 is a reply to message #444658] Tue, 23 February 2010 07:34 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
I've never thought using novalidate like this is a good idea. Novalidate is just telling oracle - trust me the data is correct.
If the data actually isn't correct then it can lead to bugs - certainly if you do it for indexes - see this thread select - various count for an example.
I can't think of any way it'd break using a not null constraint but that doesn't mean that no cases exist.
Remember that the optimiser can use constraints to short-cut steps in queries.
Re: Constraint [message #444670 is a reply to message #444658] Tue, 23 February 2010 07:45 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Quote:
alter table test_145 add (constraint test_145_nn01 check(col_1 is not null) enable novalidate);


Then why we need not null ? directly we can use novalidate or to reduce even one constraint keep it null..

In My view To Keep NOT NULL and enable novalidate contradict each other !!

Any thoughs?
Re: Constraint [message #444674 is a reply to message #444668] Tue, 23 February 2010 07:51 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I can't remember whether the CBO trusts NOVALIDATE constraints - I rather think it doesn't.

But I quite agree - except in a few cases where you've got large amounts of old data that you can't easily correct, I wouldn't use this solution.
Re: Constraint [message #444675 is a reply to message #444658] Tue, 23 February 2010 07:55 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
if we put not null then irrespective we use NOVALIDATE or NOT database will always thorw error for null value insertd is it ?

Re: Constraint [message #444678 is a reply to message #444674] Tue, 23 February 2010 08:01 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
JRowbottom wrote on Tue, 23 February 2010 13:51
I can't remember whether the CBO trusts NOVALIDATE constraints - I rather think it doesn't.

I suspect it doesn't as well, there might be other nasty side effects though.

JRowbottom wrote on Tue, 23 February 2010 13:51

But I quite agree - except in a few cases where you've got large amounts of old data that you can't easily correct, I wouldn't use this solution.


Indeed - normally this approach is only recommended for data warehouse loads where you can guarantee the data is going to conform to the constraint.

rahulvb wrote on Tue, 23 February 2010 13:55

if we put not null then irrespective we use NOVALIDATE or NOT database will always thorw error for null value insertd is it ?

Yes you'll get an error. novalidate only allows nulls in the existing data not in any new data.
Re: Constraint [message #444680 is a reply to message #444678] Tue, 23 February 2010 08:07 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
cookiemonster wrote on Tue, 23 February 2010 08:01
Yes you'll get an error. novalidate only allows nulls in the existing data not in any new data.


Ok. In this case This is very Useful that we have Huge data and now business wants its as Not null so form This point onwards we will check for nulls

Good way to ignore existing nulls.

Thanks @ cookiemonster



Re: Constraint [message #444681 is a reply to message #444674] Tue, 23 February 2010 08:13 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
JRowbottom wrote on Tue, 23 February 2010 13:51
I can't remember whether the CBO trusts NOVALIDATE constraints - I rather think it doesn't.

Actually, according to this asktom thread the CBO can trust novalidate constrints if query_rewrite_integrity is set to enforced.
Re: Constraint [message #444682 is a reply to message #444680] Tue, 23 February 2010 08:15 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
rahulvb wrote on Tue, 23 February 2010 14:07
cookiemonster wrote on Tue, 23 February 2010 08:01
Yes you'll get an error. novalidate only allows nulls in the existing data not in any new data.


Ok. In this case This is very Useful that we have Huge data and now business wants its as Not null so form This point onwards we will check for nulls

Good way to ignore existing nulls.

Thanks @ cookiemonster



My basic point is that there is no good way to ignore existing nulls. This will do it but it might have nasty side effects.
Re: Constraint [message #444685 is a reply to message #444681] Tue, 23 February 2010 08:20 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
cookiemonster wrote on Tue, 23 February 2010 15:13
JRowbottom wrote on Tue, 23 February 2010 13:51
I can't remember whether the CBO trusts NOVALIDATE constraints - I rather think it doesn't.

Actually, according to this asktom thread the CBO can trust novalidate constrints if query_rewrite_integrity is set to enforced.

It also depends on the RELY value.
From SQL Reference (constraint page):

Quote:
These parameters [MC: RELY/NORELY] 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.

Regards
Michel

[Updated on: Tue, 23 February 2010 08:21]

Report message to a moderator

Re: Constraint [message #444693 is a reply to message #444685] Tue, 23 February 2010 09:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I wasn't certain whether RELY/NORELY had an effect beyond Query Rewrite.

Constraint Issue [message #444706 is a reply to message #444658] Tue, 23 February 2010 11:18 Go to previous messageGo to next message
ramesh55.sse
Messages: 262
Registered: December 2008
Location: Hyderabad
Senior Member
Hi All,

I have created a table like this

CREATE TABLE table2(a NUMBER CONSTRAINT a_pk PRIMARY KEY);

INSERT INTO table2 VALUES(1);
INSERT INTO table2 VALUES(2);

After that i disabled the primary key like this

ALTER TABLE table2 DISABLE CONSTRAINT a_PK;

Then i inserted duplicate values
INSERT INTO table2 VALUES(1);
INSERT INTO table2 VALUES(1);

Now i want to restrict duplicate values in to that column.
for that i have used

ALTER TABLE pt MODIFY CONSTRAINT pt_apk ENABLE NOVALIDATE

cannot validate (RAMESH.a_pk) - primary key violated.

How can we enable the key.
Please help me.
Thanks in advance
Re: Constraint Issue [message #444707 is a reply to message #444706] Tue, 23 February 2010 11:24 Go to previous messageGo to next message
ThomasG
Messages: 3184
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Delete the duplicate values first.
Re: Constraint Issue [message #444708 is a reply to message #444706] Tue, 23 February 2010 11:29 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is the same question than your previous one for unique constraint instead of check one. I merge both.

Regards
Michel

[Updated on: Tue, 23 February 2010 11:36]

Report message to a moderator

Re: Constraint Issue (merged) [message #444709 is a reply to message #444658] Tue, 23 February 2010 11:32 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
And if you'd read the asktom thread I linked to earlier you would have found out how to do this, along with a load of information as to why it is a bad idea.
Re: Constraint Issue [message #444710 is a reply to message #444706] Tue, 23 February 2010 11:35 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To be able to enabling the constraint you must first:
1/ drop the constraint
2/ create a non-unique index

SQL> CREATE TABLE table2(a NUMBER CONSTRAINT a_pk PRIMARY KEY);

Table created.

SQL> INSERT INTO table2 VALUES(1);

1 row created.

SQL> INSERT INTO table2 VALUES(2);

1 row created.

SQL> alter table table2 DROP primary key;

Table altered.

SQL> INSERT INTO table2 VALUES(1);

1 row created.

SQL> INSERT INTO table2 VALUES(1);

1 row created.

SQL> -- Trying to directly recreate the PK fails because Oracle tries to create a unique index
SQL> ALTER TABLE table2 add primary key (a) novalidate;
ALTER TABLE table2 add primary key (a) novalidate
*
ERROR at line 1:
ORA-02437: cannot validate (MICHEL.SYS_C006569) - primary key violated

SQL> -- create the index first
SQL> create index table2_i on table2 (a);

Index created.

SQL> ALTER TABLE table2 add primary key (a) novalidate;

Table altered.

Hoping you didn't forget the warnings we gave you.

Regards
Michel

[Updated on: Tue, 23 February 2010 11:37]

Report message to a moderator

Re: Constraint Issue [message #444811 is a reply to message #444710] Wed, 24 February 2010 05:38 Go to previous messageGo to next message
ramesh55.sse
Messages: 262
Registered: December 2008
Location: Hyderabad
Senior Member
HI All,
I don't want to drop the constraint.I want to disable the constraint.Without dropping the constraint can't we do that.

Thanks & regards
Ramesh
Re: Constraint Issue (merged) [message #444814 is a reply to message #444658] Wed, 24 February 2010 05:47 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
yes you can

alter table
   table_name
ENABLE constraint
   constraint_name;

alter table
   table_name
DISABLE constraint
   constraint_name;


People Around this forum would appreciate if you Google the the solution.


Re: Constraint Issue [message #444820 is a reply to message #444811] Wed, 24 February 2010 05:56 Go to previous message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ramesh55.sse wrote on Wed, 24 February 2010 12:38
HI All,
I don't want to drop the constraint.I want to disable the constraint.Without dropping the constraint can't we do that.

Thanks & regards
Ramesh

Did you try?

Hint: take care of the index.

Regards
Michel

Previous Topic: count for null year
Next Topic: highest value
Goto Forum:
  


Current Time: Mon Sep 26 16:17:56 CDT 2016

Total time taken to generate the page: 0.16036 seconds