Non-Repetition constraint in the non-key attributes for each key attributes

1

I would like to establish a table in which the the non-key attributes for each value in key attributes are not repeated. for example , the following table show the locations of several companies in several cities, and the needed constraint states that each company should have at most one branch for each city.

|Company_name|city|
|Amazon|London|
|Amazon|Berlin|
|Microsoft|London|
|Microsoft|Berlin|

how to establish a such constraint in which we make sure that Amazon or Microsoft have two branches in Berlin?

Using UNIQUE Constraint alone is not enough ,as it makes the city name will not be repeated two times to present the locations of several company, so something more is needed, any suggestion?

2

You can create a unique constraint on multiple columns

ALTER TABLE dbo.CompanyBranches
  ADD CONSTRAINT Unique_Company_Branch UNIQUE (Company, City)
2 Likes