Difference Between Primary key and Unique key

Primary key vs Unique key

A column or a set of columns, which can be used to identify or access a row or a set of rows in a database is called a key. A unique key is a key that can uniquely identify a row in a table in the context of relational databases. A unique key is made up of a single column or a set of columns. A primary key is also a combination of columns in a table that uniquely identify a row. But it is considered to be a special case of the unique key.

Read More: Oracle Database Certifications

What is Unique Key?

As mentioned earlier, unique key is a single column or set of columns that can uniquely identify a row in a table. So, a unique key is constrained such that no two values of it are equal. One important property is that the unique keys do not enforce the NOT NULL constraint. Since NULL represents the lack of a value, if two rows have NULL in a column then it does not mean the values are equal. Column defined as a unique key allows only a single NULL value in that column. Then that can be used to identify that particular row uniquely. For example, in a table that contains student information, student ID can be defined as a unique key. Since no two students can have the same ID it uniquely identifies a single student. So the student ID column satisfies all the properties of a unique key. Depending on the design of a database, a table may have more than one unique key.

What is Primary Key?

Primary key is also a column or a combination of columns that uniquely defines a row in a table of a relational database. A table can have at most one primary key. Primary key enforces the implicit NOT NULL constraint. So, a column that is defined as the primary key cannot have NULL values in it. Primary key can be a normal attribute in the table that is guaranteed to be unique such as social security number or it could be a unique value generated by the database management system such as a Globally Unique Identifier (GUID) in Microsoft SQL Server. Primary keys are defined through the PRIMARY KEY constraint in ANSI SQL Standard. Primary key can also be defined when creating the table. SQL allows primary key to be made up of one or more columns and each column that is included in the primary key is implicitly defined to be NOT NULL. But some database management systems require making the primary key columns explicitly NOT NULL.

Difference Between Primary key and Unique key

Even though both the primary key and unique key are one or more columns that can uniquely identify a row in a table, they have some important differences. Most importantly, a table can have only a single primary key while it can have more than one unique key. Primary key can be considered as a special case of the unique key. Another difference is that primary keys have an implicit NOT NULL constraint while the unique key does not have that constraint. Therefore, unique key columns may or may not contain NULL values but primary key columns cannot contain NULL values.

Source: differencebetween.com

Continue reading

Difference Between Primary Key and Candidate Key

Primary Key vs Candidate Key

Though primary key is selected from the candidate keys there exist some difference between the primary key and the other candidate keys, which will be discussed in detail in this article. Database designing is one of the most important activity that should be done when maintaining and storing data. During this designing process, different tables with many relationships have to be created. In order to access these tables in a database, different types of keys are used in modern database designing languages such as MYSQL, MSAccess, SQLite, etc. Out of these keys, candidate keys and primary keys have become essential in database designing practices.

What is a Candidate Key?

Candidate key is a single column or set of columns in a table of a database that can be used to uniquely identify any database record without referring to any other data. Each table of a database can have one or more than one candidate keys. A set of candidate keys can be created by using functional dependencies. There are some important features in a candidate key. They are;

• candidate keys should be unique within the domain and they should not contain any NULL values.

• the candidate key should never change, and it must hold the same value for a specific occurrence of an entity.

The main purpose of a candidate key is to help to identify one single row out of millions of rows in a big table. Each candidate key is qualified to become a primary key. However, out of all the candidate keys, the most important and special candidate key will become the primary key of a table and it is the best among the candidate keys.

What is a Primary Key?

A primary key is the best candidate key of a table that is used to uniquely identify records that are stored in a table. When creating a new table in a database we are asked to select a primary key. Therefore, selection of a primary key for a table is the most critical decision that should be taken by a database designer. The most important constraint, which should be considered when deciding a primary key, is that the selected column of the table should only contain unique values, and it should not contain any NULL values. Some of the primary keys that are commonly used when designing tables are Social Security Number (SSN), ID and National Identity Card Number (NIC).

The programmer should remember to choose a primary key carefully because it is difficult to change. Therefore, according to the programmers, the best practice of creating a primary key is to use an internally generated primary key such as Record ID created by AutoNumber data type of MS Access. If we try to insert a record into a table with a primary key that duplicates an existing record, the insertion will fail. The primary key value should not keep changing, so it is more important to keep a static primary key.

A primary key is the best candidate key.

What is the difference between Primary Key and Candidate Key?

• A candidate key is the column that qualifies as unique whereas primary key is the column that uniquely identifies a record.

• A table without candidate keys does not represent any relation.

• There can be many candidate keys for a table in a database, but there should be only one primary key for a table.

• Although the primary key is one of the candidate keys, sometimes it is the only candidate key.

• Once a primary key was selected the other candidate keys become unique keys.

• Practically a candidate key can contain NULL values although it presently does not contain any value. Therefore, the candidate key is not qualified for a primary key because the primary key should not contain any NULL values.

• It may also be possible that candidate keys, which are unique at the moment, may contain duplicate values that disqualify a candidate key from becoming a primary key.

Source: differencebetween.com

Continue reading