CODEWITHSUNDEEP
sqlsql-serverconstraints
Anan
Anan

Reputation: 41

How to CONSTRAINT such a condition? SQL Server

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
)

Upvotes: 0

Views: 43

Answers (2)

Jamiec
Jamiec

Reputation: 136074

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.

Upvotes: 3

Tab Alleman
Tab Alleman

Reputation: 31775

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.)

Upvotes: 0

Related Questions