| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Is the use of VARCHAR(256) as Primary Keys preferred in Oracle?
Galen Boyer <galenboyer_at_hotpop.com> wrote in news:uptsmgsup.fsf_at_hotpop.com:
> You again dogged my answer. I said redesign of the application
> would have to happen. What wouldn't have to happen, with artificial
> keys, is redesign and rewrite of the PKs. The choice of keys isn't
> going to preclude a well-designed app, but choosing artificial,
> dummy keys makes it so one doesn't have to redefine and rewrite
> cascading keys.
Re-repeating myself, the idea behind using an artificial primary key is that one builds a level of abstraction between the physical layer and the logical layer. When the business changes, there may be changes to the schema but it won't be as severe if one were to have used business keys as the primary key.
I'm not advocating that the business keys get chucked though, they get used as alternate keys. This way we end up with the best-of both words at the cost of adding an additional index.
Also, depending on the naming strategy, developers and end-users alike don't have to be trained much. For example, if one standardizes on 'id' as the primary key name and the FK being <pk tablename>_<pk name>, you end up with the following:
Assume
Then
id <<pk>>
detail
id <<pk>>
master_id <<fk>>
Furthermore, it becomes _very_ easy to auto-generate code for the above. I know because we have an code generator that generates insert/update/delete PL/SQL for any schema by reading the meta data from the schema itself. All written in Perl/OraPerl.
-- Pablo Sanchez, High-Performance Database Engineering http://www.hpdbe.comReceived on Sat Nov 30 2002 - 21:31:31 CST
 |
 |