Database Keys...

Database Keys...

• Primary Key

Primary Key is one that uniquely identifies each record/row/tuple of a table. It has Constraints of NOT NULL and UNIQUE.

• Unique Key

Unique Key also identifies each row/record/tuple of a table but it don't have NOT NULL constraint but it is UNIQUE.

Difference b/w Primary Key and Unique Key
Primary Key dont allow NULLS while Unique Key allow NULLS.

Primary Key creates Clustered Index on columns whereas Unique Key creates non-clustered Index by default.

• Foreign Key

A foreign key is a column, or combination of columns, that contain values that are found in the primary key of some table (including, possibly, itself). A foreign key may be null, and almost always is not unique.

• Candidate Key

Candidate Key is one that identifies each row/record/tuple uniquely. It becomes a Primary Key.

A table can have many candidate keys but only one primary key.

Consider the following table --
FooNumber FirstName LastName BarTab
21 Fred Jones 47
32 Bill Smith 23
87 Wendy Jones -
32 Bob Stikino 943

In this example, only FirstName is a single-column candidate key, because it is the only column that is unique and not null. So it can be called Primary Key.

• Alternate Key

If a table has more then one Candidate Keys, one becomes Primary Key and the other is called Alternate Key.

• Composite Key

Composite Key is a Key that is formed by Composition of two or more Columns.

In the above example, the combinations (RecordNo,FirstName), (RecordNo,Lastname), (RecordNo,FirstName,Lastname), and (FirstName,LastName) are all candidate keys. Any combination including Age is not a candidate key because it contains a null.

• Surrogate/Sarrogate Key

Database designers add an extra column to their table designs, a column defined as an integer, which will hold a number. In Microsoft Access, this is an autonumber, in MySQL it's an auto-increment, in Oracle it's a sequence, and in SQL/Server it's an identity column. As these names suggest, this integer is automatically assigned by the database, usually incrementally, sometimes using an initial value and increment that you can specify. Some databases allow these numbers to be generated randomly.
The purpose of this type of automatically generated number is to act as the Surrogate primary key, usually in those situations similar to the above where candidate keys are multi-column. The awkwardness of a multi-column candidate key becomes apparent as soon as you define a foreign key on it.