I agree that there is nothing wrong with using a natural key as primary. However, primary keys should be immutable, i.e. they should never change, ESPECIALLY if they are used as foreign keys in other tables. So if your natural key is subject to change, I advise you to make it an unique key, but not primary, and go ahead and add a surrogate primary key generated from a sequence.

"never ever never use char, only use varchar2."

Would you say that even when using just CHAR(1)?
What if the field is "guaranteed" to use the full width?

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1542606219593

He specifically addresses CHAR(1) vs VARCHAR2(1) here:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1542606219593#8216570788470

Thanks a lot for the guidance. What would be your advice on following scenario.
The format of the NATURAL Key (Which will be a Primary Key for the table) would be AAMMYY9999 for making it meaning ful to the end users
where AA is Alphabets
MM is the month
YY is the Year
9999 is the number generated from running sequence

In this case would the decision of having Natural Key as Primary Key would stand logical in terms of Performance aspect or Should a Surrogate Key be Introduced as Primary Key performance wise.

Please suggest.

Thanks

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:689240000346704229

Tom,

In my experience, I've found that if you have a complex enterprise application, it's better to avoid using natural keys as primary keys. This is because natural keys have real-world meaning, they stand for something, and users will eventually want to be able to change their value. In this example, are we 100% absolutely positive the date in the record's PK will never have to be adjusted?

Also, coming from the Java world, we try to avoid using anything but sequence-generated PKs because we use a popular object-relational mapping tool called Hibernate. It works infinitely better with sequence PKs, because it knows an object you're saving is "new" if and only if the PK is null.

Just my 2c.

Tom, thank you for responding. I was wondering about that, and what was the reason. Even when the reason is don't get used to it! :)

@Duke Ganote

I did a search and found the article, but i did not see the comments that specifically referred to CHAR and VARCHAR2(1). Thanx for pointing it out.

I love AskTom!

hi tom

in most of databases i have designed i found that the client has some sort of requirement in one or two place to identify a record with keys like AAMMYY-9999

where
AA is Alphabets
MM is the month
YY is the Year
9999 is the number generated from running sequence

and
sadly its been a common practice which sadly i also haven been practicing. but i still not manage to understand what problem it causes because if i use three attributes for it i still have to generate date and sequence

so there should not be any difference between

create table surprised(seq integer, pdate date, symbol varchar2(2) /*some other columns here*/);

alter table surprised
ADD CONSTRAINT looks_strange_pk PRIMARY KEY(seq ,pdate,symbol);

insert into surprised values(seq.nextval, sysdate, 'CS');

and

create table i_do(pkey varchar2(60) primary key, /* other keys*/);

insert into i_do values ('CS'||sysdate||seq.nextval);

Since the primary key does not changes i think it should not make any difference. If yes please make it clear with some example

In my experience, if you allow the users to see the key, they WILL eventually want to change it, despite having "complete" requirements, now. (Requirements change...)

Perhaps in this example, the components of the key are generated, but consider the corner cases

... time--> duplicates in the US when daylight savings time flips...

...in your case you have a sequence going to 9999. What happens if you get that many in one day?

...I have also seen cases where things that cannot happen in real life "happen' in the system. A company can't move from Canada to the US, but some minimum-wage data entry person can type in MB (Manitoba) instead of MN (Minnesota). As far as the database is concerned, the company just moved from Canada to the US.


Unless there is a real physical reason (and enforced by edit checks) that makes it unique, I usually make intelligent keys become unique keys, and create a surrogate sequence-based primary key.

Respected Mr. Tom,

At the outset, I am sorry to say something that may be against the current. In Design phase, we get stuck in identifying Primary key of client transaction table. The table structure looks like below. The bold columns have been identified as a composite primary key in Oracle 10g RAC.

Client Transaction Table

Clinent ID
VOUCHER_NO
TRN_NO
SRL_NO

GL_ID
TRN_DATE
FINANCIAL_YEAR
COMPANY_ID
REGION_ID
BRANCH_ID
BILL_TYPE
DR_AMOUNT
CR_AMOUNT
TAX_PERCENTAGE

At the time of transaction, it is okay but at the year end, the outstanding amount, if any, for every client or customer will be carried forwarded to the same table and the existing data will be removed.

During the year end process, we can easily populate data to all columns expect voucher_no, Trn_no and Srl_no because consolidated amount either Debit or Credit will be brought forwarded from the previous year transaction history. At this stage, predefined primary key is violated.

Other team members are suggesting now to introduce surrogate key (Sequence Number) as primary key but I could not find any value of surrogate key in transaction table as this key will not be used in generating report or SQL query on Sub Ledger wise payment or outstanding etc.

I request you to suggest how to overcome this problem. If you think, table design should be changed, please suggest. We are desperate in need of the solution.

Regards,

Audrey from DC posts: "In my experience, I've found that if you have a complex enterprise application, it's better to avoid using natural keys as primary keys. This is because natural keys have real-world meaning, they stand for something, and users will eventually want to be able to change their value. In this example, are we 100% absolutely positive the date in the record's PK will never have to be
adjusted?"

In an OLTP environment (does not apply for Dss/DW) the following:
One of two things going on here. First, for several very good reasons, of course a natural PK has a real-world meaning. If the meaning changes you have a new entity (table). You can't simply kidnap a natural PK when its meaning changes; you have to design a new entity (table). If a mistake is made entering a PK then simply logically delete those row(s) (parent and child, if they exist) and add the corrected natural PK and children, if required.

"Also, coming from the Java world, we try to avoid using anything but sequence-generated PKs because
we use a popular object-relational mapping tool called Hibernate. It works infinitely better with
sequence PKs, because it knows an object you're saving is "new" if and only if the PK is null."

If you are using surrogate PKs then you should also be using an Alternate Key (AK) on the column(s) of the natural key. If you do not know the natural key them you have a poor design. If you know the natural key, you don't need a surrogate. Think that all the way through. If you use a surrogate, without an AK, this can happen:

Create table table1 (SK number Primary Key, color varchar2(10);
Create sequence seq1;
Insert into table1 (seq1.nextval, 'RED');
insert into table1 (seq1.nextval, 'YELLOW');
Insert into table1 (seq1.nextval, 'RED');

Oops, which RED is the real RED, SK 1 or SK 3?
OK, to avoid that issue, we'll put an AK on color;
If we put an AK on color we no longer need the SK.

AAMMDDYYYY as a pk field. This reminds me of the spreadsheet users who merge data into one filed to make the spreadsheet thinner and easier to read. Very bad design for a database.

A primary key with format AAMMYY-9999 is not a good design. What happens if we need to store more than 9999 values? I have seen this type of keys suggested by inexperienced business analysts.

Hi Tom,

Why does Oracle not allow to create more than one primary key on a table? What was the thought behind this restriction?
Would that create a confusion in the execution plan?

Oracle defines a PK as: "The column or set of columns that uniquely identifies a row in a table. Only one primary key can be defined for each table."

A table usually has a column or combination of columns whose values uniquely identify each row in the table. This column (or columns) is called the primary key (sometimes called the natural key) of the table and enforces the entity integrity of the table. A table can have only one PRIMARY KEY constraint, and a column that participates in the PRIMARY KEY constraint cannot accept null values.

You might be thinking of an Alternate Key (AK), these are usually used when the PK is a sequence and the natural key column or columns have an AK built on them.

While it is true that every table should have a definition to ensure that every row is unique, I do not believe that it is the job of the primary key to do that.

A primary key is used for maintaining relationships between entities and should be as narrow as possible (e.g. a numeric field). Unique indexes can be used to define uniqueness on a row.

I am currently working on a database that uses natural and compound natural keys. Regardless of the data wanting to change, all relationships are horribly inefficient and needlessly complex to code against.

I think creating tables should be simple and I always follow two rules:

1. Always use a surrogate, numeric PK
2. Nearly always use a unique index to define uniqueness (exceptions may be log tables and such, where duplicate data may not mean that the data is invalid).

And so, to the original question:

Do not spend time thinking about what the PK should be, always create a Numeric surrogate. Do spend time on thinking about what makes the rest of the data unique, and define that with unique indexes.

I've heard the rule, "Do not expose your primary keys" to users.
Hm..., that would mean you can't have a natural key as the PK, so if
I'm in a shop and the main guy there is spouting that, then I guess
surrogates win.

I very much have a like/dislike relationship with Natural keys. The
like is because the queries against children tables do not have to
join back to the parent with the natural key, they can just ask the
table directly. With natural keys as AKs, then all joins involve
driving through the model to get to the table that has the AK tied to
the PK that's on the child.

My dislike is when the number of attributes for the PK get numerous,
maybe above 3. But, that's just my own sort of dislike.

My like for surrogate keys are truly because the object oriented world
has shown up. In supporting an object oriented system, a single key
does simplify the world for everybody. Objects in the app space have
the sense of a single id, getting that from the database makes the tie
exact. Yeah, I know you will continue to ughhhh, but, hey, surrogates
work as PKs. Even when the object oriented system where to go away,
the design won't be wrong, so one isn't wrong in designing it that way
to begin with. When the surrogate world is part of the design then I
only have one rule, there must be a NOT NULL attribute identified as
the alternate key. My beef with that design is the arrogance I find
in the documentation for HIBERNATE. Their documentation states it
with the following flavor, "Surrogate keys are the correct way to
model databases". Huh??? I model surrogates because of the inherent
weak assumption of the object model, not because its the exact correct
way to do it!!! Those arrogant java architects were probably the same
bozos that came up with the ridiculous idea of entity beans. Yeah,
lets do all the caching of rows on the app side, heck, lets have
multiple objects cached on the app all representing the same row, all
because somebody said trips to the database were expensive. Sure,
that's fact!!! We all know it, we've all seen the numbers to support
it, you know those well publicized numbers showing how trips to the
database cost the app way too much time. Oh wait a minute, its the
myth from the java world. Must be akin to the new "database
ignorance" model of app development. Okay, rant over...

If you are building a datawarehouse where your dimensions aren't even
close to 3rd normal, your are forced into surrogates. But, what you
will find is the natural key is how you use access the table. Some
fact will come in from the source tied to the "natural key" with some
date and your code will look up in the dimension with the natural key
where the source date is between the start/end dates and vhalla, you
get the dimensions key for the fact. So, in the Kimball datawarehouse
world, always use surrogates for PKs.

Remember Julian to Gregorian calendar change :))

Hi Tom,

I hear your words clashing with Kimballs on the date dimension
everytime I use it. On that one I went with Kimball. To me there is
one fundamental reason a date is wrong as a key and it is that it
really does violate the "atomic" principle. A date is stored with the
expectation that the stored attribute will be parsed looking for
months, or days, or years or quarters, so, its like the end all be-all
of intelligent keys, and we know the rule on intelligence in a key.
So, in my theoretical rationalization, I've joined Kimball. But, not
having to join to the date dimension when I'm researching things would
be alot easier, which, is when I hear your words loud and clear. :-)
And, don't get me wrong, I still have dates all over my models, but,
in the dimensional world, I've sided with Kimball.

I guess, the "ease" or correctness of the design is found when end
users are accessing the data. Doing "date logic" is much less easier
to them than doing math, ie, the subtracting the julian number.
That's sometimes true based on what user, and one could also say,
well, let them do the join then! But, Kimball makes a very loud point
explicitly stating do not use dates on the fact, implicitly saying,
"Force the join".

But, I hear ya. I mention this clash many times with my group. I
say, "Tom Kyte" would take me out to the woodshed on this, but Kimball
point blanks says don't use a date. Hem, hah, flip a coin, make a
call...

How avoid someone to change an alternate key value (natural key) in which the primary key is already being referenced as foreign key in another table?

ex.
create table employee(
employee_id number primary key,
employee_code varchar2(5) unique
...);

create table employee_salaries(
salaries_id number primary key,
empoyee_id number references employee(employee_id)
...);

I want to avoid someone to change the employee_code if the employee_id was already used by another table (referenced)

Consider that the primary key can be used by dozen of other tables (referenced)