Difference between a primary key and a unique key in SQL?

In SQL both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a non-clustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.

There can be more than one unique values for the columns in a table.

There is only one primary key for a column and primary key can be associated with other tables with foreign key relationship.

This is not possible with Unique key

There is a correction

There is only one primary key for a table.

primary key doesnot allow duplicate values and doesnot allow null.
unique key also doesnot allow duplicate values but allows one null value.

There are more then one primary key in a table.Primary Key do not allow any null value.but unique key allow one null value.

when we compare both primary key and unique key,they appear to be similar but their functionalities may differ,bcoz primary key is to serve as identity b/w the two tables as unique key may not have.

Unique Key: Identify the row by uniquely, means if you are inserting a records into the table suppose you already inserted a record with ID 101 and another record you are inserting with the same ID then it will give an error(unique key voilation).

Prmary Key:Once you define a primay key on a particular column then a column become a mandatory field.That means you need to insert something on it, you can not leave as a blank otherwise it will give an error as primary key voilation.

primary key is not be null ...
unique key will be null , this is major diffrent

It seems to me that a primary key is really just a clustered index, unique constraint, and not null constraint. Do primary keys add any real value.
A table can have at most one primary key, but more than one unique key. A primary key is a combination of columns which uniquely specify a row. It is a special case of unique keys. One difference is that primary keys have an implicit NOT NULL constraint while unique keys do not. Thus

Primary Key: primary key is uniquely identifies each row in database.
*NOT NULL
*Must be unique value
*each table shoud have one primary key
-----------------------------------------------
Unique key: Unique Key is doesn't allow NULL Value.
*May ba NULL
*Must be Unique value

One more attribute you missed which is not so big point but it is very major point sometimes to define value in table that

Primary key Supports Auto Increment value
but
Unique doesn't support auto Increment value

So the differences are mainly

1) Primary Key can't not be Null where as Unique may be Null some times if declared.
2) Maximum one Primary Key can be declared in a table where we can declare many Unique keys.
3) Primary key can be associated with another table's Foreign Key whereas It is impossible for Unique field.
4) And with Auto Increment field we can generate ID Automatically where we are unable to do that with Unique.

unique and primary both primary key and unique enforce uniqueness of the column on which they are defined.there is one primary key in the table and many unique key.primary key can't be null value where as unique may be null.one primary key and many unique key in a single table .which uniquely identifie the table

unique and primary both primary key and unique enforce uniqueness of the column on which they are defined.there is one primary key in the table and many unique key.primary key can't be null value where as unique may be null.one primary key and many unique key in a single table .which uniquely identifie the table

primary key is not be null ................
unique key will be null , this is major diffrent...............................................................................................................................................................................................

major difference is
primary key doesnt allow null value,and supports auto increament where as unique key allow null value and doesnt support autoincreament...............................................................................................................................................................................................

unique and primary both primary key and unique enforce uniqueness of the column on which they are defined.there is one primary key in the table and many unique key.primary key can't be null value where as unique may be null.one primary key and many unique key in a single table .which uniquely identifie the table

Please enter your name.
*your opinion/answer:
Please enter your name.
*your opinion/answer: Please enter your name.
*your opinion/answer: Please enter your name.
*your opinion/answer: Please enter your name.
*your opinion/answer: Please enter your name.
*your opinion/answer:

unique and primary both primary key and unique enforce uniqueness of the column on which they are defined.
Primary key doesnot allow null value but unique key allows
null value. We can declare only one primary key in a table
but a table can have multiple unique key(column assign).

there is following difference between primary key and unique


primary key:
*apply on only one column in single table
*not allow null values
*create clusterd index on column
Unique Key:
*apply on multiple column in single table
*allow onle one null value
*create non clusterd index on cloumn

primary key doesnt allow null value.........................................................................................................................................................................
unique key will be null.......................

This is one of the best answer so far, I have read online. Just useful information. Very well presented. I had found another good collection of sql keys over internet.
please check out this link...

http://mindstick.com/Articles/d837d16c-d483-4948-ad18-41b405c011bd/?Important%20SQL%20Keys

Both primary key and unique enforce uniqueness of the column on which they are defined.But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. to Know more Click Here

primary key cannot have null value and has uniqueness.a table can have only one primary key.

unique key has uniqueness but can have null values.
a table can have one or more unique key. ...............

To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)Note: In the example above there is only ONE PRIMARY KEY (pk_PersonID). However, the value of the pk_PersonID is made up of two columns (P_Id and LastName).



--------------------------------------------------------------------------------

Primary key automatically create Clustered index when it is created.
A table created with Primary key restricts null values.

Unique key automatically create Non clustered index when it is created.
A table created with Unique key zllows insertion of null values.

Primary key cannot contain null value
unique key contains at least one null value;
...........................................................
..............................................................
............................................................
......................................................

primary key by default create clustered index
unique key by default create non-clustered index,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
................................................................
.......................................................

Primary key does not accept null values
unique key accept null values.
..........................................................
............................................................
..........................................................
..........................................................

There will be one primary key table.
More than one unique key in a table.
.............................................
................................................
................................................
..............................................

kuch bhi bol doge kya???????????
ye sb jhuth hai gumrah kar rae h tmhe?????????/ mauj kato

kaha pade ho chakkar me jb koi nai h takkar samjheo.........................................................................................................................................................................

Differences between Primary Key and Unique Key:

Primary Key
1. A primary key cannot allow null values. (You cannot define a primary key on columns that allow nulls.)
2. Each table can have at most one primary key.
3. On some RDBMS A primary key automatically generates a clustered table index by default.

Unique Key
1. A unique key can allow null values. (You can define a unique key on columns that allow nulls.)
2. Each table can have multiple unique keys.
3. On some RDBMS A unique key automatically generates a non-clustered table index by default.

Unique key allow only one null value (OR) Unique key allow null values.
As per mine it allow null values becoz of any null value is not equal to other null value..So no duplicate as well..





Can any one Justify this.

amit pandey Said..

Differences between Primary Key and Unique Key:

Primary Key
1. A primary key cannot allow null values. (You cannot define a primary key on columns that allow nulls.)
2. Each table can have at most one primary key.
3. On some RDBMS A primary key automatically generates a clustered table index by default.

Unique Key
1. A unique key can allow null values. (You can define a unique key on columns that allow nulls.)
2. Each table can have multiple unique keys.
3. On some RDBMS A unique key automatically generates a non-clustered table index by default.

Primary key can't null and it is declare only once in a table but unique kay can be null and it can be declare multiple times in table..remaining things are same..


-----------
primary key can be become unique kay ,,but its not vice warsa..



.............................................

Primary key can't null and it is declare only once in a table but unique kay can be null and it can be declare multiple times in table..remaining things are same..


-----------
primary key can be become unique kay ,,but its not vice warsa..



.............................................

The column holding the primary key constraint cannot accept
null values.whereas colum holding the unique constraint can
accept null values assume that t3 is a table with two
columns t1 having primary key constraint and t2 having
unique constraint if u try to insert null into t2 it will
accept that values whereas column t1 will not accept null.

Primary key doesn't allow null value but unique key allows null value. We can declare only one primary key in a table but a table can have multiple unique key(column assign).
.........................................................................................................................

There is primary key we define auto increment and primary key basically use for relation between two table.
primary key make foreign key in another table key.



thanks in advance

unique and primary both primary key and unique enforce uniqueness of the column on which they are defined.there is one primary key in the table and many unique key.primary key can't be null value where as unique may be null.one primary key and many unique key in a single table .which uniquely identifie the table

unique and primary both primary key and unique enforce uniqueness of the column on which they are defined.there is one primary key in the table and many unique key.primary key can't be null value where as unique may be null.one primary key and many unique key in a single table .which uniquely identifie the table

There is only one column is pk but unique key have many column, pk doesn't accept null value but uk accept null value, pk reference with fk but un isn't refers to the fk.pk should be declare through constraint but uk isn't declared.................THANK YOU?????!!!!!

primary key and unique key difference check this
www.allinterview.com/showanswers/20536/page2.html
****************************************************************************************************************************
Thank you
***************************************************************************************************

primary key and unique key difference check this
www.allinterview.com/showanswers/20536/page2.html
****************************************************************************************************************************
Thank you
***************************************************************************************************

primary key are the unique identifiers for each row and they must contains unique values.a table can have only one primarykey.It desn't allows null values and duplicate values where as unique key allows only one NULL value,doesn't allow duplicate values

postgresSQL allows multiple null in unique column..Can anyone calrify me on this??

thanks
Dinesh .

Its not strange..

According to the SQL[3] standard and Relational Model theory, a unique key (unique constraint) should accept NULL in several rows/tuples — however not all RDBMS implement this feature correctly.

-from Wikipedia .

Primary Key
1) Primary key will create column data uniqueness in the table.
2) Primary key will create clustered index on column by default.
3) primary key doesnot allow duplicate values and does not allow null in the column.
4) Only one Primary key can be created for a table because it only one clustered index exits in table
5) Multiple columns can be consolidated to form a single primary key
6) There is only one primary key for a column and primary key can be associated with other tables with foreign key relationship.
7) Once you define a primay key on a particular column then a column become a mandatory field. If we leave that column as blank while inserting record it will give an error as primary key voilation.

Unique Key
1) Unique key constraint will provide you a constraint like the column values should retain uniqueness.
2) Unique key will create non-clustered index on column by default
3) unique key also does not allow duplicate values but allows one null value in the column
4) The number of UNIQUE constraints per table is limited by the number of indexes on the table. i.e 249. so multiple unique constraints can be created upto 249 per table.
5) single columns can be consolidated to form a single unique key
6) This is not possible with Unique key
7) if we are inserting record with the same ID then it will give an error(unique key voilation).

follow me on www.facebook.com/sunilyadav85


Primary Key
1) Primary key will create column data uniqueness in the table.

2) Primary key will create clustered index on column by default.

3) Primary key does not allow duplicate values and does not allow null in the column.

4) Only one Primary key can be created for a table because it only one clustered index exits in table

5) Multiple columns can be consolidated to form a single primary key.

6) There is only one primary key for a column and primary key can be associated with other tables with foreign key relationship.

7) Once you define a primary key on a particular column then a column become a mandatory field. If we leave that column as blank while inserting record it will give an error as primary key voilation.

Unique Key
1) Unique key constraint will provide you a constraint like the column values should retain uniqueness.

2) Unique key will create non-clustered index on column by default.

3) unique key also does not allow duplicate values but allows one null value in the column.

4) The number of UNIQUE constraints per table is limited by the number of indexes on the table. i.e. 249. So multiple unique constraints can be created upto 249 per table.

5) Single columns can be consolidated to form a single unique key.

6) This is not possible with Unique key

7) If we are inserting record with the same ID then it will give an error (unique key voilation).

primary key doesn't support null value and it is always unique
unique key also same as primary key but it support one null vale

Single columns can be consolidated to form a single unique key.

6) This is not possible with Unique key

7) If we are inserting record with the same ID then it will give an error (unique key voilation).

Primary Key: A column in a table whose values uniquely identify the
rows in the table. A primary key value cannot be NULL.
Unique Key: Unique Keys are used to uniquely identify each row in the
table. There can be one and only one row for each unique key value. So
NULL can be a unique key.There can be only one primary key for a table but there can be more
than one unique for a table.

primary key and unique key are two important concept in relational database, and used to uniquely identify a row in a table. Both primary key and unique key can identify a row uniquely but there are some subtle difference between them:
1.Unique key in a table can be null, at-least one but primary key can not be null