Not Null on Primary Key???
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 31

Thread: Not Null on Primary Key???

  1. #1
    Join Date
    Aug 2002
    Posts
    24

    Not Null on Primary Key???

    I'm designing a new physical data model using a third party tool. When I generate the database creation script automatically it puts 'not null' constraints on the primary keys. I don't have much experience with this particular tool and can't find the correct setting to stop this.

    The extra 'not null' constraints are redundant but what would be the problem if I just leave them? For example, would the 'not null' condition be checked twice for every insert or update?

    Any ideas? I could always just edit the script...
    Dave Peacock
    Oracle DBA
    Leeds, England

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    PKzzz defualtly will have Not Null constraints....

    I dont understand wat are you talking about.
    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"

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    if a not null constraint is being created in addition to the primary key, then you can drop it. Yes, it will be checking not null twice.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455
    Just like Erwin, when you create a PK on a column it creates a NOT NULL.

    PK has to be NOT NULL.

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    yes, but not null is inherent in the PK constraint -- it shold not be created seperately
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    You cannot alter table column to not null or null if that column is a PRIMARY key. It is by default a not null if it has a primary key constraint on it.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    yes you can ...

    SQL> create table my_table (my_column number primary key);

    Table created.

    SQL> desc my_table
    Name Null? Type
    ----------------------------------------- -------- ----------------------

    MY_COLUMN NOT NULL NUMBER

    SQL> alter table my_table modify (my_column not null);

    Table altered.
    SQL> alter table my_table modify (my_column null);

    Table altered.

    SQL> alter table my_table modify (my_column null);
    alter table my_table modify (my_column null)
    *
    ERROR at line 1:
    ORA-01451: column to be modified to NULL cannot be modified to NULL

    SQL> desc my_table
    Name Null? Type
    ----------------------------------------- -------- -------------------

    MY_COLUMN NOT NULL NUMBER


    Note that it is the second modify to not null that fails, but not because o the PK definition ...

    SQL> drop table my_table;

    Table dropped.

    SQL> create table my_table (my_column number null);

    Table created.

    SQL> alter table my_table modify (my_column null);
    alter table my_table modify (my_column null)
    *
    ERROR at line 1:
    ORA-01451: column to be modified to NULL cannot be modified to NULL
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Aug 2002
    Posts
    24

    Question Example of table creation...

    Example:

    create table TBL_PERSONWORK (
    PERSONID NUMBER(9) not null,
    LIMIT NUMBER(3),
    PEREVERY NUMBER(3),
    DAP NUMBER(5),
    HAZARD NUMBER(1),
    constraint PK_PERSONWORK primary key (PERSONID)
    using index
    tablespace INDX
    );

    So the 'not null' on PERSONID adds an extra not null constraint when this is already covered by the PK constraint?

    Slimdave - Thanks for your help.

    Cheers
    Dave Peacock
    Oracle DBA
    Leeds, England

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253

    Re: Example of table creation...

    So the 'not null' on PERSONID adds an extra not null constraint when this is already covered by the PK constraint?
    Correct, it does, and this will be an overhead on insert.

    SQL> create table my_table (my_column number not null primary key);

    Table created.

    SQL> select constraint_type from user_constraints
    2 where table_name = 'MY_TABLE';

    C
    -
    C
    P

    SQL> alter table my_table modify (my_column null);

    Table altered.

    SQL> select constraint_type from user_constraints
    2 where table_name = 'MY_TABLE';

    C
    -
    P

    SQL> desc my_table;
    Name Null? Type
    ----------------------------------------- -------- --------------------

    MY_COLUMN NOT NULL NUMBER


    Your tool may have a configuration setting to prevent such an unfortunate double-constraint.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I just thought of a reason why a designer might want to keep the not null on there actually.

    Suppose you were defining a table for which the PK had to be deferrable -- you might want the "unique" part of the PK to be deferrable, but keep the "not null" part enforced. In such a case you would have to keep a seperate not null constraint on the table as well as the deferrable PK.

    Probably not a very common case, I would think. The only situation i can think of for having a deferrable pk would be on a fast-refresh materialized view -- i believe that deferrable pk's are a benefit there because Oracle does not guarantee uniqueness on a PK or Unique column list while the refresh is taking place.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

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