Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2010
    Posts
    30

    Unanswered: DB2 Z/os V8 - Primary Key

    Hi , Is there any advantage or purpose of defining a Primary Key column as ' not null with default ' . My understanding is all primary key columns should be defined as ' Not null' only.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    According to some, you don't need no stinkin' primary keys at all.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There are a myriad of different issues that come with a default. In my opinion, most of them are bad.

    Adding the default allows you to insert one row at a time using the default value. This can be a mitigating work-around when there is a problem with third party software that doesn't reference the column since it allows you a tiny sliver of "breathing room" to work around those limitations.

    Depending on your use case, there almost has to be a better solution. If you can explain what you are doing (or trying to accomplish) I'm sure that someone can help you find a better answer.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Since a PK must be unique, you might run into problems if the same default value is used for more than one row, even if the PK is made up of multiple columns. That is the main reason for not using NOT NULL WITH DEFAULT on PK's.

    However, there are exceptions, such as if a TIMESTAMP column is the PK (or part of the PK), and it is supposed to represent the current_timestamp when the row is inserted, then NOT NULL WITH DEFAULT is probably acceptable (or even advisable). There might some other exceptions also, but the main thing is make sure the PK is unique.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Sep 2010
    Posts
    30
    Thanks for both of your valuable advice. I am going through a modification request for a new Table and come across single column(CHAR) primary key with 'Not NULL with default' which I thought was not correct but need the second opinion.

    However I thought If the primary key(CHAR) define as ' NOT NULL WITH DEFAULT', it is possible to insert/load only a single record with 'SPACE' for the primary key column due to uniqueness. But what you are saying is one can insert one row at a time if define as default. Does it mean that a unique key does not maintain for defaults like space and zeros please ?

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    No, a DEFAULT will provide a value if none is specified in an INSERT or MERGE statement. It will not allow rows with duplicate values for a PK (primary key) to be added, but it will permit a row to be added as long as no row exists with the default value.

    This is a marginal case, but when a PK column is added to the schema of a table that can't be changed (for example it is provided by a vendor) the DEFAULT will allow single row inserts. You can then add code (such as a trigger) that will note the added row, assign a new PK, and perform additional processing as needed.

    This was once (long ago) a common work around for z/OS DB2 (Mainframe) to allow commercial product schemas to be extended or integrated with existing (home grown) software.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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