主键和外键同时。

Would it be possible in SQL Server 2008 to have a table created with 2 columns that are at the same time primary and foreign keys? If yes, how would such a code look like? I've searched and came up with nothing.

在SQL Server 2008中，是否可能创建一个包含两个列的表，同时包含主键和外键?如果是，这样的代码会是什么样子?我找过了，却一无所获。

4 个解决方案

#1

Sure, no problem:

当然,没有问题:

CREATE TABLE dbo.[User]
(
  Id int NOT NULL IDENTITY PRIMARY KEY,
  Name nvarchar(1024) NOT NULL
);

CREATE TABLE [Group] 
(
  Id int NOT NULL IDENTITY PRIMARY KEY,
  Name nvarchar(1024) NOT NULL
);

CREATE TABLE [UserToGroup]
(
  UserId int NOT NULL,
  GroupId int NOT NULL,
  PRIMARY KEY CLUSTERED ( UserId, GroupId ),
  FOREIGN KEY ( UserId ) REFERENCES [User] ( Id ) ON UPDATE  NO ACTION  ON DELETE  CASCADE,
  FOREIGN KEY ( GroupId ) REFERENCES [Group] ( Id ) ON UPDATE  NO ACTION  ON DELETE  CASCADE
);

This is quite commonly used to model many-to-many relations.

这通常用于建模多对多关系。

#2

These are totally different constructs.

这些是完全不同的结构。

A Primary Key is used to enforce uniqueness within a table, and be a unique identifier for a certain record.

主键用于强制表中的惟一性，并且是特定记录的惟一标识符。

A Foreign Key is used for referential integrity, to make sure that a value exists in another table.

外键用于引用完整性，以确保值存在于另一个表中。

The Foreign key needs to reference the primary key in another table.

外键需要在另一个表中引用主键。

If you want to have a foreign key that is also unique, you could make a FK constraint and add a unique index/constraint to that same field.

如果您想要一个外键也是唯一的，您可以创建一个FK约束，并向该字段添加一个惟一的索引/约束。

For reference purposes, SQL Server allows a FK to refer to a UNIQUE CONSTRAINT as well as to a PRIMARY KEY field.

为了便于参考，SQL Server允许FK引用唯一的约束以及主键字段。

#3

It is probably not a good idea since often you want to allow duplicate foreign keys in the table. Even if you don't now, in the future, you might, so best not to do this. See Is it fine to have foreign key as primary key?

这可能不是一个好主意，因为您通常希望在表中允许重复的外键。即使你现在没有，在将来，你可能，所以最好不要这样做。把外键作为主键可以吗?

#4

Just a quick note - from Microsoft pages (http://msdn.microsoft.com/en-us/library/ms189049.aspx)...

请注意——从Microsoft页面(http://msdn.microsoft.com/en-us/library/ms189049.aspx)……

"A foreign key constraint does not have to be linked only to a primary key constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table."

“外键约束不需要只链接到另一个表中的主键约束;它也可以定义为引用另一个表中唯一约束的列。

Not used often, but useful in some circumstances.

不经常使用，但在某些情况下有用。

#1