Many tables in a relational database system (in my admittedly limited experience) have this sort of structure (Oracle syntax as that's what I'm used to):
create table widgets ( widget_id number primary key, widget_name varchar2(20) not null, widget_colour varchar2(10) ); create unique index ix01_widgets on table widgets (widget_name);
So there are two indices: one for the primary key,
widget_id, and one for the
Can anyone explain the advantage of having a numeric primary key over just using
widget_name as the primary key? The latter has the advantage of making the contents of child tables more readable, e.g.:
create table widget_characteristics ( widget_name varchar2(20) not null, characteristic_name varchar2(20) not null, characteristic_value varchar2(100), constraint pk_widget_characteristics primary key (widget_name, characteristic_name), constraint fk01_widget_characteristics foreign key (widget_name) references widgets (widget_name) );
Otherwise (if using a generated numeric primary key) I would need to create views to see the denormalised data.
Is it something about the "indexability" of numbers versus strings? I can understand the issue of index size if the identifier (i.e.
widget_name) is long, but if it's short then is that still an issue? For tables with multi-column primary keys I can understand the logic to having a generated numeric PK, but for "simple" tables I'm not clear.
Given the ubiquity of numeric primary keys I assume there is a fundamental reason for this: I'm just not sure what it is!