Re: Attribute Level Constraint

Khurram wrote:
> Thanx andrew for yours coperation
>
> > There is a subtle but real difference between NULL and mytype(NULL,NULL).??
>
> Please would you tell me whats the diffrence between NULL and mytype
> (NULL,NULL)??
>
>
> SQL> CREATE TYPE mytype AS OBJECT
> 2 (fname VARCHAR2(10),
> 3 lname VARCHAR2(10))
> 4 .
> SQL> /
>
> Type created.
>
> SQL> CREATE TABLE t (id NUMBER,name MYTYPE);
>
> Table created.
>
> SQL> ALTER TABLE t MODIFY (name CONSTRAINT t_name_nn NOT NULL);
>
> Table altered.
>
> SQL> INSERT INTO t VALUES (1,NULL);
> INSERT INTO t VALUES (1,NULL)
> *
> ERROR at line 1:
> ORA-01400: cannot insert NULL into ("SCOTT"."T"."NAME")
>
>
> SQL> INSERT INTO t VALUES (1,mytype(NULL,NULL));
>
> 1 row created.
>
> I am still confused why it let go (NULL,NULL) value while the
> attributes are masked by NOT NULL at the time of table creation?
>
> Khurram

mytype(null,null) would result in a pointer to be placed in the table column Name
which would point to this (null,null) row. That is not the same as putting a null value in the column.

Now that is another point as to whether or not oracle should have treated
mytype(null,null) as being equivalent to null. However, this is how the behavior is.

Similar thing happens for clobs:
ORA92> create table v (a clob not null);

Table created.

ORA92> insert into v values (empty_clob());

1 row created.

ORA92> insert into v values (null);
insert into v values (null)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("AVARMA"."V"."A")

Anurag Received on Fri Jun 23 2006 - 08:59:31 CDT