Primary and Foreign Key at the same time

Question

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.

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