Difference Between Primary Key and Unique Key

difference between primary key and unique key

Primary Key:

• There can only be one primary key constraint in a table
• In some DBMS it cannot be NULL - e.g. MySQL adds NOT NULL
• Primary Key is a unique key identifier of the record

Unique Key:

• Can be more than one unique key in one table
• Unique key can have NULL values
• It can be a candidate key
• Unique key can be NULL ; multiple rows can have NULL values and therefore may not be considered "unique"

Difference between Primary Key and Unique Index in SQL Server

From SQL UNIQUE Constraint

The UNIQUE constraint uniquely identifies each record in a database
table.

The UNIQUE and PRIMARY KEY constraints both provide a
guarantee for uniqueness for a column or set of columns.

A PRIMARY
KEY constraint automatically has a UNIQUE constraint defined on it.

Note that you can have many UNIQUE constraints per table, but only one
PRIMARY KEY constraint per table.

Also, from Create Unique Indexes

You cannot create a unique index on a single column if that column
contains NULL in more than one row. Similarly, you cannot create a
unique index on multiple columns if the combination of columns
contains NULL in more than one row. These are treated as duplicate
values for indexing purposes.

Whereas from Create Primary Keys

All columns defined within a PRIMARY KEY constraint must be defined as
NOT NULL. If nullability is not specified, all columns participating
in a PRIMARY KEY constraint have their nullability set to NOT NULL.

What are the main differences between a primary key and a unique constraint?

Primary column can never be null, a unique column can be.

In SQL, why do we need a primary key if we can use NOT NULL and UNIQUE constraints in place of a primary key?

The definition of a primary key is:

• A primary key is unique.
• A primary key is not null.
• Table has only one primary key.

You are asking about the third condition. Well, that is the definition. The "primary key" is a single set of keys that have been explicitly chosen to uniquely identify each row in the table. The word "primary" implies that there is only one per table. Other keys or combinations of keys that meet the first two conditions are called candidate primary keys.

Although not strictly enforced, primary keys are the best method for referencing individual rows. They should be used for foreign key constraints, for instance (and any database that I come into contact with does enforce primary keys for foreign key constraints). Having multiple different keys refer to a single table confuses the data model. Think about Entity-Relationship modeling. The links should be primary keys.

To give a flavor of the use of primary keys, some databases (such as MySQL using the InnoDB storage engine) by default cluster tables based on the primary key. A table can only be clustered once, hence the use of a single key.

What is the difference b/w Primary Key and 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.

what is difference between primary and unique key?

A table in MySQL can only have one Primary Key at most, while you can create as many unique Keys or indexes as you want.

Also a primary key is not nullable while a unique key can have NULL as value.

But the biggest difference is the purpose:

You want to have a primary key because you need an identifier

The unique Key/Index on the other hand is usefull to controll values that are automatically getting inserted into your table (e.g. to avoid duplicates where none are allowed)

If you want to use a column as a forgein key, you need to define it as a primary key first. Unique Constraint can not be related with other tables as a foreign key.

What is the difference between a primary key and a unique constraint?

A primary key is a unique field on a table but it is special in the sense that the table considers that row as its key. This means that other tables can use this field to create foreign key relationships to themselves.

A unique constraint simply means that a particular field must be unique.

---

Related Topics