0

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!

  • 4
    Can you explain why a plain unique index on locationname AND companyid doesn't satisfy your requirement? – Scott Hodgin Jun 20 '18 at 18:05
1

Seems like this should do it:

CREATE UNIQUE INDEX [UNQ_locationName_companyId]
ON dbo.[locations]([locationName],[companyId]);
  • 2
    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). – ypercubeᵀᴹ Jun 20 '18 at 18:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.