keys-Day10

Keys :

Surrogate key

A unique primary key generated by the RDBMS that is not derived from any data in the database and whose only significance is to act as the primary key.

A surrogate key is frequently a sequential number but doesn't have to be. Having the key independent of all other columns insulates the database relationships from changes in data values or database design and guarantees uniqueness.

Some database designers use surrogate keys religiously regardless of the suitability of other candidate keys However, if a good key already exists, the addition of a surrogate key will merely slow down access, particularly if it is indexed.

Intelligent key

A relational database which depends wholely on one or more other columns in the same table. An intelligent key might be identified for implementation convenience, where there is no good candidate key

For example, if the three-letter initials of a group of people are known to be unique but only their full names are recorded, a three letter acronym for their names (e.g. John Doe Smith -> JDS) would be an intelligent key.

Intelligent keys are a Bad Thing because it is hard to guarantee uniqueness, and if the value on which an intelligent key depends changes then the key must either stay the same, creating an inconsistency within the containing table, or change, requiring changes to all other tables in which it appears as a foreign key The correct solution is to use a surrogate

Partial key

A key which identifies a subset of a set of information items and which could narrow the subset to one item if other partial key(s) were combined with it.

Compound key

(Or "multi-part key", "concatenated key") A key which consists of more than one attribute of the body of information (e.g. database "record ") it identifies.

Alternate key

A candidate key which is not selected as a primary key

Unique key

A key which identifies only one body of information out of several.

Primary key

The candidate key selected as being most important for identifying a body of information (an entity, object or record).

Candidate key

One of several possible attributes or combinations of attributes which can be used to uniquely identify a body of information (a "record ").

Super Key

A Set of attributes that uniquely identify a tuple in a relation is called a super key.

Secondary Keys

Keys other than the candidate keys are called the secondary keys.

Foreign Key

A key column in a table that identifies records in a different tbale is called foreign key.