SQL unique column based on other columns

Question

I have a SQL database table that I am trying to fix without having to resort to using the front end code to determine if the name of the institute is unique. I am setting up a Linq to SQL code base for all of the inserts.

here is the new table that I am trying to setup:

CREATE TABLE [dbo].[institution]

(

    [institutionID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,

    [typeID] [tinyint] NOT NULL REFERENCES [dbo].[institutiontype]([institutiontypeID]),

    [name] [varchar](255) NOT NULL UNIQUE,

    [cityID] [int] NULL REFERENCES [dbo].[city]([cityID]),

    [stateID] [int] NULL REFERENCES [dbo].[stateprovince]([stateID]),

    [countryID] [int] NULL REFERENCES [dbo].[country]([countryID]),

    [createby] [int] NOT NULL REFERENCES [dbo].[ipamuser]([ipamuserID]),

    [createdatetime] [datetime] NOT NULL DEFAULT (GETDATE()),

    [modifyby] [int] NULL REFERENCES [dbo].[ipamuser]([ipamuserID]),

    [modifydatetime] [datetime] NULL,

    [dataversion] [int] NOT NULL DEFAULT (0)

)

The issue is, the institutionname needs to be unique ONLY when the cityID, stateID and the countryID are the same. Setting up the table with the institutename as unique will not satisfy the needs since there are times when the same name can exist in different, cities, states or countries.

How would I resolve this

Answer 1

You need to write a complex constraint in your table. Define a user-defined-function which returns true (1 in BIT) if your required condition is satisfied and false otherwise. Put this constraint in the table schema with a CHECK constraint.

CREATE FUNCTION dbo.fnIsNameUnique (
  @name [varchar](255),
  @cityID int,
  @stateID int,
  @countryID int,
)
RETURNS tinyint
AS
BEGIN
  DECLARE @Result tinyint
  IF EXISTS(SELECT * FROM institution WHERE name = @name AND cityID = @cityID AND stateID = @stateID AND countryID = @countryID)
    SET @Result= 0
  ELSE 
    SET @Result= 1
  RETURN @Result
END

CREATE TABLE [dbo].[institution]
(
    [institutionID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [typeID] [tinyint] NOT NULL REFERENCES [dbo].[institutiontype]([institutiontypeID]),
    [name] [varchar](255) NOT NULL UNIQUE,
    [cityID] [int] NULL REFERENCES [dbo].[city]([cityID]),
    [stateID] [int] NULL REFERENCES [dbo].[stateprovince]([stateID]),
    [countryID] [int] NULL REFERENCES [dbo].[country]([countryID]),
    [createby] [int] NOT NULL REFERENCES [dbo].[ipamuser]([ipamuserID]),
    [createdatetime] [datetime] NOT NULL DEFAULT (GETDATE()),
    [modifyby] [int] NULL REFERENCES [dbo].[ipamuser]([ipamuserID]),
    [modifydatetime] [datetime] NULL,
    [dataversion] [int] NOT NULL DEFAULT (0),
    CONSTRAINT ckValidName CHECK (
    dbo.fnIsNameUnique(name, cityID, stateID, countryID) = 1)
  )
)

SQL unique column based on other columns

Sign up