Differencebetween a Primary Key and a Unique Constraint

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.

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

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

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"

Primary Key versus Unique Constraint?

Can you provide references to these articles?

I see no reason to change the tried and true methods. After all, Primary Keys are a fundamental design feature of relational databases.

Using UNIQUE to serve the same purpose sounds really hackish to me. What is their rationale?

Edit: My attention just got drawn back to this old answer. Perhaps the discussion that you read regarding PK vs. UNIQUE dealt with people making something a PK for the sole purpose of enforcing uniqueness on it. The answer to this is, If it IS a key, then make it key, otherwise make it 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.

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.

MySQL PRIMARY KEY vs UNIQUE constraints

As a preface, note that a Primary Key does not need to be a single column: it can be comprised of multiple columns: this is known as a Composite Key. Also note that not every table has an AUTO_INCREMENT/IDENTITY column at all, and you can have a UNIQUE constraint on a single column inside a composite-key anyway.

There is none - but it doesn't make sense for a DBMS to prohibit such redundancy either because you'd need added logic and complexity to handle that condition, whereas there's no real harm done by having both (besides the performance impact of having to maintain two indexes).
As said above: because the opportunity cost of detecting and preventing that redundancy isn't worth it.

Another thing to consider is that the primary-key definition of a table is not immutable and therefore is subject to change. A table might already have columns with a UNIQUE constraint set-up and then the database-designer decides to include that in a new definition of the primary-key - it would be a user-unfriendly to require the old constraint be removed first, especially if other parts of their application system depend on that UNIQUE constraint being there (e.g. a 1:0..1 relationship definition).

(Also, AUTO_INCREMENT is not mutually-inclusive with UNIQUE or PRIMARY KEY: you can use AUTO_INCREMENT with non-unique columns (e.g. if AUTO_INCREMENT is added after a table already contains data), and contrarywise a PRIMARY KEY can use unique values sourced from elsewhere, such as another identity column as a foreign key (composite primary keys can contain foreign keys!) or a "natural" data source, such as using a US Social Security Number as a Primary Key (of course you should never do this in reality)).