Constraints validated

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

astalavista - 30 Jan 2006 20:31 GMT

Hi,

I have a table with a constraint NOT NULL
wich is enable, validate,
but there is some rows with a null value.
How is it posssble ?
I thought all my data was NOT NULL
if the constraint is validate.

I did disable
and after I can't enable it
which is OK

Something I didn't understand ???

Oracle 9.2.05 AIX 5.1

Thanks in advance

Reply to this Message

ajay - 30 Jan 2006 23:00 GMT

Even I do not understand this.If you have defined a column not null one
cannot insert null values

.But, there is an option where one can use the keyword NOVALIDATE

This means that one can create a table with a column which has no
constraints( in our case its not null constraint) insert null values
and then add the constarint
with the keyword NOVALIDATE this will make sure that it will not
validate any previous null values in the column and will start with the
latest one.

This is the only possible way that one can have null values in not null
column

Example:
alter table aa1 modify id number constraint NN not null NOVALIDATE

Table aa1 was first cretaed with no constraints and a null value was
inserted and tehn constraint was added with the above syntax.

Reply to this Message

Carlos - 31 Jan 2006 07:52 GMT

>>have a table with a constraint NOT NULL
>>wich is enable, validate,
>>but there is some rows with a null value.
>>How is it posssble ?

The constraint may be INVALID.

>>I did disable
>>and after I can't enable it
>>which is OK

>>Something I didn't understand ???

When you disabled it an then tried to enable it the constraint tried to
become VALID but the data whithin the column made it impossible.

HTH

Cheers.

Carlos.

Reply to this Message