Primary key vs. Unique key

Comparisons between the primary key and unique key

Well, a key can be defined as a as the basic identifier or access to a row or set of rows in a given database that is generally made of a columns or set of columns. As for a unique key, its wide use is based on relational databases and is used to uniquely identify a row in a table. Like the primary key, the unique key is composed of a column or a set of columns. The primary and unique keys are however different in some respects.

So what is a unique key? As it has already been noted in the introductory part, a primary key can either take the form of a single column or a set of columns and is uniquely used to identify a row or even a set of rows in a given table. The basic fact about the unique key is that it normally does not take to account the NOT NULL constraint although it is restricted in such a way that there can never be any two values of it that are equal , therefore it follows that columns will only allow a single NULL value to be contained in it. NULL in databases is used to indicate lack of value. Since a column will only contain one NULL value it is then possible to identify that whole row uniquely. For example in a table containing employees details the unique key can be set as employee ID number since there is 0% chance of finding two employees with the same ID number. In other words, the uniqueness of employee ID can be used to identify the other attributes of the employee contained in that particular row. A table may contain one or more unique keys depending on the nature of the database.

While a primary key like the unique key contains a column or even a set of columns that identifies a row, the difference between it and the unique key is that its application is normally limited to relational databases. It is worth stating that a table can only be assigned one primary key only. Unlike the unique key, the primary key in a relational database take into account the implicit as opposed to the NULL constraint  which further leads us to necessarily conclude that a  primary key cannot contain a NULL value. A primary key has to be uniquely assigned or better still be generated by a convenient application such as GUID common to many Microsoft SQL servers. Primary keys are normally created for every table and are definitely implicitly defined that they can never contain a null value.

The distinction between the primary and unique keys in any database is based on the methodology of using either although their general structure in term of containing columns and sets of column is somehow the same. While you cannot have more than one primary key in a table, the reverse is true for unique key where you can assign more that one in a single table. In terms of constraints, the primary key usually has an implicit NOT NULL constraint while the unique key does not. This means that as for unique keys, they may or may not contain a NULL value while primary keys cannot contain a NULL value.


  • Cksnou

    gud article