Attribute Level Constraint

From: Khurram <oraware_at_gmail.com>
Date: 23 Jun 2006 03:21:31 -0700
Message-ID: <1151058091.755953.220270@i40g2000cwc.googlegroups.com>

hi

As documented

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/clauses3a.htm

Restrictions on NOT NULL Constraints

-You cannot specify NULL or NOT NULL in a view constraint.
-You cannot specify NULL or NOT NULL for an attribute of an object.
Instead, use a CHECK
constraint with the IS [NOT] NULL condition.

Its ok You cannot specify NULL or NOT NULL for an attribute of an object

SQL> CREATE TYPE mtype AS OBJECT

         (fname VARCHAR2(10) NOT NULL,
          lname  VARCHAR2(10))

SQL> / Warning: Type created with compilation errors.

SQL> CREATE TYPE mytype AS OBJECT

         (fname VARCHAR2(10),
          lname VARCHAR2(10))

  2 .
SQL> / Type created.

SQL> CREATE TABLE t (id NUMBER,name MYTYPE NOT NULL);

Table created.

SQL> INSERT INTO t VALUES (1,mytype('KHURRAM','SIDDIQUI'));

1 row created.

SQL> INSERT INTO t VALUES (2,mytype('SHAHZAD',NULL));

1 row created.

SQL> INSERT INTO t VALUES (2,mytype(NULL,NULL));

1 row created.

SQL> SELECT id,e.name.fname,e.name.lname FROM t e;

        ID NAME.FNAME NAME.LNAME

• --------------------- ----------------------- 1 KHURRAM SIDDIQUI 2 SHAHZAD 2

My question Why it is accepting NULL values though i have declared NOT NULL to table
column name??

name MYTYPE NOT NULL

Khurram Received on Fri Jun 23 2006 - 05:21:31 CDT