Prerequisite – Keys in Relational Model
A primary key is a column of table which uniquely identifies each tuple [row] in that table. Primary key enforces integrity constraints to the table. Only one primary key is allowed to use in a table. The primary key does not accept the any duplicate and NULL values. The primary key value in a table changes
very rarely so it is chosen with care where the changes can occur in a seldom manner. A primary key of one table can be referenced by foreign key of another table.
For better understanding of primary key we take table named as Student table, having attributes such as Roll_number, Name, Batch, Phone_number, Citizen_ID.
The roll number attribute can never have identical and NULL value, because every student enrolled in a university can have unique Roll_number, therefore two students cannot have same Roll_number and each row in a table is uniquely identified with student’s roll number. So, we can make Roll_number attribute as a primary key in this case.
Unique key constraints also identifies an individual tuple uniquely in a relation or table. A table can have more than one unique key unlike primary key. Unique key constraints can accept only one NULL value for column. Unique constraints are also referenced by the foreign key of another table. It can be used when someone wants to enforce unique constraints on a column and a group of columns which is not a primary key.
For better understanding of unique key we take Student table with Roll_number, Name, Batch, Phone_number, and Citizen_ID attributes.
Roll number attribute is already assigned with the primary key and Citizen_ID can have unique constraints where each entry in a Citizen_ID column should be unique because each citizen of a country must have his or her Unique identification number like Aadhaar Number. But if student is migrated to another country in that case, he or she would not have any Citizen_ID and the entry could have a NULL value as only one NULL is allowed in the unique constraint.
Key Differences Between Primary key and Unique key:
Primary key will not accept NULL values whereas Unique key can accept NULL values.
- A table can have only one primary key whereas there can be multiple unique key on a table.
- A Clustered index automatically created when a primary key is defined whereas Unique key generates the non-clustered index.
Comparison Chart:
Basic | Used to serve as a unique identifier for each row in a table. | Uniquely determines a row which isn’t primary key. |
NULL value acceptance | Cannot accept NULL values. | Can accepts NULL values. |
Number of keys that can be defined in the table | Only one primary key | More than one unique key |
Index | Creates clustered index | Creates non-clustered index |
Auto Increment | A Primary key supports auto increment value. | A unique key does not supports auto increment value. |
Modification | We cannot change or delete values stored in primary keys. | We can change unique key values. |
- Online Practice Test >
- Oracle
- « Previous
- Next »
Options
- Primary key can not have null values
- Primary key may contain duplicate
values
- Primary key can not be applied for multiple columns
- All of the above
CORRECT ANSWER : Primary key may contain duplicate values
Discussion Board |
Primary key Primary key may not contain duplicate values. Primary Key is a combination of Unique and NOT NULL Constraints so it can’t have duplicate values or any NULL values. A primary key is a special relational database table column [or combination of columns] designated to uniquely identify all table records. It must contain a unique value for each row of data. Prajakta Pandit 02-6-2017 04:20 AM |
"primary key may contains duplicate values" is also true "primary key may contains duplicate values" is also true b'coz of initially deferred option in primary key constraint Wajid 01-9-2015 09:01 AM |
two corect answers Primary key can be composite - made up of two or more columns. Thus the third option[Primary key can not be applied for multiple columns][false] correct as well. Nick 08-22-2014 04:50 PM |
- « Previous
- Next »
Write your comments
* |
* Email must be in the form |
* |
* |
Enter the code shown above:
Please enter the code shown above
[Note: If you cannot read the numbers in the above image, reload the page to generate a new one.]