Difference between Primary Key and Unique Index in SQL Server

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; }
}

Solution 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.

Solution 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.

Solution 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.

Solution 4

Well, they are very similar but here are the differences.

Only one primary key is allowed on a table but multiple unique indexes can be added up to the maximum allowed number of indexes for the table (SQL Server = 250 (1 x clustered, 249 x non clustered) and SQL 2008 and SQL 2012 = 1000 (1 x clustered, 999 x non clustered)). Primary keys cannot contain nullable columns but unique indexes can. Note, that only one NULL is allowed. If the index is created across multiple columns, each combination of values and NULL’s must be unique.

By default, unless you specify otherwise in the create statement and providing that a clustered index does not already exists, the primary key is created as a clustered index. Unique indexes however are created by default as non clustered indexes unless you specify otherwise and providing that a clustered index does not already exist.

Following link will really help you.just go with it

HERE

Solution 5

Yes, a composite and unique key, like you have here, will give you an index very much like the primary key. One of the advantages of these are that the data is contained in the index, so it does not have to do a look up in the table if you are only querying for the fields in the key.

This is also possible in Entity Framework. It would go something like this.

public class AT_APSRANCD
{
    [Column(Order = 0), Key, ForeignKey("AC_Analysis_category")]
    public int AC_Analysis_category{ get; set; }

    [Column(Order = 1), Key, ForeignKey("AC_ANALYSI_CODE")]
    public int AC_ANALYSI_CODE{ get; set; }
}

View more solutions

Comments

[clifford.duke]

So how do I go about designing the models for it, I use annotations and so far have only used [Key] to define the primary key, If I don't do that, what else can I do?

[clifford.duke]

Doesn't that just create a composite primary Key?

[Szymon]

Sorry but this is more c omplex than that.

[Silas Hansen]

This is not entirely correct. It's the Clustered Index Key that is used in all other non-clustered indexes. Often though, the clustered index is also the PK, but that does not HAVE to be the case always.

[Szymon]

@SilasHansen That's right. I updated my answer, thank you.