Data Modelling – Key

FOREIGN KEY:

Foreign key is a referential constraint between two tables, A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables. You can create a foreign key by defining a FOREIGN KEY constraint when you create or modify a table.

In a foreign key reference, a link is created between two tables when the column or columns in another table reference the column or columns that hold the primary key value for one table. This column becomes a foreign key in the second table.

For example, the Sales.SalesOrderHeader table in the AdventureWorks2008R2 database has a link to the Sales.SalesPerson table because there is a logical relationship between sales orders and salespeople. The SalesPersonID column in theSalesOrderHeader table matches the primary key column of the SalesPerson table. The SalesPersonID column in theSalesOrderHeader table is the foreign key to the SalesPerson table.

Surrogate

It is a substitute primary key – for when you don’t have a real one. An ID column populated via a sequence is known as a surrogate key.

The use of SKs in Datawarehouse environments is not only simplicity of star design or performance.

You often need to populate your dimensions with extra records not found in the source table, such as “UNKNOWN” or “NOT APPLIES” records. If you don’t use SKs, What PK value would you use for this extra records?

How can you be sure that the values you choose won’t collide with future records from the source table?

Also, taking your dimensional schemas away from possible source key changes is a good idea. Of course creating and maintaining SKs adds complexity and is extra work for ETL processes.

Natural

If the table has a true primary key – it is known as a “natural key”

Business

Unique key that use the business users to identify uniquely an entity. The business key is a meaningful/business code based generally on the concatenation of attributes value of an entity instance.