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!

• Scott Hodgin - Retired: Can you explain why a plain unique index on locationname AND companyid doesn't satisfy your requirement?

Answer 1

Seems like this should do it:

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

• ypercubeᵀᴹ: You can add that the wanted behaviour can be accomplished with either a unique index or a unique constraint (which will create an index under the hood).