What is the difference between Surrogate Key and Primary Key? | LinkedIn

What is the difference between Surrogate Key and Primary Key? | LinkedIn

Introduction

There is no difference to the meanings below due to the data model; these are relational database management system (RDBMS) concepts. Whether you are using a normalized model or a dimensional model, the "primary key", "surrogate key", "natural key", and "alternate key" terms all have the same meanings.
A Google search turned up lots of places to read up on keys, including these:

• http://en.wikipedia.org/wiki/Unique_key
• http://en.wikipedia.org/wiki/Surrogate_key
• http://www.dbdebunk.com/page/page/622337.htm
• http://databases.about.com/cs/administration/g/primarykey.htm
• Methods for primary and secondary key selection (Data base management)

Primary Key

A Primary Key (PK) in a database table uniquely identifies a row in the table. There can be only one PK per table. Depending on the database engine being used, a PK may require the explicit creation of a unique index or the database engine may automatically create such an index for the column or columns identified as the PK column(s).
A primary key can consist of a single column using a "surrogate" value or be made up of one or more columns in the table that will uniquely identify a row (also called a "natural" or "meaningful" key).

Surrogate Key, aka Meaningless Key

The term "Surrogate Key" describes a particular type of Primary Key (PK). There really is no such thing as a "Surrogate Key". There are Primary Keys that use surrogate values. A PK that uses a surrogate value always consists of only one column.
The surrogate value is generally a system-generated value that nobody would know what it is to search for from a query. This PK may then be used as a foreign key (FK) in other tables and used for join conditions.
A Primary Key that uses a surrogate value is also known as a "meaningless" key. A table with only a "surrogate key" (primary key using an arbitrary value) must have an alternate key (AK) (see below). Without an AK, it is very likely that there will be "duplicate" rows in the table. An explanation of this follows below.

Natural Key, aka Meaningful Key

The other type of PK is called a "natural key". A natural (or meaningful) primary key is made up of one or more columns in a row that uniquely identify that row. The column or columns used for a natural key contain values that have meaning to the business.
If the natural key is the PK, then there can be no surrogate key. If the natural key is not the PK, it is an alternate key.

Alternate Key

While there can be only one primary key in a table, there may be more than one "alternate" keys in a table. These alternate keys are controlled by one or more unique indexes. A table with only a "surrogate key" (primary key using an arbitrary number) and no unique index for an alternate key is useless - you are doomed to have duplicates in rows for every column EXCEPT the surrogate key which does not help you find a unique row at all.
Generally, the columns used for the alternate key(s) are the columns that people will use in their queries to find a unique row, but the joins to other tables will be done using the single PK column comprising the "meaningless key" with the PK column being used as a FK column in those other tables.
The use of surrogate key values prevents the need to duplicate multiple columns in "child" tables just to be able to find the children of the "parent" record in a JOIN (or vice versa).

Impact of Primary Key Design

Let's examine the impact and implications of designing a table's primary key. Table_1, below, is a table that has a PK made up of a surrogate value, hence "Surrogate Key", and no AK.

Table_1: This table has a surrogate primary key (PK_ID) but no alternate key.

T1_PK_ID	T1_COL1	T1_COL2	T1_COL3
1	A	B	C
2	A	B	C
3	A	D	E

While the three rows above are unique in the PK value (PK_ID column), rows 1 and 2 are really not unique rows, are they.
A table with only a "surrogate key" (PK using an arbitrary value) and no unique index for an alternate key (or the natural key) is useless - you are doomed to have duplicates in rows for every column EXCEPT the surrogate key which does not help you find a unique row at all.
If Table_1 were created with a unique index on the columns that make up an alternate key, it would look like Table_2 below.

Table_2: This table has a surrogate primary key (T2_PK_ID) plus an alternate key (T2_AK1, T2_AK2) enforced by a unique index.

T2_PK_ID	T2_AK1	T2_AK2	T2_COL3
1	A	B	C
2	A	D	E

Generally, the alternate key columns are the columns that people will use in their queries to find a row, but the join to another table will be done using the single primary key column comprising the "meaningless key" which is a FK in the table being joined to. I'll show an example of this later.
A primary key can consist of a single "surrogate" value or be made up of one or more columns in the table that will uniquely identify a row (also called a meaningful key). If Table_1 were recreated using the natural key, it would look like Table_3 below.

Table_3: This table has a meaningful primary key (T3_PK1, T3_PK2).

T3_PK1	T3_PK2	T3_COL3
A	B	C
A	D	E

If Table_3 was joined to another table, that table would have to include all the columns making up the primary key in Table_3; these would be the foreign key (FK) columns. The use of surrogate key values as in Table_2 prevents the need to duplicate multiple columns in "child" tables just to be able to find the unique "parent" records in a JOIN, and vice versa.
As an example of what I stated above, let's assume the following table structure.

Table_4: This table has a surrogate primary key (T4_PK), a natural key (T4_AK1, T4_AK2), and foreign key columns (T3_PK1, T3_PK2) needed to join this table to Table_3 ."

T4_PK	T4_AK1	T4_AK2	T4_COL3	T3_PK1	T3_PK2
100	AA	BA	CA	A	B
101	AA	BB	CB	A	D

A query to find a row in Table_4 and join it to Table_3 to get another column would look like this:

SELECT
  T4.T4_PK,
  T4.T4_AK1,
  T4.T4_AK2,
  T4.T4_COL3,
  T3.T3_COL3
FROM
  Table_4 "T4"
  INNER JOIN Table_3 "T3" ON
    T4.T3_PK1 = T3.T3_PK1 AND
    T4.T3_PK2 = T3.T3_PK2
WHERE
  T4.T4_AK1 = 'AA' AND
  T4.T4_AK2 = 'BA'

The result from the query above would be:

T4_PK                  T4_AK1 T4_AK2 T4_COL3 T3_COL3 
---------------------- ------ ------ ------- ------- 
100                    AA     BA     CA      C     

If this same query were modified to be run against Table_1, assuming T1_COL1 is the same as T3_PK1 and T1_COL2 is the same as T3_PK2, the query would look like this:

SELECT
  T4.T4_PK,
  T4.T4_AK1,
  T4.T4_AK2,
  T4.T4_COL3,
  T1.T1_COL3
FROM
  Table_4 "T4"
  INNER JOIN Table_1 "T1" ON
    T4.T3_PK1 = T1.T1_COL1 AND
    T4.T3_PK2 = T1.T1_COL2
WHERE
  T4.T4_AK1 = 'AA' AND
  T4.T4_AK2 = 'BA'

The result from the query above would be:

T4_PK                  T4_AK1 T4_AK2 T4_COL3 T1_COL3 
---------------------- ------ ------ ------- ------- 
100                    AA     BA     CA      C       
100                    AA     BA     CA      C 

Instead of a single row being returned, we now get two rows.
Remember we created a modified version of Table_1 giving it an AK as Table_2. If we run the same query above against Table_2, changing the table and column names in the JOIN condition, we see that the AK in Table_2 prevented the "duplicate" row and we get only one row returned.

SELECT
  T4.T4_PK,
  T4.T4_AK1,
  T4.T4_AK2,
  T4.T4_COL3,
  T2.T2_COL3
FROM
  Table_4 "T4"
  INNER JOIN Table_2 "T2" ON
    T4.T3_PK1 = T2.T2_AK1 AND
    T4.T3_PK2 = T2.T2_AK2
WHERE
  T4.T4_AK1 = 'AA' AND
  T4.T4_AK2 = 'BA'

Results:
T4_PK                  T4_AK1 T4_AK2 T4_COL3 T2_COL3 
---------------------- ------ ------ ------- ------- 
100                    AA     BA     CA      C       

The table below, Table_5, has a surrogate primary key (T5_PK), a natural key (T5_AK1, T5_AK2), and foreign key columns needed to join this table to Table_1 and Table_4. Since T1_PK_ID is a surrogate (meaningless) key, it is not expected that one would know what it is. Therefore, searches for a row would be based on other columns thought to uniquely identify a row, in this case T1_COL1, T1_COL2, and T1_COL3.

Table_5:

T5_PK	T5_AK1	T5_AK2	T5_COL4	T4_PK	T1_COL1	T1_COL2	T1_COL3
100	AA	BA	CA	100	A	B	C
101	AA	BB	CB	101	A	D	E

In the query below, we will try to get primary key value from Table_1; let's say we need to use it in another query. The expectation is a return of a single row, but, because the rows are not truly unique based on the other columns in Table_1, two rows are returned:

SELECT 
  Table_1.T1_PK_ID
FROM Table_5
INNER JOIN Table_4 ON
  Table_5.T4_PK = Table_4.T4_PK
INNER JOIN table_1 ON
  Table_5.t1_col1 = table_1.t1_col1
  AND Table_5.t1_col2 = Table_1.t1_col2
WHERE
Table_5.t1_col1 = 'A'
AND Table_5.t1_col2 = 'B'

Results:
T1_PK_ID               
---------------------- 
1                      
2                  

If we wanted to get the value of Table_1.T1_COL3 for the "unique" values in the supposed "key" values in T1_COL1 and T1_COL2 using the SQL below, we will get an error.

SELECT 
  T1_COL3
FROM 
  Table_1
WHERE
  t1_pk_id = (select t1_pk_id
    from table_1
    where
    t1_col1 = 'A'
    AND t1_col2 = 'B')


Error starting at line 1 in command:
SELECT 
  T1_COL3
FROM 
  Table_1
WHERE
  t1_pk_id = (select t1_pk_id
    from table_1
    where
    t1_col1 = 'A'
    AND t1_col2 = 'B')
Error report:
SQL Error: ORA-01427: single-row subquery returns more than one row
01427. 00000 -  "single-row subquery returns more than one row"
*Cause:    
*Action:

Conclusion

While this started out with a question asking about the difference between a "surrogate key" and a "primary key", it evolved into a dissertaion on primary keys and how they can be designed. You should have learned:

1. There really is no such thing as a "surrogate key; there are primary keys using surrogate values."
2. A "primary key" can be made up from
• a single column using a surrogate value
• one or more columns that have unique values and known business meanings
3. A table with a PK using a single column with a surrogate value MUST have an alternate key (AK)
4. There are impacts to SQL writing if the PK is designed wrong!

I could go on about the impact of primary keys in a data warehouse environment where multiple rows with the same primary key must be maintained for historical purposes (slowly changing dimension tables), but I won't! Aren't you happy?! This disssertation should give you a "basic" understanding of primary keys and I leave it up to you to apply this understanding in your data model designs.

Programming using Scripting Languages

It might seem like I'm digressing from my "data-centric" focus, but, really, I don't think so.  I have had to write  scripts to automate many "data-centric" tasks over the years; most of these scripts contained embedded SQL for data manipulation (DML).  Many of these scripts were automated by using UNIX cron, Windows AT, or some other scheduler. For example:

• Transfer data files from one location to another
• Load data files directly into database staging tables
• Extract data to create data files to be sent to clients or vendors using FTP
• Create reports
• Data migration from mainframe to regional client/server databases.

Granted, these tasks were done before modern CASE tools took over many of these tasks, but you still might need to do some tasks like these on an impromptu, urgent basis.  Or maybe the client doesn't have one of the CASE tools and doesn't want to spend the money to get one - just do it!

On UNIX systems, I suggest learning Korn Shell (ksh).  You will be surprised at how robust a programming language this is.  Now I know some people will say it is not a "programming" language because it isn't compiled, but the skills needed to write good scripts are the same as those needed to write good compiled programs.  Compiling a program is done to make it run faster and there are programs that will compile ksh scripts, such as shc.

Another favorite of mine is Perl.  It can be written for both UNIX and Windows systems (as well as many others) with little change needed between systems so it is quite portable.  Perl scripts can be compiled, too, using commercial programs such as Perl2EXE.

Kimball or Inmon?

The debate goes on as to whether Kimball's or Inmon's philosophy on data warehouses is best. But here is a fresh perspective, by Bill Inmon himself.

A TALE OF TWO ARCHITECTURES

LPAD and RPAD for Teradata

Teradata does not come with an LPAD() or RPAD() function. If you can create a UDF for them, that would be best, but if you can't, here are some workarounds.

RPAD a string with spaces

-- replace 10 in char(10) below with desired column length
-- single ticks put around result to show spaces were actually added to right

select
  'abc' as "SampleString",
  '''' || cast(SampleString as char(10)) || '''' "rpad_spaces";

RPAD a string with any character

-- make string of characters (in this example '0000') to match desired length
-- leading space in column is preserved
select 
  ' 1' AS "SampleString",
  SampleString || Substring('0000' From 1 For Chars(SampleString)) AS "rpad_zero";

-- same as above, but using ANSI SUBSTR
select 
  ' 1' AS "SampleString",
  SampleString || SUBSTR('0000',Chars(SampleString)) AS "rpad_zero";

LPAD a string with any character

-- replace 0 with desired character
-- make string of characters (in this example '0000') to match desired length
-- leading space in column is preserved
select 
  ' 1' AS "SampleString",
  Substring('0000' From 1 For Chars(SampleString)) || SampleString "lpad_zero";

-- same as above, but using ANSI SUBSTR
SELECT 
  '19' as "SampleText",
  SUBSTR('00000'||SampleText,CHARACTERS(SampleText)+1) "LPAD(SampleText,5,'0')";

-- Showing how trailing spaces from a CHAR column can be dropped 
select
  '0000123  ' as "SampleText",
  CAST(CAST(SampleText AS integer FORMAT '-9(10)') AS CHAR(10)) "LPAD(SampleText,10,'0')";

STAR schema and transactions

I recently replied to a question in ITtoolbox's Data Warehouse forum regarding star schema data marts.
The original post had several questions that others answered, but one question had not been addressed, so that was the basis for my reply.

"Is it sound industry practice to use a star schema to hold individual records rather than totals?"

In order to create a fact table that aggregates transaction data, there must be a dimension table that contains the transaction detail. The granularity of that dimension table needs to be at the level needed to perform the drill down or slice and dice necessary. For some industries, such as cell phone carriers, the granularity might need to be down to the millisecond level. For others, daily granularity might be OK.
These transactional dimension tables also need to be updated, so you must determine what type of slowly changing dimension (SCD) table you need. (The "slowly" part of the name was a bad choice of words when transaction changes can occur by the second.) There are three types: SCD1, SCD2, and SCD3. Most common is SCD2, next is SCD1, and rarely used is SCD3.
A time dimension table is critical for any data warehouse / data mart. The granularity of the time dimension needs to be at a level at least as fine as the dimensional transaction table is at.
For reporting purposes, I usually suggest using a fact table or view to be the source for the reporting engine rather than have the reporting engine do all the aggregating. This way, the algorithms needed to do the aggregations will be consistent. If these are left to the reporting engine(s) and true ad-hoc queries, the results might be different and there will be lots of complaints and troubleshooting to determine why the results are not consistent.
Whether a table or view is used depends on how frequently the report needs to updated and the length of time needed to return results.

How to work with bad date formats in Teradata

A question was posted on ITtoolbox's Teradata forum concerning data loading from a file that contained character strings for dates that used single digit day and month formats like 4/1/2010. Teradata does not support dates in this format: M/Y/YYYY; it only supports two-digit month and day formats like MM/DD/YYYY. Therefore my reply was a sample SQL statement that would work for the date conversion, but the actual load from the data file would need to do this in the "wrapper' code.

The SQL in Teradata format is:

SELECT
    '4/1/2010' AS "BAD_DATE",
    CASE
        WHEN INDEX(BAD_DATE,'/') = 2
          THEN '0' || SUBSTRING(BAD_DATE FROM 1)
          ELSE BAD_DATE
    END AS "MONTH_OK",
    CASE
        WHEN INDEX(SUBSTRING(MONTH_OK FROM 4),'/') = 2
          THEN SUBSTRING(MONTH_OK FROM 1 FOR 3) || '0' || 
            SUBSTRING(MONTH_OK FROM 4)
          ELSE BAD_DATE
    END AS "GOOD_DATE"
-- don't need a FROM clause to do this
; 

Database Management Systems Best Uses

As a data architect, I am database agnostic until reaching the physical implementation. During the conceptual and logical modeling phases, I will have gathered metrics that will help predict the scalability needed for the physical implementation.
In the perfect scenario, these metrics will be used to determine the best database management system (DBMS) to use. Many times, however, the target DBMS has already been chosen; in this case, these metrics will provide the basis for scalability and risk assessment. These metrics include:

• Number of entities (tables)
• Number of attributes (columns)
• Volume metrics
• Initial
• Table size (row count and bytes)
• Connection Numbers
  • Total
  • Average concurrent
  • Maximum concurrent
  • Application / system users
  • Power users
• Growth rates for above: 1 year, 3 years

The data architect may or may not be involved in the physical implementation process, depending on the company's organizational hierarchy. Assuming that I will be involved with this, I don't need to be expert in all the available DBMS, but I should have some knowledge and understanding of them based on past experience. But I won't be able to keep up with all the DBMS out there, especially the niche systems. Some open source systems are also available and used.  A data architect needs to work closely with the DBMS subject matter experts (SME).  The most important SME to me are those who have actual experience using and supporting a DBMS and the type of data store being used on that DBMS.

So we all might gain understanding of what is available and the best DBMS uses, what is your experience? Consider the following points and share what DBMS was used and how it met, or failed to meet, expectations.

• Purpose of database (EDW, DW, Data mart, ODS, reporting, OLAP, OLTP, etc.):
• Database structure (Normalized, Dimensional, Snow Flake, etc.):
• Database size in TB:
• Number of tables in database:
• Number of columns in database:
• Total number of users:
• Total number of power users:
• Maximum number of connections:
• Average number of connections:
• DBMS or RDBMS used:
• Expectations met or not for:
• Database growth in TB?
• Query performance?
• Performance tuning?
• Connectivity?
• Database maintenance (DBA)?
• Other comments:

My experiences:

• Purpose of database: Enterprise Data Warehouse (EDW), Single source of truth; was source for other data marts, reporting data stores, OLAP.
• Database structure: Normalized, 3NF
• Database size in TB: Greater than 100 TB
• Number of tables in database: More than 1000 tables; more than 3000 views.
• Number of columns in database: More than 50,000
• Total number of users: More than 1000
• Total number of power users: More than 100
• Maximum number of connections: More than 500
• Average number of connections: More than 100
• DBMS or RDBMS used: Teradata
• Expectations met or not for:
• Database growth in TB? Yes. Additional disk space seemed fairly easy to add. Adding more servers (AMPS) increased disk space, provided more CPU processing, and more user connectivity.
• Query performance? Very good.
• Performance tuning? Performance dependent on Primary Indexes (distribution), partitioning, secondary indexes, SQL tuning, statistics collected.
• Connectivity? Batch programs, user direct, and user web-based.
• Database maintenance (DBA)? Faster to add or drop columns to/from tables by creating new table and copying data from old table to new table than using ALTER TABLE commands. Obviously, not all steps to do this are itemized here.
• Other comments: By far, the best RDBMS I have experienced for VLDB. Most everything was done in massively parallel processing. Data marts, reporting data stores, etc. were derived from physical database via views to create logical data stores. Response times were excellent; in those few cases where multi-table joins could not be improved, a physical table was created and loaded via batch processing.