SQL 4 begginers - Keys

In most online articles about databases you can easily find a lot of articles explaining cool, complex and new features. But what’s about some basics? I decided to release some articles about the basics. The articles will mostly cover topics usable in most relational databases, but the examples and the focus will be on MS SQL Server database engine.

A database key provides the possibility to identify, access and update information in a database table. A key is a single field (column) or combination of multiple fields. Its purpose is to access or retrieve data rows from table according to the requirement. The keys are defined in tables to access or sequence the stored data quickly and smoothly. They are also used to create links between different tables

Primary key (also known as unique) is a column (or more than one) that best identifies one unique row and identifies each record as unique (e.g. social security number, DNK, fingerprint etc.).

Primary key:

  • There can only be one primary key per table
  • It cannot be unknown (NULL)
  • It ensures that there are no duplicate rows

When creating a primary key in MS SQL Server a Clustered index is created automatically. A clustered index defines the order in which data is physically stored in a table. Table data can be sorted in only way, therefore, there can be only one clustered index per table. Indexes will be another topic explained in another article.

In lot of database designs primary keys are often autoincremented numeric data or unique identifier (GUID) data.

Foreign key is a column that matches a primary key in another table so we can join the data between both.

On the following image you can see an example of two imaginary tables.

The first table is "Invoice header" and it have some basic records about one invoice. The invoice number is a primary key for this table. This means that it can't exists two invoice with the same number. The second table, "Invoice details" contains details of one invoice. Primary key for the second table is the autoincrement column "ID". Foreign keys are "CustomerID" in the "Invoice header" table and "Invoice number" and "ArticleID" in the second table.

Secondary key is a field or combination of fields for retrieving data. Secondary key is a non-unique field. One secondary key value may refer to many records.

Composite key is a primary key that consists of two or more attributes.

Unique key is a key that may or may not be NULL.

In theory there are also other types of keys (Candidate, Alternate, Super, Compound, Surrogate etc.) but we will not go in depth with all of them.

In MS SQL you can mostly use Primary and Foreign keys. The primary key is always unique. It can be "simple" or "composite". MS SQL will create a clustered index on the primary key. Primary key column must have a value (NOT NULL). Foreign key is not unique, can be NULL and can be also "simple" and "composite". The index will not be created by default on this key.