I tried to find it out in google but not satisfactory answer is given out there. Can anybody explain the solid difference.
actually if Primary key is used to select data uniquely then what is the need of Unique key?
When should I use a Primary key and when to use a Unique key?
Primary Key and Unique Key are used for different things - understanding what they are for will help you decide when to use them.
The primary key is used to identify a row of data in a table. It is used whenever you need to refer to a particular row, eg. in other tables or by application code etc. In order to identify a row, the values of a PK must be unique. Furthermore, they can't be null, because most DBMS treat null as not equal to null (since null typically means "unknown"). A table can only have one PK. All tables in your database should have a PK (although this is not enforced by most DBMS), and PK can span multiple columns.
Unique key constraints are used to ensure that data is not duplicated in two rows in the database. One row in the database is allowed to have null for the value of the unique key constraint. Although a table should have a PK, it need not have any additional unique keys. However, tables can have more than one unique key if that meets your needs. Like PKs, unique keys can span multiple columns.
It is also worth knowing that, by default, many DBMS index and physically order tables on disk using the PK. This means that looking up values by their PK is faster than using other values in a row. Typically, however, you can override this behaviour if required.
PK can span multiple columns.? –
Bolanger The term "unique key" is both ambiguous and tautologous. In the relational model, a "key" means a candidate key, which by definition is unique anyway. A primary key is just any one of the candidate keys of a relation. Therefore "unique key" means exactly the same as "candidate key" which means exactly the same as "primary key". There is no difference.
However, SQL has something called a UNIQUE constraint which is subtly different to a SQL PRIMARY KEY constraint - both enforce uniqueness but PRIMARY KEY can only be used once per table. UNIQUE constraints also allow nulls whereas PRIMARY KEY constraints don't.
So the potentially confusing term "unique key" is most often used to mean a key enforced by a UNIQUE constraint. It might even be used to mean a UNIQUE constraint on nullable columns, although that's a very dubious use of the term in my opinion because a set of columns that include nulls cannot be a candidate key so the use of the word "key" for nullable columns isn't really correct and is bound to cause confusion.
Before discussing about difference between Primary Key and Unique Key, it is important to determine what is key, how it plays a role in business and how it is implemented in SQL / Oracle etc.
As per business prospective: For an organization or a business, there are so many physical entities (such as people, resources, machines etc.) and virtual entities (their Tasks, transactions, activities). Typically, business need to record and process information for those business entities. These business entities are identified within whole business domain by a Key.
As per RDBMS prospective: Key(a.k.a Candidate Key), a value or set of values that uniquely identifies entity. For a Db-Table, there are so many keys are exists and might be eligible for Primary Key. So that all keys, primary key, unique key, etc are collectively called as Candidate Key.
For a table DBA selected Candidate Key is called Primary Key, other candidate keys are called secondary keys.
Difference between Primary Key and Unique key
1. Behavior: Primary Key is used to identify a row (record) in a table whereas Unique-key is to prevent duplicate values in a column.
2. Indexing: By default Sql-engine creates Clustered Index on primary-key if not exists and Non-Clustered Index on Unique-key.
3. Nullability: Primary key does not include Null values whereas Unique-key can.
4. Existence: A table can have at most one primary key but can have multiple Unique-key.
5. Modifiability: You can’t change or delete primary values but Unique-key values can.
For more information with examples: http://dotnetauthorities.blogspot.in/2013/11/Microsoft-SQL-Server-Training-Online-Learning-Classes-Integrity-Constraints-PrimaryKey-Unique-Key_27.html
A primary key does not allow nulls, a unique key will allow one null (on sql server and multiple nulls on Oracle) A table can only have one primary key but many unique keys
use primary keys when you want to set up foreign key relationships
Here is a small example with just one column in each table
--primary key table
CREATE TABLE PrimaryTest (id INT PRIMARY KEY NOT NULL)
GO
-- foreign key table
CREATE TABLE ForeignTest (Pkid INT NOT NULL)
GO
--relationship
ALTER TABLE dbo.ForeignTest ADD CONSTRAINT
FK_ForeignTest_PrimaryTest FOREIGN KEY
(
Pkid
) REFERENCES dbo.PrimaryTest
(
id
) ON UPDATE NO ACTION
ON DELETE NO ACTION
GO
insert a row in the primary key table
insert PrimaryTest values(1)
insert a row in the foreign key table with a value that exist in the primary key table
insert ForeignTest values(1)
Now this will fail because value 2 does not exist in the primary key table
insert ForeignTest values(2)
Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_ForeignTest_PrimaryTest". The conflict occurred in database "aspnetdb", table "dbo.PrimaryTest", column 'id'. The statement has been terminated.
A primary key is a unique key. Both types of key serve to uniquely identify a single row in a table. Many RDBMSs require that one of the unique keys on a table be designated as the "primary key", for several different implementation reasons. In terms of data integrity, there is no difference.
Just to add another example:
Think of a table holding user data, where each user has an email address. No two users can have the same email address, so that column becomes a unique key. While it could be the primary key (I have never made a string a primary key), it doesn't have to be.
Both are representing a unique identification to a row in a table but there is little bit difference is that
PRIMARY key does not allows NULL values
while
UNIQUE key allows only one NULL values.
that is the main difference ..
Primary Key constraint
1. A primary key cannot allow null.
2. Multiple primary keys are NOT allowed.
3. On some RDBMS a primary key generates a clustered index by default.
Unique constraint
1. A unique constraint can be defined on columns that allow nulls.
2. Multiple unique keys are allowed.
3. On some RDBMS a unique key generates a nonclustered index by default.
Source Wikipedia
NOT NULL means Any entry in that particular column should not be null. UNIQUE means Each entry in the column should be distinct. PRIMARY KEY means Any entry in the column should be distinct and not null.
So simply..
PRIMARY KEY= UNIQUE + NOT NULL
you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table. More info can be found here
A unique key will served with other key while a primary key does not serve any other key with it. The primary key is used without any association of any other key.
© 2022 - 2024 — McMap. All rights reserved.