Sql-server – Create a unique constraint on a column based on distinct value from another column in the same table

constraintsql serversql server 2014unique-constraint

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(
  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!

Best Answer

Seems like this should do it:

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