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!