What is Surrogate key?

Surrogate keys are almost never defined in the logical model. They either do not map to real data elements or are derivable, both of which are reasons why you avoided these keys in the Logical model. Until the Physical model transformation stage, you never considered these as another type of candidate key. Three types of surrogate keys exist.

1. Concatenated surrogate keys: These are compressed, multicolumn data strings.
2. Semisurrogate keys: These replace a portion of a multicolumn key with a system-generated number.
3. Surrogate keys: These are completely system generated.

These rows tell you that Person 1 has played two games; they won one and lost one. Sometimes looking at sample data helps make the primary key choices clearer than the logical modeling did. But in this case, we still do not see any natural keys, other than the one we already found. However, our experience has told us that you should avoid having dates and times in a primary key if possible. This is mostly because of the way DBMSs want to format 1) date and 2) time data for you. Using dates and times in a primary key can become extremely complicated and can make using the key programmatically awkward. So let us decisively rule out making the natural key the primary key in this case.

Surrogate keys are also known as system-assigned keys(SAKs). To implement these, you need the system to generate a unique number each time a record is inserted into the table and use that as the primary key. Remember to put a unique index on any natural keys to prevent inadvertently duplicating any rows. Surrogate keys are a great addition to your physical design arsenal. In fact, they are a good choice if you need to set up a large table that might have performance issues, since DBMSs generally find numeric keys much easier to sort and locate than text. But you should never apply surrogate keys as a default key design.

Note: Surrogate keys are often defaulted as the primary key of everything, without a single thought about what other choices might be made. This can promote a kind of narrow view of the design and lack of concern about the quality of data.

Surrogate keys are often defaulted as the primary key of everything, without a single thought about what other choices might be made. This can promote a kind of narrow view of the design and lack of concern about the data’s quality.

Using surrogate keys requires that certain issues be addressed, just like using concatenated and semisurrogate keys. None of these primary keys means anything to anybody. To fulfill all the roles of a primary key, you must give at least one natural key a unique index to maintain data set member uniqueness. When you are using surrogate keys, you may also have to deal with the issue of concurrency (handling concurrent requests for new keys) and the reusability of key values. Oracle and DB2 UDB deal with this problem by using "sequence generator" structures, which are independent of tables. wIn SQL Server, you specify a column as being an identity column. This makes reusability of the surrogate key in SQL Server difficult between different tables. Before deciding on surrogate keys, make sure the RDBMS handles them in a way that will be helpful.