Our goal: to check in the DB level that for each ID (or other column) will be other column with a specific value not more than one time.
Wow - long statement… Let's do it with an example :)
CREATE TABLE [dbo].[SpecialCheckConst]
( SomeID int,
SomeStatusID int, -- we will check vaalues of this column
SomeOtherColumn nvarchar(50) -- or any other columns
)
We want to check that for each SomeID there is no more than one record of SomeStatusID 1.
INSERT INTO dbo.SpecialCheckConst
( SomeID, SomeStatusID, SomeOtherColumn)
VALUES
( 2, 3, 'aaa'), (5, 1, 'sss')
When we will try to insert new record with SomeStatusID 1 for SomeID 2 - we should success.
When we will try to insert new record with SomeStatusID 1 for SomeID 5 - we should fail (because there is a record for this SomeID with status 1).
When we will try to insert new record with new SomeID - of course that SomeStatusID 1 is OK.
Solution:
CHECK CONSTRAINT that calls a function that count the number of SomeStatusID 1 for the SomeID:
CREATE FUNCTION [dbo].[fn_SpecialCheckConst_SomeStatusIDOne]
(
@SomeID int
)
RETURNS INT
AS
BEGIN
DECLARE @SomeStatusIDOne int
SELECT @SomeStatusIDOne = COUNT(*)
FROM dbo.SpecialCheckConst
WHERE SomeID = @SomeID
AND SomeStatusID = 1
RETURN @SomeStatusIDOne
END
GO
---------------------------------------------------------------------------------------------
ALTER TABLE [dbo].[SpecialCheckConst] WITH CHECK ADD CONSTRAINT [CHK_dbo_SpecialCheckConst_SomeStatusIDOne]
CHECK (NOT ([dbo].[fn_SpecialCheckConst_SomeStatusIDOne](@SomeID) > 1 AND SomeStatusID = 1))
GO
ALTER TABLE [dbo].[SpecialCheckConst] CHECK CONSTRAINT [CHK_dbo_SpecialCheckConst_SomeStatusIDOne]
GO
---------------------------------------------------------------------------------------------
And ….. It’s work!
You can drop the example temp table:
--DROP TABLE dbo.SpecialCheckConst
--go
