Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2008
    Location
    Monroe, NY
    Posts
    2

    Unanswered: My first post - Need help with surrogate key table design

    Hello everyone,

    I discovered this forum the good old fashioned way - google

    I am a Java developer mostly working with Oracle databases. In fact, prior to doing mainly Java web stuff, I did a lot more Oracle backend procedures.

    I am new to the whole idea of surrogate keys for my table, so my knowledge is a little fuzzy.

    I have a table item defined as

    Code:
    CREATE TABLE ITEM
    (
      ITEM_ID               INTEGER                 NOT NULL,
      ITEM_CODE        VARCHAR2(30 BYTE)       NOT NULL,
      MODEL_NAME            VARCHAR2(255 BYTE)      NOT NULL,
      MODEL_DESCRIPTION     VARCHAR2(255 BYTE)
    );
    Here, item_id is the sequence generated primary key and the logical primary key is item_code, defined with a unique index.

    So my UI should never allow me to change item_code.


    I also have another table called catalog defined as
    Code:
    CREATE TABLE CATALOG
    (
      CATALOG_ID     INTEGER                        NOT NULL,
      CATALOG_NAME   VARCHAR2(50 BYTE)              NOT NULL
    );

    catalog_id is the sequence generated surrogate key and catalog_name is unique and enforced with a unique key index ( like item)

    However tn this case, the UI should allow for the catalog name to be changed.


    This is where I am confused. Is the scenario for catalog a reasonable use case - meaning candidate primary key can be changed.

    Hope I have made sense.

    Thanks
    Franco

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    does changing a PK value make sense? only if your business rules allow it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    As Rudy says, there is no hard and fast rule that says you can't change the value of unique identifiers. One of the common purposes of surrogare keys is to make this easier, as the DBMS won't allow you to change the value of a unique identifier that is referenced by foreign keys (unless it supports "on update cascade" functionality, which Oracle doesn't). As long as you have no foreign keys that reference catalog_name you are fine; even if you do, updating is still possible but requires more work.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by andrewst
    (unless it supports "on update cascade" functionality, which Oracle doesn't)
    did not know that

    oracle slips yet another notch on my respect scale

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2008
    Location
    Monroe, NY
    Posts
    2
    I prefer to leave primary keys non-editable too.

    But in this case, the business required this name to be editable and since no no other foreign keys would reference catalog_name, this would be safe.

    Thanks for all your responses.

Posting Permissions

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