A Gentle Introduction to

SQL

NEW.

These questions have been submitted by users.



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: Artist_TBL Store_TBL 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 characters fields. Well, for this table by itself.
access variations:
None

access
db2
mysql
oracle
postgres
sqlserver