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)
)
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)
)
)