0

I have table holding items for a given list id in my Ms Sql server database (2008R2). I would like to add constraints so that no two list ids have same item list. Below illustrate my schema.

ListID  , ItemID
1      a
1      b
2      a
3      a
3      b

In above example ListID 3 should fail. I guess you can't put constarint/check within the database itself (Triggers,check) and the logic constaint can only be done from the frontend?

Thanks in advance for any help.

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
Tek
  • 3
  • 2
  • Logic constraint can be embedded in a stored procedure if data is only updated through the stored procedure. Data can be checked on the fly with a trigger but it will badly effect performance. How many rows does this table have? – Nick.Mc Mar 28 '18 at 10:41
  • It might help to explain the real problem behind this. For example if you had a data model that stored pre defined "templates", you could enforce that a specific template could only be used once – Nick.Mc Mar 28 '18 at 10:46

1 Answers1

0

Create a function that performs the logic you want and then create a check constraint or index that leverages that function.

Here is a functional example, the final insert fails. The function is evaluated row by row, so if you need to insert as a set and evaluate after, you'd need to do an "instead of" trigger:

CREATE TABLE dbo.Test(ListID INT, ItemID CHAR(1))
GO

CREATE FUNCTION dbo.TestConstraintPassed(@ListID INT, @ItemID CHAR(1))
RETURNS TINYINT
AS
BEGIN
    DECLARE @retVal TINYINT = 0;

    DECLARE @data TABLE (ListID INT, ItemID CHAR(1),[Match] INT)

    INSERT INTO @data(ListID,ItemID,[Match]) SELECT ListID,ItemID,-1 AS [Match] FROM dbo.Test

    UPDATE @data
    SET [Match]=1
    WHERE ItemID IN (SELECT ItemID FROM @data WHERE ListID=@ListID)

    DECLARE @MatchCount INT
    SELECT @MatchCount=SUM([Match]) FROM @data WHERE ListID=@ListID

    IF NOT EXISTS(
        SELECT *
        FROM (
            SELECT ListID,SUM([Match]) AS [MatchCount]
            FROM @data
            WHERE ListID<>@ListID
            GROUP BY ListID
        ) dat
        WHERE @MatchCount=[MatchCount]
    )
    BEGIN
        SET @retVal=1;
    END

    RETURN @retVal;
END
GO

ALTER TABLE dbo.Test
  ADD CONSTRAINT chkTest
  CHECK (dbo.TestConstraintPassed(ListID, ItemID) = 1); 
GO

INSERT INTO dbo.Test(ListID,ItemID) SELECT 1,'a'
INSERT INTO dbo.Test(ListID,ItemID) SELECT 1,'b'
INSERT INTO dbo.Test(ListID,ItemID) SELECT 2,'a'
INSERT INTO dbo.Test(ListID,ItemID) SELECT 2,'b'

Related

UnhandledExcepSean
  • 12,504
  • 2
  • 35
  • 51
  • I'm not trying to troll but... a function won't do anything if it's not triggered in some way. Why a function and not a stored procedure? – Nick.Mc Mar 28 '18 at 10:39
  • @Nick.McDermaid because the function will be triggered by the constraint. I don’t know if a stored procedure can be used in a constraint. – UnhandledExcepSean Mar 28 '18 at 10:41
  • Apologies I should have read the reference (considering I also commented on that one). It would be better if you added more detail on the solution rather than linking to it. The other key piece of the question is: what is the actual SQL to identify this. – Nick.Mc Mar 28 '18 at 10:45
  • At the moment users can update the table via MS Access linked table. As trigger kicks in after each insert command and before they complete entering the items for a given list ID, it can create false failure and I guess it will effect performance as well , but not too much worry about that, as it is not mission critical db. I think I need to move it to stored procedure and build the interface within MS Access. – Tek Mar 28 '18 at 11:17