Unique key related problem in SQL Server

Question

I am very confused in difference between unique key and primary key, and unique key constraint and primary key constraint in SQL Server.

Can I define more than 1 primary key in my table?

Again I am not getting how to set unique key in my table and what is the practical use of it. Second thing, I found statement like this on internet

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.

Does it mean that when I create primary key, it automatically define the unique key on that, that is I can have only 1 null value inserted in it?

Answer 1

your primary key is always unique. you can add additional keys and the may or may not be unique. If i am not wrong, the primary key decides how the records are actually stored on the disk.

Answer 2

You cannot have more than one primary key on a table, but you can have as many (within reasonable technical limits) unique constraints on a table.

The primary key cannot contain nulls, a unique constraint does allow nulls to occur, but treats NULLs as values in this case (so for a single column constraint, only one null value can occur).

Both Primary Keys and Unique constraints may be the referenced key for a foreign key constraint.

---

Many relational purists believe that the introduction of "Primary Key" in SQL was a mistake - there's no real reason to select one key as "better" than any other key - if there are genuinely multiple keys for the table. You could abandon primary keys entirely and only use UNIQUE constraints, and treat all keys equally.

Answer 3

Ok:

• you can have only one primary key for each table
• the primary key is by definition unique and does not allow NULL values

On the other hand:

• you can define as many unique indices as you like

The primary key is a very special thing - it defines the way to clearly and uniquely identify each row. Therefore it must be unique, and it cannot be NULL.

The unique index is just that - a mechanism to ensure something (a column, several columns toegther) are unique. You can have multiple of those constraints on a table.

Each unique index could be your primary key - it's a so-called candidate key. But only one of those can be picked and chosen to actually be your primary key.