sql - How is primary key is different from unique key when both are actually serving the same purpose as per my understanding?

Question

Primary key is actually the one which cannot be repeated for more than one entries so as the same for unique key as far as I know.

Let's say, we take employee IDs as primary keys for 100 number of employees, this means that employee ID for two employees cannot and can never be same.

But then, what is the unique key? As employee ID is a unique identifier for each employee. What I just know is that the primary key cannot cater Null values whereas unique key can cater just one Null value.

But is that actually the difference between the two? Can someone, make it more easily understandable preferably with a code example.

Also, after differentiating between two, how can we define both in a single dataset? Are there any set rules which we have to follow.

question from:https://stackoverflow.com/questions/65860803/how-is-primary-key-is-different-from-unique-key-when-both-are-actually-serving-t

Answer 1

A primary key has three properties:

• The key is unique across all rows of a table.
• The key (or no components of a composite key) are NULL.
• There is only one per table.

In general, primary keys are used for foreign key relationships. They are typically integers, because that is somewhat more efficient for indexing.

Other columns or combinations of columns can be unique and non-NULL. Those are candidate primary keys. However, a table has only one primary key.