CODEWITHSUNDEEP
sqlforeign-keys
David Thielen
David Thielen

Reputation: 32874

Can a single foreign key point to multiple primary keys?

I have a case where the DB is set up with (simplifying) a table User and a table SuperUser. Both have a column Id. Every SuperUser row has a row in the User table with the same Id value (not my design!!!). The SUperUser table does not pull common info from the User table, it duplicates it.

Can/should any foreign key that points to User.Id also have a FK:PK relationship with SuperUser.Id? My take on this is it is at a minimum a very bad idea and that many (most?) DBs can't enforce this relationship/

Am I off base here?

Upvotes: 0

Views: 1134

Answers (2)

John Kane
John Kane

Reputation: 4443

Since every user has a record in the user table and I am assuming that not every user is a super user, I would just reference them by user and check if they are a super user when needed.

It would good to remove the duplicated fields if at all possible. This can easily become a nightmare to maintain as things grow.

Upvotes: 1

Joe W
Joe W

Reputation: 1872

Based on my understanding of your question you would run into two major problems with a foreign key set up to both the User and SuperUser tables.

  1. You would not be able to enter or update any table with a foreign key set up like that unless the user was in both tables since the foreign key expects that userid to be present in both of them.

  2. If you have the foreign key set up with a cascade delete and remove a record from the SuperUser then all tables with that set up would remove records associated with that userid potentially leaving you with just the record in the User table and all other information lost.

What would be a better option would be setting the userid column in the SuperUser table to be a foreign key of the userid column in the User table that way you will not run into issues where a user is not in both tables or removing a user from the SuperUser table.

Upvotes: 2

Related Questions