Results 1 to 9 of 9

Thread: primary key?

  1. #1
    Join Date
    Sep 2010
    Posts
    36

    Unanswered: primary key?

    If not implementing referential integrity, when does it make sense to create a primary key? If it's just to ensure uniqueness, then a unique index should be enough?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    essentially a primary key is a unique key
    there can be one or more unique keys on a table, but there can only ever be one primary key in force at any one time. well strictly speaking no more than one primary index as there is no compulsion to define a primary key assuming you prefer enforcing the RI constraints through code as opposed through database declaration
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2012
    Posts
    156
    Biggest difference I have seen (outside of referential integrity) is the columns in a primary key must be defined as not null, whereas with a unique index you can have 1 unique value that contains nulls. IE. Unique index containing 1 column, will allow 1 null value. Of course if your columns on the table are already defined as not null this would not really be an issue, which for a unique index is normally the case.

  4. #4
    Join Date
    Sep 2010
    Posts
    36
    Still unclear if there is any benefit in creating a primary key if not implementing referential integrity? If we create a unique index, the columns that make up this index will be defined as not null.

    Another thing is I've heard that performance indexes are usually non-unique. Is this a valid statement?

  5. #5
    Join Date
    Apr 2012
    Posts
    156
    If not using referential integrity and columns are defined as not null no need for the primary key. Not sure what you mean by performance indexes. I have always gone with if the columns in the index are unique, then define it as unique. If they are not unique then define as not unique. Performance would be the same in both cases.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by shamikp View Post
    when does it make sense to create a primary key?
    Every time you create a table for an entity that can be uniquely identified, that is, almost always. The fact that in some databases, including DB2, it is automatically backed by a unique index is a side effect. Its primary purpose is to declare the attribute that allows you to distinguish one record from another.

    Your employer's business analysts, data modellers, developers, and DBAs will thank you for declaring primary keys long after you're fired for asking questions like this on a public forum.
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Sep 2010
    Posts
    36
    Quote Originally Posted by n_i View Post
    Every time you create a table for an entity that can be uniquely identified, that is, almost always. The fact that in some databases, including DB2, it is automatically backed by a unique index is a side effect. Its primary purpose is to declare the attribute that allows you to distinguish one record from another.

    Your employer's business analysts, data modellers, developers, and DBAs will thank you for declaring primary keys long after you're fired for asking questions like this on a public forum.

    I won't get fired for asking this question. Why would I, heh?

    If the table is not part of RI, has a unique index and all columns are defined as not null, then why create PK? If PK is required in order "to declare the attribute that allows you to distinguish one record from another", DB2 has to enforce it.

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by shamikp View Post
    I won't get fired for asking this question. Why would I, heh?

    If the table is not part of RI, has a unique index and all columns are defined as not null, then why create PK? If PK is required in order "to declare the attribute that allows you to distinguish one record from another", DB2 has to enforce it.
    Typically, I create the unique index first, and then after that the PK constraint with same name as index (and DB2 will use the existing index to enforce the uniqueness). This allows for things like INCLUDE clause in the index for other columns that do not define uniqueness, but may help performance if they are in the same index (typically by allowing index only access).

    If it is logically the PK, then it doesn't hurt, and may help, to go ahead and define the PK.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by shamikp View Post
    why create PK?
    Primary key is a logical concept. If the primary key exists for a given entity, it exists regardless of whether you want it or not. By not declaring it explicitly you are just hiding its existence from people and tools that might make better use of this knowledge than you.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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