How to CONSTRAINT such a condition? SQL Server

Question

I need to create a table TEAMS and create a constraint that the coach (TeamCoachName) can just be a coach for just 1 team (TeamName).

/* Table number 2: Teams */
CREATE TABLE TEAMS
(
    TeamName VARCHAR(255) UNIQUE,
    YearOfFounding INT,
    TeamOwnerName VARCHAR(255),
    StadiumName VARCHAR(255),
    GeographicArea VARCHAR(255) 
         CHECK (GeographicArea  IN ('North','Central','South')),
    TeamCoachName VARCHAR(255),

    CONSTRAINT Names UNIQUE (TeamName, TeamCoachName),
    CONSTRAINT OneTeamCoach
)

Possible duplicate of [Sub queries in check constraint](https://stackoverflow.com/questions/13000698/sub-queries-in-check-constraint) — McNets, Nov 30 '18 at 14:51

score 3 · Accepted Answer · answered Nov 30 '18 at 14:51

3

This is your Team table, and if a coach can only be associated with 1 team row, then it is just a UNIQUE CONSTRAINT on TeamCoachName.

answered Nov 30 '18 at 14:51

Jamiec

133,658
13
134
193

I think you understood my question very well, that is my first time in SQL and I still don't know how to write it In the right way, can you please help me write the full line of code so I can execute it in server management ? – Anan Nov 30 '18 at 15:41

score 0 · Answer 2 · answered Nov 30 '18 at 14:49

You can do this with a UDF that takes a TeamName and a CoachName and returns "true" if the CoachName is associated with any other team.

Then check the value of that function in the CHECK constraint.

(Although actually Jamie raises a good point. If TeamName is also UNIQUE, then all you need is a UNIQUE constraint on CoachName. The only way you would need my approach is if the same TeamName could appear in multiple rows.)

How to CONSTRAINT such a condition? SQL Server

2 Answers2