Unique key

Key takeaways

Unique key belongs to science; place it in that map before memorising details.
Learn the definition first, then one example that makes the definition concrete.
Connect Unique key to a quantity you can measure, compute or draw — that is where exam questions come from.
Reproduce the core statement of Unique key from memory before moving on to harder problems.

Reference excerpt

In relational database management systems, a unique key is a candidate key. All the candidate keys of a relation can uniquely identify the records of the relation, but only one of them is used as the primary key of the relation. The remaining candidate keys are called unique keys because they can uniquely identify a record in a relation. Unique keys can consist of multiple columns. Unique keys are also called alternate keys. Unique keys are an alternative to the primary key of the relation. In SQL, the unique keys have a UNIQUE constraint assigned to them in order to prevent duplicates (a duplicate entry is not valid in a unique column). Alternate keys may be used like the primary key when doing a single-table select or when filtering in a where clause, but are not typically used to join multiple tables.

Summary Keys provide the means for database users and application software to identify, access and update information in a database table. There may be several keys in any given table. For example, in a table of employees, both employee number and login name are individually unique. The enforcement of a key constraint (i.e. a uniqueness constraint) in a table is also a data integrity feature of the database. The DBMS prevents updates that would cause duplicate key values and thereby ensures that tables always comply with the desired rules for uniqueness. Proper selection of keys when designing a database is therefore an important aspect of database integrity. A relational database table may have one or more available unique keys (formally called candidate keys). One of those keys per table may be designated the primary key; other keys are called alternate keys. Any key may consist of one or more attributes. For example, a Social Security Number might be a single attribute key for an employee; a combination of flight number and date might be a key consisting of two attributes for a scheduled flight. There are several types of keys used in database modeling and implementations.

At the most basic definition, "a key is a unique identifier", so unique key is a pleonasm. Keys that are within their originating entity are unique within that entity. Keys that migrate to another entity may or may not be unique, depending on the design and how they are used in the other table. Foreign keys may be the primary key in another table; for example a PersonID may become the EmployeeID in the Employee table. In this case, the EmployeeID is both a foreign key and the unique primary key, meaning that the tables have a 1:1 relationship. In the case where the person entity contained the biological father ID, the father ID would not be expected to be unique because a father may have more than one child. Here is an example of a primary key becoming a foreign key on a related table. ID migrates from the Author table to the Book table.

Here ID serves as the primary key in the table 'Author', but also as AuthorID serves as a Foreign Key in the table 'Book'. The Foreign Key serves as the link, and therefore the connection, between the two related tables in this sample database. In a relational database, a candidate key uniquely identifies each row of data values in a database table. A candidate key comprises a single column or a set of columns in a single database table. No two distinct rows or data records in a database table can have the same data value (or combination of data values) in those candidate key columns since NULL values are not used. Depending on its design, a database table may have many candidate keys but at most one candidate key may be distinguished as the primary key. A key constraint applies to the set of tuples in a table at any given point in time. A key is not necessarily a unique identifier across the population of all possible instances of tuples that could be stored in a table but it does imply a data integrity rule that duplicates should not be allowed in the database table. Some possible examples of keys are Social Security Numbers, ISBNs, vehicle registration numbers or user login names. In principle any key may be referenced by foreign keys. Some SQL DBMSs only allow a foreign key constraint against a primary key but most systems will allow a foreign key constraint to reference any key of a table.

Defining keys in SQL The definition of keys in SQL:

Likewise, keys can be defined as part of the CREATE TABLE SQL statement.

Differences between primary key constraint and unique constraint Primary key constraint

A primary key cannot allow null (a primary key cannot be defined on columns that allow nulls). Each table cannot have more than one primary key. On some RDBMS a primary key generates a clustered index by default. Unique constraint

A unique constraint can be defined on columns that allow nulls, in which case rows that include nulls may not actually be unique across the set of columns defined by the constraint. Each table can have multiple unique constraints. On some RDBMS a unique constraint generates a nonclustered index by default. Note that unlike the PRIMARY KEY constraint a UNIQUE constraint does not imply NOT NULL for the columns participating in the constraint. NOT NULL must be specified to make the column(s) a key. It is possible to put UNIQUE constraints on nullable columns but the SQL standard states that the constraint does not guarantee uniqueness of nullable columns (uniqueness is not enforced for rows where any of the columns contains a null). According to the SQL standard a unique constraint does not enforce uniqueness in the presence of nulls and can therefore contain several rows with identical combinations of nulls and non-null values — however not all RDBMS implement this feature according to the SQL standard.

See also Globally unique identifier (GUID), also known as universally unique identifier (UUID) Persistent object identifier (POID), a unique identifier of a record on a table, used as the primary key

References

External links Relation Database terms of reference, Keys: An overview of the different types of keys in an RDBMS

Worked examples

Example 1 — a first encounter with Unique key

Start with the simplest possible case. Write down what Unique key claims or describes in one sentence, then invent the smallest concrete situation in which that sentence is true. In science, the smallest case is usually a single object, a single equation or a single measurement. Check that every symbol or term in your sentence has a meaning in that case.

Example 2 — changing one variable

Take the situation from Example 1 and change exactly one quantity: double it, halve it, or set it to zero. Predict what should happen to Unique key before you calculate. Comparing your prediction with the result is the fastest way to find out whether you understand the idea or only the words.

Example 3 — an exam-style question

Typical questions about Unique key ask you to (a) state it precisely, (b) apply it to given data, and (c) explain a limitation. Practise writing all three answers in under five minutes; the third part is what separates a full-mark answer from an average one.

Applications of Unique key

In research: Unique key appears in science research whenever the underlying quantities have to be modelled precisely. Papers usually cite it as a starting assumption and then explore where it breaks down.
In technology and industry: Engineering practice reuses Unique key in design rules, simulations and safety margins. Knowing the idea lets you read a specification sheet and understand why the numbers look the way they do.
In the classroom: Unique key is common in secondary-school and first-year university syllabi. It links to neighbouring topics Data modeling, Database management systems, so understanding it makes those chapters shorter.
In everyday life: Look for Unique key outside the textbook — in sport, cooking, traffic, electronics or the sky above you. An example you found yourself is remembered far longer than one you were given.

How to study Unique key in 20 minutes

Read the reference excerpt below once, without taking notes.
Close the page and write down what Unique key means in your own words.
Compare your version with the excerpt and mark what you missed.
Work through the three examples above with pen and paper.
Explain Unique key out loud to somebody else — or to Teacher Smith in the lgStudy chat.

Frequently asked questions

What is Unique key in simple terms?

In relational database management systems, a unique key is a candidate key. All the candidate keys of a relation can uniquely identify the records of the relation, but only one of them is used as the primary key of the relation.

Why does Unique key matter?

Because it connects several science ideas at once: it gives you a definition you can apply, a quantity you can calculate, and a way to check whether a result is plausible.

How should I study Unique key?

Read the excerpt, restate it from memory, then work through the examples and applications listed on this page. The five-step study plan above takes about twenty minutes.

What does this page cover?

It gives you a compact reference excerpt plus original lgStudy explanations, examples, applications and study material on Unique key.