Primary Key Generated From External System

Question

What are the general guidelines for the following scenario:

You need to create a table where the PK is a value that is generated from an external system. It is unique, similar to using a SSN.

Does it make sense for this to be the primary key? Especially considering it will be used in foreign key relationships with two other tables. Or, would it be better to create an auto-incrementing ID field in addition to the unique key, and use the auto-incrementing ID in the table relationships?

Answer 1

Personally, I wouldn't trust an ID from an external system. Sure, they'll promise you today that it will be unique, never change and never be reused. But then, some day in the future...

Create your own auto-incrementing ID and use that for the PK and FK relationships. Keep their ID only as a reference.

Answer 2

I would always create my own surrogate primary key, and set the natural identifier as a secondary unique key.

Scott W Ambler has a good comparison of the two strategies here.