PK and NOT NULL
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: PK and NOT NULL

  1. #1
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434

    PK and NOT NULL

    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.

    Code:
    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.
    Last edited by abhaysk; 05-07-2004 at 08:29 AM.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Hi Abhay,
    Did the index exist before the PK constraint was added? The PK is handled a bit differently if you do that.

  3. #3
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by DaPi
    Hi Abhay,
    Did the index exist before the PK constraint was added? The PK is handled a bit differently if you do that.
    No, index was created along with PK.. See below DDL

    Code:
    CREATE TABLE RLSCSHEV
    (
    SALES_ORGANIZATION_CD           VARCHAR2(4)   ,
    SHIPMENT_NO                    	VARCHAR2(10)  ,
    PARTNER_FUNCTION_CD            	VARCHAR2(3)   ,
    SOURCE_NM                      	VARCHAR2(16)  ,
    EVENT_SPEC_SUPPLIER_NO         	VARCHAR2(10)  ,
    EVENT_SPEC_PARTNER_CONTACT_NO  	VARCHAR2(17)  ,
    EVENT_SPECIFIC_LANGUAGE_CD     	VARCHAR2(1)   ,
    EVENT_SPECIFIC_TITLE_TX        	VARCHAR2(15)  ,
    EV_SPEC_DOING_BUSINESS_AS_NM   	VARCHAR2(35)  ,
    EVENT_SPEC_CUSTOMER_LEGAL_NM   	VARCHAR2(35)  ,
    EV_SPEC_CUST_LEGAL_OVRFLW_NM   	VARCHAR2(35)  ,
    EVENT_SPEC_STREET_AD           	VARCHAR2(35)  ,
    EVENT_SPEC_POSTAL_CD           	VARCHAR2(10)  ,
    EVENT_SPEC_CITY_NM             	VARCHAR2(35)  ,
    EVENT_SPEC_POST_OFFICE_BOX_TX  	VARCHAR2(10)  ,
    EVENT_SPEC_TELEPHONE_NO        	VARCHAR2(30)  ,
    EVENT_SPEC_FAX_NO              	VARCHAR2(31)  ,
    EVENT_SPEC_PO_BOX_POSTAL_CD    	VARCHAR2(10)  ,
    EVENT_SPEC_COUNTRY_CD          	VARCHAR2(3)   ,
    EVENT_SPEC_HOUSE_NO            	VARCHAR2(6)   ,
    EVENT_SPEC_REGION_CD           	VARCHAR2(3)   ,
    SOURCE_DT                      	VARCHAR2(15)  ,
    EVENT_SPEC_DISTRICT_CD         	VARCHAR2(35)  ,
    EVENT_SPEC_STREET_2_AD         	VARCHAR2(35)  ,
    EVENT_SPEC_STREET_AD_OVRFLWX   	VARCHAR2(35)  ,
    EVENT_SPEC_TELEX_NO            	VARCHAR2(30)  ,
    EVENT_SPEC_TAX_JURISDICTION_CD 	VARCHAR2(15)  ,
    CONSTRAINT PK_RLSCSHEV
    PRIMARY KEY ( SALES_ORGANIZATION_CD, SHIPMENT_NO, PARTNER_FUNCTION_CD, SOURCE_NM )
    	USING INDEX
    		TABLESPACE CAS_IDX_1 PCTFREE 10
    		STORAGE ( INITIAL 10M NEXT 10M PCTINCREASE 0 ))
    TABLESPACE CAS_TAB_1 NOLOGGING 
       PCTFREE 10
       PCTUSED 60
       INITRANS 1
       MAXTRANS 255
      STORAGE ( 
       INITIAL 50M
       NEXT    50M
       MINEXTENTS 1
       PCTINCREASE 0
       MAXEXTENTS 255
       FREELISTS 1 FREELIST GROUPS 1 )
       NOCACHE;
    However, when Primary Key is added later using Alter Command, it will check for any null values and if not found any then update the column and make it "NOT NULL"

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Can you reproduce this? (I couldn't - though I didn't have much time . . . )

  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by DaPi
    Can you reproduce this? (I couldn't - though I didn't have much time . . . )
    I suerly cannot reproduce what problem i am seeing.. & i can tell you this problem is only seen for 5 objects.. I wil try to find out the root cause..

    Any way i can show you one thing that "It does not really make any difference if PK is enforced before or after the index is created on the column list"

    BTW, Jurij/Dave/Pando any ideas why this happened?

    (To just mention, recently we had a crash of our Primary DB and hence switched to Standby).

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    It's quite simple, nothing misterious here. You have created this constraint as deferrable - look at the vaue of column DEFERRED in your DBA_CONSTRAINT query!

    In this case columns belonging to PK are not implicitely set to NOT NULL by the constraint creation command, which is quite normal and logical. So even if your constraint checking is not deferred, the PK columns have no NOT NULL condition. As a side effect, optimizer will not recognize an index scan to be a viable path to satisfy your query and will insist in table scan. If you want to avoid this you'll either have to set this PK to NOT DEFERRABLE or to add column NOT NULL constraints explicitely.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by jmodic
    It's quite simple, nothing misterious here. You have created this constraint as deferrable - look at the vaue of column DEFERRED in your DBA_CONSTRAINT query!
    Jurij,

    I am not sure if you have seen the DEFFERED column i posted, but let me post it again.

    Code:
    WW04_PRO> ed
    Wrote file afiedt.buf
    
      1  Select
      2    CONSTRAINT_NAME, Constraint_Type, DEFERRABLE, DEFERRED
      3  from
      4    dba_constraints
      5  where
      6*   table_name = 'RLSCSHEV'
    WW04_PRO> /
    
    CONSTRAINT_NAME           CONSTRAINT_TYPE DEFERRABLE     DEFERRED
    ------------------------- --------------- -------------- ---------
    PK_RLSCSHEV               P               NOT DEFERRABLE IMMEDIATE
    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by abhaysk
    Jurij,

    I am not sure if you have seen the DEFFERED column i posted, but let me post it again.
    Oh yes, I have seen it, it said IMMEDIATE. However you have not posted the DEFERRABLE column in your first post as you did now. So I had every reason to belive your PK was deferrable.

    Well, PK constraint being defined as deferrable is my only explanation about PK columns not having NOT NULL constraint.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Ok thas fine, I think i should have posted Defferable column as well..

    But now i know that this table was built with NON DEFFERABLE & it still says so, how can we reason the issue now.

    Well, let me give you this info..

    1) Our Primary DB crashed on 02-May-04.
    2) Till 02-May we had the NULLABLE as "N" in our primary db.
    3) Since our primary had some hardware issues, we brought up the Standby.
    4) In standby we are seeing this strange thing.
    5) Intersetingly this issue is seen only on 4 tables which were created with NOLOGGING option.

    But i believe all DDL will be logged since DDL is a updation of SYS base tables and so & all such sys objects will be logging mode..
    So why is this definition of "NULLABLE" differing from primary?

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    I think it's time to raise a TAR.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width