Difference between Primary Key and Unique Index in SQL Server [duplicate]

Question

My company is currently in the process of rewriting an application that we recently acquired. We chose to use ASP.net mvc4 to build this system as well as using the Entity Framework as our ORM. The previous owner of the company we acquired is very adamant that we use their old database and not change anything about it so that clients can use our product concurrently with the old system while we are developing the different modules.

I found out that the old table structures does not have a Primary key, rather, it uses a Unique Index to serve as their primary key. Now when using Entity framework I have tried to match their tables in structure but have been unable to do so as the EF generates a Primary key instead of a unique index.

When I contacted the previous owner, and explained it, he told me that "the Unique key in every table is the Primary Key. They are synonyms to each other."

I am still relatively new to database systems so I am not sure if this is correct. Can anyone clarify this?

his table when dumped to SQL generates:

-- ----------------------------
-- Indexes structure for table AT_APSRANCD
-- ----------------------------
CREATE UNIQUE INDEX [ac_key] ON [dbo].[AT_APSRANCD]
([AC_Analysis_category] ASC, [AC_ANALYSI_CODE] ASC) 
WITH (IGNORE_DUP_KEY = ON)
GO

however my system generates:

-- ----------------------------
-- Primary Key structure for table AT_APSRANCD
-- ----------------------------
ALTER TABLE [dbo].[AT_APSRANCD] ADD PRIMARY KEY ([AC_Analysis_category])
GO

EDIT: Follow up question to this is how would I go about designing the Models for this? I am only used to using the [Key] annotation which defines it as a primary key, and without it, EF will not generate that table. so something like this:

[Table("AT_APSRANCD")]
public class Analysis
{
    [Key]
    public string AnalysisCode { get; set; }
    public string AnalysisCategory { get; set; }
    public string ShortName { get; set; }
    public string LongName { get; set; }
}

Answer 1

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.

Answer 2

They're definitely different. As mentioned in other answers:

• Unique key is used just to test uniqueness and nothing else
• Primary key acts as an identifier of the record.

Also, what's important is that the primary key is usually the clustered index. This means that the records are physically stored in the order defined by the primary key. This has a big consequences for performance.

Also, the clustered index key (which is most often also the primary key) is automatically included in all other indexes, so getting it doesn't require a record lookup, just reading the index is enough.

To sum up, always make sure you have a primary key on your tables. Indexes have a huge impact on performance and you want to make sure you get your indexes right.

Answer 3

They are most certainly not the same thing.

A primary key must be unique, but that is just one of the its requirements. Another one would be that it cannot be null, which is not required of a unique constraint.

Also, while, in a way, unique constraints can be used as a poor man's primary keys, using them with IGNORE_DUP_KEY = ON is plainly wrong. That setting means that if you try to insert a duplicate, the insertion will fail silently.