Editing Surrogate key (section)

===Disassociation===
The values of generated surrogate keys have no relationship to the real-world ''meaning'' of the data held in a row. When inspecting a row holding a foreign key reference to another table using a surrogate key, the meaning of the surrogate key's row cannot be discerned from the key itself. Every foreign key must be joined to see the related data item. If appropriate database constraints have not been set, or data imported from a legacy system where [[referential integrity]] was not employed, it is possible to have a foreign-key value that does not correspond to a primary-key value and is therefore invalid. (In this regard, [[C.J. Date]] regards the meaninglessness of surrogate keys as an advantage.<ref>C.J. Date. The primacy of primary keys. From "Relational Database Writings, 1991-1994. Addison-Wesley, Reading, MA.</ref>)

To discover such errors, one must perform a query that uses a left [[outer join]] between the table with the foreign key and the table with the primary key, showing both key fields in addition to any fields required to distinguish the record; all invalid foreign-key values will have the primary-key column as NULL. The need to perform such a check is so common that Microsoft Access actually provides a "Find Unmatched Query" wizard that generates the appropriate SQL after walking the user through a dialog. (It is, however, not too difficult to compose such queries manually.) "Find Unmatched" queries are typically employed as part of a [[data cleansing]] process when inheriting legacy data.

Surrogate keys are unnatural for data that is exported and shared. A particular difficulty is that tables from two otherwise identical schemas (for example, a test schema and a development schema) can hold records that are equivalent in a business sense, but have different keys. This can be mitigated by not exporting surrogate keys, except as transient data (most obviously, in executing applications that have a "live" connection to the database).

When surrogate keys supplant natural keys, then domain specific [[referential integrity]] will be compromised. For example, in a customer master table, the same customer may have multiple records under separate customer IDs, even though the natural key (a combination of customer name, date of birth, and e-mail address) would be unique. To prevent compromise, the natural key of the table must not be supplanted: it must be preserved as a [[unique constraint]], which is implemented as a unique index on the combination of natural-key fields.