var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: My first post - Need help with surrogate key table design
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
Here, item_id is the sequence generated primary key and the logical primary key is item_code, defined with a unique index.
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)
So my UI should never allow me to change item_code.
I also have another table called catalog defined as
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.
does changing a PK value make sense? only if your business rules allow it
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.
did not know that
Originally Posted by
oracle slips yet another notch on my respect scale
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.