Is it possible to a db constraint in for this rule?

Question

Hi folks,

I wish to make sure that my data has a constraint the following check (constraint?) in place

• This table can only have one BorderColour per hub/category. (eg. #FFAABB)
• But it can have multiple nulls. (all the other rows are nulls, for this field)

Table Schema

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'm gussing I would have to make a check constraint? 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

CHECK constraints are ordinarily applied to a single row, however, you can cheat using a 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

Then create the constraint and reference the UDF:

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

Answer 2

You can also do a trigger with something like this (this is actually overkill - you can make it cleaner by assuming the database is already in a valid state - i.e. UNION instead of UNION all etc):

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 3

If you have a unique column in your table, then you can accomplish this by creating a unique constraint on a computer column.

The following sample created a table that behaved as you described in your requirements and should perform better than a UDF based check constraint. You might also be able to improve the performance further by making the computed column persisted.

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

The one possibly undesirable facet of the above implementation is that it allows a category/hub to have both a Null AND a color defined. If this is a problem, let me know and I'll tweak my answer to address that.

PS: Sorry about my previous (incorrect) answer. I didn't read the question closely enough.

Answer 4

Another option that is available is available if you are running SQL2008. This version of SQL has a feature called filtered indexes.

Using this feature you can create a unique index that includes all rows except those where BorderColour is 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

This approach is cleaner than the approach in my other answer because it doesn't require creating extra computed columns. It also doesn't require you to have a unique column in the table, although you should have that anyway.

Finally, it will also be much faster than the UDF/Check Constraint solutions.