CODEWITHSUNDEEP
sql-serverforeign-keysconstraints
jbd
jbd

Reputation: 415

how do I add unique constraint on an existing FK's parent?

consider these tables:

leagues [ id,name ]

teams [ id,name,leaguesid]
FK: (leaguesid) -> (leagues.id)
AK: (leaguesId) UNIQUE /teams can be in one league/

players [id,name]

players_teams [playersid,teamsid]
FK: (playersid) -> players.id, teamsid -> teams.id
AK: (playersid,teamsid) UNIQUE /player can not be on same team twice/

this works pretty well, but i need a constraint that will prevent the same player from being in the same league more than once. can this be done without creating a players_league table or adding leagueid to this table? those options seem to add denormalization/overhead since i can already determine any leagues a player is in based on their team.

thanks

Upvotes: 0

Views: 81

Answers (1)

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

One nice(?) way to resolve that kind of problem is to add a check constraint, probably on players_teams table, based on an user defined function, returning a bool value, for example.

msdn

http://msdn.microsoft.com/en-US/library/ms188258%28v=sql.105%29.aspx

Or also

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/02/07/using-a-udf-in-a-check-constraint-to-check-validity-of-history-windows-start-end-date-windows.aspx

or here

http://connectsql.blogspot.com/2011/01/sql-server-function-based-check.html

or... google for check constraint udf sql server

Upvotes: 1

Related Questions