PK and NOT NULL

[abhaysk]

Hi All,

Today i was amused to observe one thing and i was surprised ( or may be its too simple and missing something )..

I have a table X and PK_X is the Primary Constraint on it & its using the index PK_X..

When i did "Select Count(*) From X" it was always going for Table Scan & and even after giving Index_FFS hint it went for Table Scan..

So i guessed it was something got to do with "NOT NULL"..

To my surprise its the culpirt.. But i wonder how on earth a Primary Key built on a table will not have a "NOT NULL" constraint for the columns involved in it.

WW04_PRO> select CONSTRAINT_NAME, Constraint_Type, TABLE_NAME, DEFERRED, INDEX_NAME
  2        from dba_constraints where table_name = 'RLSCSHEV';

CONSTRAINT_NAME    CONSTRAINT_TYPE    TABLE_NAME DEFERRED  INDEX_NAME
------------------ ------------------ ---------- --------- -------------
PK_RLSCSHEV        P                  RLSCSHEV   IMMEDIATE PK_RLSCSHEV

-- Check out the constraint type, its P!

WW04_PRO> select position, column_name from dba_cons_columns where constraint_name = 'PK_RLSCSHEV';

 Pos COLUMN_NAME
---- ----------------------------------------
   1 SALES_ORGANIZATION_CD
   2 SHIPMENT_NO
   3 PARTNER_FUNCTION_CD
   4 SOURCE_NM

WW04_PRO> select TABLE_NAME, COLUMN_NAME, NULLABLE from dba_tab_columns where table_name = 'RLSCSHEV' and
  2        Column_Name in (Select Column_Name From dba_cons_columns where constraint_name = 'PK_RLSCSHEV');

TABLE_NAME                     COLUMN_NAME                              N
------------------------------ ---------------------------------------- -
RLSCSHEV                       SALES_ORGANIZATION_CD                    Y
RLSCSHEV                       SHIPMENT_NO                              Y
RLSCSHEV                       PARTNER_FUNCTION_CD                      Y
RLSCSHEV                       SOURCE_NM                                Y


-- OMG, The columns of primary key allow null??

WW04_PRO> insert into rlscshev(SALES_ORGANIZATION_CD, SHIPMENT_NO, SOURCE_NM, PARTNER_FUNCTION_CD)
  2        values('33SC', '0080195315', null, null);
insert into rlscshev(SALES_ORGANIZATION_CD, SHIPMENT_NO, SOURCE_NM, PARTNER_FUNCTION_CD)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("WWCIW_SAP"."RLSCSHEV"."PARTNER_FUNCTION_CD")

-- But, interestingly it does not if we try to

Can anyone tell me wass going on??

Regards
Abhay.