Create a unique constraint on a column based on distinct value from another column in the same table

Question

I want to make a column unique, but it has to be unique based off a distinct value from another column in the table.

Consider the following table:

CREATE TABLE locations(
  locationId INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
  locationName VARCHAR(50) NOT NULL,
  companyId INT NOT NULL FOREIGN KEY REFERENCES companies (companyId)
);

My goal is to have location name be unique for each value in the companyId column.

For example, given the following table:

    [locationId]   [locationName]   [companyId]
=====================================================================
    1              'Example A.'     1          
    2              'Example B.'     1          
    3              'Example A.'     2          
    4              'Example C.'     2    

If I attempted to execute the following query:

INSERT INTO locations (locationName, companyId)
VALUES ('Example B.', 2)

This would not give me a unique constraint error because no row in the locations table has a locationName of 'Example B.'and a companyId of 2

However, the executing a query like this should produce a unique constraint error:

INSERT INTO locations (locationName, companyId)
VALUES ('Example C.', 2)

because a row with locationName 'Example C.' and companyId 2 already exists.

I've tried the query

CREATE UNIQUE INDEX [UNQ_locationName_companyId]
ON dbo.[locations]([locationName])
WHERE ([companyId] IS DISTINCT);

however IS DISTINCT is not valid SQL syntax, but I can't think of the proper way to accomplish this.

Any help would be very appreciated!

Answer 1

Seems like this should do it:

CREATE UNIQUE INDEX [UNQ_locationName_companyId]
ON dbo.[locations]([locationName],[companyId]);