Primary and Foreign Key at the same time

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.

---

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.

---

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.

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

---

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?

链接地址: http://www.djcxy.com/p/76304.html

上一篇: 索引中包含哪些列？

下一篇: 主键和外键同时进行