# Why Surrogate Keys are Important

<figure><img src="https://2629735788-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F3G2NQEHaUSzehWzaKvXK%2Fuploads%2FjQGKY70Lnq3ENtDGbqVW%2Fdw_surrogate_key.png?alt=media&#x26;token=f6c3a54a-9bf5-4fb2-94e6-5f803a31cf81" alt=""><figcaption><p>Example of Primary / Foreign Keys</p></figcaption></figure>

Lets have a sample data

<table><thead><tr><th width="137">StoreID *</th><th width="157">Street</th><th width="132">City</th><th width="93">State</th><th>Country</th></tr></thead><tbody><tr><td>S1001</td><td>24th Blvd</td><td>Phoenix</td><td>AZ</td><td>USA</td></tr><tr><td>S1002</td><td>21 Bell Road</td><td>Miami</td><td>FL</td><td>USA</td></tr><tr><td>S1003</td><td>Main Street</td><td>New Port</td><td>CA</td><td>USA</td></tr></tbody></table>

StoreID is Natural Key (PK) It has a meaning S stands for Store 1001 means its the first store.

### What is the issue now ?

When data changes (Slowly Changing Dimension we will cover in next chapter) how to handle the change.

**Situation 1**: Store 1 moves to new location or store is closed for sometime and opened under new franchise.

<table><thead><tr><th>StoreID *</th><th width="192">Street</th><th>City</th><th>State</th><th>Country</th></tr></thead><tbody><tr><td><strong>S1001</strong></td><td>24th Blvd</td><td>Phoenix</td><td>AZ</td><td>USA</td></tr><tr><td>S1002</td><td>21 Bell Road</td><td>Miami</td><td>FL</td><td>USA</td></tr><tr><td>S1003</td><td>Main Street</td><td>New Port</td><td>CA</td><td>USA</td></tr><tr><td><strong>S1001</strong></td><td>1st Street</td><td>Phoenix</td><td>AZ</td><td>USA</td></tr></tbody></table>

**Situation 2**: When acquiring competitive business (say Target buys KMart), the Natural Keys dont make sense now.

<table><thead><tr><th>StoreID *</th><th>Street</th><th width="195">City</th><th>State</th><th>Country</th></tr></thead><tbody><tr><td>S1001</td><td>24th Blvd</td><td>Phoenix</td><td>AZ</td><td>USA</td></tr><tr><td>S1002</td><td>21 Bell Road</td><td>Miami</td><td>FL</td><td>USA</td></tr><tr><td>S1003</td><td>Main Street</td><td>New Port</td><td>CA</td><td>USA</td></tr><tr><td><strong>233</strong></td><td>South Street</td><td>New Brunswick</td><td>NJ</td><td>USA</td></tr><tr><td><strong>1233</strong></td><td>JFK Blvd</td><td>Charlotte</td><td>NC</td><td>USA</td></tr></tbody></table>

These business decisions / changes have nothing to do with the Technology.

### How to Overcome this issue?

Add Surrogate Keys (running sequence number)

<figure><img src="https://2629735788-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F3G2NQEHaUSzehWzaKvXK%2Fuploads%2FMl64hDMYYyaxPcJx14K9%2Fdw_surrogate_key_02.png?alt=media&#x26;token=db987577-6adf-4d00-9935-518c4260dc9e" alt=""><figcaption></figcaption></figure>

<table><thead><tr><th width="154">Surr_Store</th><th>StoreID *</th><th width="149">Street</th><th>City</th><th width="91">State</th><th>Country</th></tr></thead><tbody><tr><td>1</td><td>S1001</td><td>24th Blvd</td><td>Phoenix</td><td>AZ</td><td>USA</td></tr><tr><td>2</td><td>S1002</td><td>21 Bell Road</td><td>Miami</td><td>FL</td><td>USA</td></tr><tr><td>3</td><td>S1003</td><td>Main Street</td><td>New Port</td><td>CA</td><td>USA</td></tr><tr><td>4</td><td>233</td><td>South Street</td><td>New Brunswick</td><td>NJ</td><td>USA</td></tr><tr><td>5</td><td>1233</td><td>JFK Blvd</td><td>Charlotte</td><td>NC</td><td>USA</td></tr></tbody></table>

### Properties of Surrogate Keys

```
- Numerical
- Sequential
- Meaningless Simple Number
```

### **Adv of Surrogate Keys**

```
- Constant Behavior (will not change based on Business need)
- Integration is easier.
- Faster Query Performance. (because of Integer values)
- Future records (every other column can be NULL still ID is available)
```

**Its a good practice to have Surrogate Key in DataWarehouse Dimension & Fact tables.**

