Primary and Foreign Key at the same time

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?