复合主键还是不是？ [英] Composite primary key or not?

问题描述

这是什么让我感到困惑我经常在数据库表中有复合主键。这种方法的不利方面是，当我删除或编辑条目时，我有很多额外的工作。但是，我觉得这种做法是在数据库设计的精神上。

另一方面，我的朋友是从不使用复合键，而是介绍表中另一个id列，所有其他键都只是FK。他们在编辑删除和编辑过程时工作少得多。但是，我不知道他们如何保留数据条目的唯一性。

例如：

Way 1

 创建表ProxUsingDept（
 fkProx int引用Prox（ProxID）NOT NULL，
 fkDept int references Department（DeptID）NOT NULL，
值int，
 PRIMARY KEY（fkProx，fkDept）
）
  

方式2

 创建表ProxUsingDept（
 ID int NOT NULL IDENTITY PRIMARY KEY 
 fkProx int引用Prox ）NOT NULL，
 fkDept int references Department（DeptID）NOT NULL，
值int 
）
  

哪种方式更好？使用第二种方法的不利方面是什么？任何建议？

解决方案

我个人喜欢您的第二种方法（并将使用它几乎100％时间） - 介绍代理 ID 字段。

为什么？

• 使引用表的任何表格变得更加容易 - 只需单个ID列（而不是2）就可以使JOIN条件更简单 ，3或甚至更多列，您需要加入，所有的时间）




• 使生活更容易，因为任何引用表的表只需要携带一个 ID 作为外键字段 - 不是复合键中的几个列




• 使生活数据库可以处理创建唯一的 ID 列（使用 INT IDENTITY ）

但是，我不知道
如何保留数据条目的唯一性。

非常简单：将一个独特的INDEX放在您以主键方式使用的复合列上！

  CREATE UNIQUE INDEX UIX_WhateverNameYouWant 
 ON dbo.ProxUsingDept（fkProx，fkDept）
  

现在，您的表格保证表中永远不会有重复的（fkProx，fkDept）副本！ p>

Here's what's confusing me. I often have composite primary keys in database tables. The bad side of that approach is that I have pretty extra work when I delete or edit entries. However, I feel that this approach is in the spirit of database design.

On the other side, there are friends of mine, who never use composite keys, but rather introduce another 'id' column in a table, and all other keys are just FKs. They have much less work while coding delete and edit procedures. However, I do not know how they preserve uniqueness of data entries.

For example:
Way 1

create table ProxUsingDept (
    fkProx int references Prox(ProxID) NOT NULL,    
    fkDept int references Department(DeptID) NOT NULL,    
    Value int,    
    PRIMARY KEY(fkProx,fkDept)
)

Way 2

create table ProxUsingDept (
        ID int NOT NULL IDENTITY PRIMARY KEY
        fkProx int references Prox(ProxID) NOT NULL,    
        fkDept int references Department(DeptID) NOT NULL,    
        Value int
)

Which way is better? What are the bad sides of using the 2nd approach? Any suggestions?

解决方案

I personally prefer your 2nd approach (and would use it almost 100% of the time) - introduce a surrogate ID field.

Why?

• makes life a lot easier for any tables referencing your table - the JOIN conditions are much simpler with just a single ID column (rather than 2, 3, or even more columns that you need to join on, all the time)

• makes life a lot easier since any table referencing your table only needs to carry a single ID as foreign key field - not several columns from your compound key

• makes life a lot easier since the database can handle the creation of unique ID column (using INT IDENTITY)

However, I do not know how they preserve uniqueness of data entries.

Very simple: put a UNIQUE INDEX on the compound columns that you would otherwise use as your primary key!

CREATE UNIQUE INDEX UIX_WhateverNameYouWant 
   ON dbo.ProxUsingDept(fkProx, fkDept)

Now, your table guarantees there will never be a duplicate pair of (fkProx, fkDept) in your table - problem solved!

这篇关于复合主键还是不是？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！