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.

Do you mean what would the SQL to create the table look like? — Joachim Isaksson, Jan 12 '12 at 21:53
This is a very common case on ORM frameworks that suppport inheritance mapping by doing table-per-class mapping. For example if a class B inherits from a class A, and those are mapped to table_a and table_b, it is common that the instances of B have the same id on table_a and table_b, and table_b defines a FK on it's id column to the table_a id column. Just try to define the FK and the PK using the SQLServer Management Studio. — Pablo Pazos, Jun 29 '15 at 18:26

score 71 · Accepted Answer · answered Jan 12 '12 at 22:08

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.

Just as an FYI, in the `information_schema.key_column_usage` for the `UserToGroup` table, both `UserID` and `GroupID `will return two rows each... one for PK, one for FK... [in reference to...](http://stackoverflow.com/a/15622288/623952) — gloomy.penguin, Mar 25 '13 at 18:53

score 12 · Answer 2 · answered Jan 12 '12 at 21:57

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.

score 5 · Answer 3 · edited May 23 '17 at 12:34

5

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?

edited May 23 '17 at 12:34

Community

1
1

answered Nov 14 '14 at 16:45

thebiggestlebowski

2,610
1
33
30

2

Why not? Like the link you've put, it says there are cases like 1-to-1 relationship where in it may be necessary – Abel Callejo Dec 16 '15 at 01:57
I didn't say it's ALWAYS a bad idea ;) You are right, there may be exceptions where it will always be a 1:1. My comment is here to make people think about the right choice for them. – thebiggestlebowski Aug 17 '16 at 23:22
I would agree with this, for consistency sake as well. – blindguy Dec 06 '22 at 20:27

score 1 · Answer 4 · answered Jun 02 '14 at 09:11

Just a quick note - from Microsoft pages (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.

Primary and Foreign Key at the same time

4 Answers4

Linked