Primary Key, Unique Key
Exploration of different types, concepts and meanings about “keys”. Discussion includes primary keys, unique keys, the Key+ column on the Design page and more.
Primary Key
In the relational database model a primary key is a specific choice of a minimal set of columns that uniquely specify or identify a row in a table (relation). Most tables have more than one sets of columns that can be combined to make a unique key – a single column or combination of columns in a table that ensures every row in the table is uniquely identified. A primary key can be based on one or more columns, such as first and last name, a sales order number, etc.
The primary key provides a means to uniquely identity every row, using data within the row.
Unique Keys, Candidate Keys/Alternate Keys
In relational database management systems, a every unique key is known as a candidate key or alternate keys. All the candidate keys of a table can uniquely identify the rows (records) of the table, but only one of them is used as the primary key of the table. The remaining candidate keys are called unique keys because they can uniquely identify a record in a table. Unique keys can consist of multiple (concatenated) columns.
Related Concepts
Key+ Logical Key
The Design page designates one or more columns/fields as logical keys. They are called logical keys because the keys or indexes may only exist temporarily during the Load process.
Depending on context the Key+ keys may or may not be unique or primary keys.
For more about Key+ logical keys see ETL+ Design Page.
Identity Columns in MS SQL Server
SQL Server has a feature called Identity columns that can be used for generating key values. The identity property on a column generates values from a seed and an increment. https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-ver16
Unique Identifier – Universally Unique Identifier or Globally Unique Identifier
Unique identifier is a term often used as a type of key that ensure uniqueness across different systems or databases. Also known as a UUID (Universally Unique Identifier) or GUID (Globally Unique Identifier). A unique identifier is a specific type of identifier used to uniquely identify a resource or entity that is unique across different systems or databases. Unique identifiers are typically used when there is a need for globally unique identification such as when integrating data from multiple sources.
In summary, a unique key is a database concept used to enforce uniqueness within a table, while a unique identifier is a more general term referring to a unique identification code or value, often used for cross-system or global identification purposes.