What do you call something that’s not the primary key

terminology

I've been working with a group who have built a system where they've coined a few new terms in the process, and I wanted to know if there's something more standardized they should be calling it.

Basically, they have tables which hold versioned info, so the unique key for the table is the identifier for the document being tracked, plus the record number (although, the record number is auto-numbered, so it's unique in itself). They refer to the fields that make up the document's identifier (it changes for each table), as the 'prime key' … but the fields both individually and collectively (eg, if it has fields A, B and C to get a unique identifier, they might interchangable refer to A as being 'a prime key' and A/B/C as being 'the prime key'

If they had a normalized database, this would be an obvious candidate for a foreign key — but it's not normalized, so it's not a reference to a different table.

Is there some name for this concept — an identifier to select some concept that's not what the table is based on, but which isn't a foreign key?


update: it's not an alternate key, as it doesn't identify a unique record within the table; it identifies a group of records. Take the folowing table:

id   document_id   version_no
--   -----------   ----------
 1             1            1
 2             2            1
 3             1            2
 4             2            2
 5             2            3

So we're tracking metadata for 5 objects, which are two different files, one of which has been updated once (2 versions/editions of it), and one which has been updated twice.

We have the following:

  • candidate keys : (id) or (document_id, version_no)
  • natural key : (document_id, version_no)
  • surrogate key : (id)
  • primary key : (id, document_id) (I didn't select it)

… if document_id were a relationship to another table, it'd be a foreign key, but 'foreign key' typically also infers a constraint (ie, the value has to exist in some other table before it can be used here) … but in this case, it's not. So, is there some other term to describe this as something that selects a related grouping of objects rather than a single record out of the table?

The first definition of surrogate on the wikipedia page for surrogate key fits, but that's not the common usage of the term, where most people consider a surrogate key to fit the second definition:

  • Surrogate (1) : This definition is based on that given by Hall, Owlett and Todd (1976). Here a surrogate represents an entity in the outside world. The surrogate is internally generated by the system but is nevertheless visible to the user or application.
  • Surrogate (2) :
    This definition is based on that given by Wieringa and De Jonge (1991). Here a surrogate represents an object in the database itself. The surrogate is internally generated by the system and is invisible to the user or application.

update 2 : Basically, if you had a table that handled versioning of records, if you moved the records to a new table and stored editing history, the fields that link back to the original table would be considered a foreign key … but if you stored all of it in one table, without relating to a secondary table, is there a name for the group of fields that identify the multiple versions of the record?

The reason I ask is that the group I'm working with has decided to call it a 'prime key', and when I came on board, I reviewed all of their documentation, and told them they had a typo, and it was the 'primary key', but they explained that no, it was the phrase they had coined to describe this concept.

I'm trying to identify if there's a accepted name in the database community for this concept.

Best Answer

User terms surrogate key and natural key for primary key as a variant.

Surrogate Key:

Surrogate keys are keys that have no “business” meaning and are solely used to identify a record in the table. Such keys are either database generated (example: Identity in SQL Server, Sequence in Oracle, Sequence/Identity in DB2 UDB etc.) or system generated values (like generated via a table in the schema).

Natural Key:

Keys are natural if the attribute it represents is used for identification independently of the database schema. What this basically means is that the keys are natural if people use them example: Invoice-Numbers, Tax-Ids, SSN etc.

Surrogate Keys vs Natural Keys for Primary Key