Is it possible to limit db for this rule?

Question

I want to make sure that my data has a restriction on the next check (restriction?) In place

• There can only be one BorderColour in this table for one hub / category. (e.g. #FFAABB)
• But it can have several zeros. (all other lines are zeros for this field)

Table layout

ArticleId INT PRIMARY KEY NOT NULL IDENTITY
HubId TINYINT NOT NULL
CategoryId INT NOT NULL
Title NVARCHAR(100) NOT NULL
Content NVARCHAR(MAX) NOT NULL
BorderColour VARCHAR(7) -- Can be nullable.

I understand that I will need to make a control restriction? But I'm not sure how, etc.

sample data.

1, 1, 1, 'test', 'blah...', '#FFAACC'
1, 1, 1, 'test2', 'sfsd', NULL
1, 1, 2, 'Test3', 'sdfsd dsf s', NULL
1, 1, 2, 'Test4', 'sfsdsss', '#AABBCC'

now .. if i add the following line i should get some sql error ....

INSERT INTO tblArticle VALUES (1, 2, 'aaa', 'bbb', '#ABABAB')

any ideas?

Answer 1

, SQL2008. SQL , .

, , , BorderColour null.

CREATE TABLE [dbo].[UniqueExceptNulls](
    [HubId] [tinyint] NOT NULL,
    [CategoryId] [int] NOT NULL,
    [BorderColour] [varchar](7) NULL,
)

GO

CREATE UNIQUE NONCLUSTERED  INDEX UI_UniqueExceptNulls
ON [UniqueExceptNulls] (HubID,CategoryID)
WHERE BorderColour IS NOT NULL

, , . , , .

, , UDF/Check Constraint.

Answer 2

CHECK , UDF:

CREATE FUNCTION dbo.CheckSingleBorderColorPerHubCategory
(
    @HubID tinyint,
    @CategoryID int
)
RETURNS BIT
AS BEGIN
    RETURN CASE
        WHEN EXISTS
        (
            SELECT HubID, CategoryID, COUNT(*) AS BorderColorCount
            FROM Articles
            WHERE HubID = @HubID
                AND CategoryID = @CategoryID
                AND BorderColor IS NOT NULL
            GROUP BY HubID, CategoryID
            HAVING COUNT(*) > 1
        ) THEN 1
        ELSE 0
    END
END

UDF:

ALTER TABLE Articles
ADD CONSTRAINT CK_Articles_SingleBorderColorPerHubCategory
CHECK (dbo.CheckSingleBorderColorPerHubCategory(HubID, CategoryID) = 1)

Answer 3

- ( - , , , UNION UNION ..):

IF EXISTS (
    SELECT COUNT(BorderColour)
    FROM (
             SELECT INSERTED.HubId, INSERTED.CategoryId, INSERTED.BorderColour
             UNION ALL
             SELECT HubId, CategoryId, BorderColour
             FROM tblArticle
             WHERE EXISTS (
                 SELECT *
                 FROM INSERTED
                 WHERE tblArticle.HubId = INSERTED.HubId
                       AND tblArticle.CategoryId = INSERTED.CategoryId
             )
    ) AS X
    GROUP BY HubId, CategoryId
    HAVING COUNT(BorderColour) > 1
)
RAISEERROR

Answer 4

, , .

, , , , UDF. .

CREATE TABLE [dbo].[UQTest](
    [Id] INT IDENTITY(1,1) NOT NULL,
    [HubId] TINYINT NOT NULL,
    [CategoryId] INT NOT NULL,
    [BorderColour] varchar(7) NULL,
    [BorderColourUNQ]  AS (CASE WHEN [BorderColour] IS NULL 
                               THEN cast([ID] as varchar(50))
                               ELSE cast([HuBID] as varchar(3)) + '_' + 
                                    cast([CategoryID] as varchar(20)) END
                           ),
 CONSTRAINT [UQTest_Unique] 
 UNIQUE  ([BorderColourUNQ])
) 

One possible undesirable aspect of the aforementioned implementation is that it allows the category / hub to have both NULL and a specific color. If this is a problem, let me know and I will select my answer to answer this question.

PS: Sorry for my previous (incorrect) answer. I have not carefully studied this issue.