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!
locationname
ANDcompanyid
doesn't satisfy your requirement?