|what is a surrogate key
what is a surrogate key and is there a feature in oracle that could be used for this purpose?.
A surrogate key is, simply put, a way to join information together from multiple tables without getting duplicate or "junk" information(ie..Cartesian Product). How it works is simple. Let's say we have two tables each with it's own Primary Key. If I want to select something from both tables I need to join them together correct? So it might look something like this:
col1 - Artist_id (pk) col1 - Store_id (pk)
col2 - Artist_name col2 - Artist_id (fk...aka(Surragate Key))
col3 - Artist_Album col3 - Branch_id
col4 - Artist_SongTitle col4 - Store_Name
select a.Artist_name, a.Artist_Album, b.Store_Name
from Artist_TBL a, Store_TBL b
where a.Artist_id = b.Artist_id
well, the above answer does explain what a join is, it does not explain what
a surrogate key is.
A surrogate key is a key that you create and use when there is already a field
(or multiple fields in the case of a composite key) that you could have used
for a primary key. Why would you want to do this ? Well say that in a given
table, the fields that are present that you could use for a composite primary
key are three characters fields called (for example) Region_Code , Area_Code ,
and Person_Code. To use a surrogate key, you could create an additional field
(preferably numeric) and identify it as the primary key and name it RAP_PK (Region
Area Person Primary Key) or whatever you want. Now, each unique value of RAP_PK would correspond to each unique combination of values for RegionCode,
AreaCode,PersonCode and you can use it to uniquely identify each record. What
advantage is there in this ? Well, for the table by itself, it is much much faster
to use a numeric key than a character key (not to mention a composite character key).
Also, in child tables that use this value as a foreign key, the record size will
be much smaller (a good thing) using a single numeric key as opposed to a long
Well, for this table by itself.