在SQL Server主键和唯一索引的区别 [英] Difference between Primary Key and Unique Index in SQL Server

问题描述

我公司目前在重写，我们最近收购了应用程序的过程。我们选择使用ASP.net mvc4建立这个系统，以及使用实体框架作为我们的ORM。我们收购公司的previous老板很坚定，我们用自己的旧的数据库和不会改变任何事情，这样客户端可以与旧系统并行使用我们的产品，同时我们正在开发不同的模块。

我发现旧表结构没有一个主键，相反，它采用了独特的指数作为自己的主键。现在，使用实体框架时，我曾尝试，以配合其在结构表，但一直未能如愿的EF生成主键，而不是唯一的指标。

在我接触的previous所有者，并解释说，他对我说，在每一个表中的唯一键是主键，它们是同义词对方。

我还是比较新的数据库系统，所以我不知道这是正确的。任何人都可以澄清这一点？

当转储到SQL他的桌子生成：

   -  ----------------------------
 - 索引结构表AT_APSRANCD
 -  ----------------------------
CREATE UNIQUE INDEX [ac_key] ON [DBO]。[AT_APSRANCD]
（[AC_Analysis_category] ​​ASC，[AC_ANALYSI_ code] ASC）
WITH（IGNORE_DUP_KEY = ON）
走
 

不过我的系统生成：

   -  ----------------------------
 - 为表AT_APSRANCD主键结构
 -  ----------------------------
ALTER TABLE [DBO]。[AT_APSRANCD] ADD PRIMARY KEY（[AC_Analysis_category]）
走
 

编辑：
跟进问题这是我将如何去设计模型呢？我只习惯使用[关键]注释将其定义为一个主键，没有它，EF不会产生该表。
所以像这样的：

  [表（AT_APSRANCD）]
公共类分析
{
    [键]
    公共字符串分析code {搞定;组; }
    公共字符串AnalysisCategory {搞定;组; }
    公共字符串SHORTNAME {搞定;组; }
    公共字符串LONGNAME {搞定;组; }
}
 

解决方案

SQL唯一约束

  

UNIQUE约束唯一标识数据库中的每个记录
  表。

  
  

UNIQUE和PRIMARY KEY约束都提供了
  保证唯一性的一列或列。

  
  

一个PRIMARY
  KEY约束自动上定义的唯一约束。

  
  

请注意，你可以有每个表许多独特的约束，但只有一个
  每桌PRIMARY KEY约束。

另外，从创建唯一索引

  

您不能如果这列一列创建唯一索引
  包含NULL在多个行。同样，您不能创建
  多个列唯一索引，如果列的组合
  包含NULL在多个行。这些都被视为重复
  为索引的目的值。

而来自创建主键

  

PRIMARY KEY约束中定义的所有列必须被定义为
  NOT NULL。如果没有指定为空性，所有列参赛
  在PRIMARY KEY约束他们已经为空性设置为NOT NULL。

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

解决方案

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.

这篇关于在SQL Server主键和唯一索引的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！