Require uniqueness with group by

Question

I'm not sure how to couch this question. I have a table that has multiple columns including an id column, a foreign key column 'JobId', and a 'GxpId' column.

I have a requirement that a 'JobId' can have at most one 'GxpId' but the table can have multiple rows with the same 'JobId' and 'GxpId'.

There is a separate process that is importing data into the table and the application using the table has failed because the import process has created two rows with the same 'JobId' but different 'GxpId'.

I would like to add a constraint to the table to ensure that any row for a specific 'JobId' must have the same 'GxpId'. Hope that makes sense.

score 1 · Accepted Answer · answered Apr 24 '14 at 01:56

In SQL Server, you need a two step process to do this. The check constraint can't have complex logic in it, but it can reference a function which can.

This constraint is a pretty big red flag for how the schema is designed. Looking at your other questions, it looks like you're aware of this and have limited options to work with.

CREATE TABLE TestTable (TestTableId INT IDENTITY(1,1), Jobid INT NOT NULL, GxpId INT NOT NULL)

GO
CREATE FUNCTION dbo.TestTable_GxpCountForJobId_fn(@JobId AS INT)
RETURNS INT
BEGIN
    DECLARE @GxpCount INT
    SELECT @GxpCount = COUNT(*)
    FROM (
        SELECT DISTINCT
            GxpId
        FROM TestTable
        WHERE JobId = @JobId
    ) AS DistinctGpx

    RETURN @GxpCount
END
GO

ALTER TABLE TestTable
ADD CONSTRAINT TestTable_SingleRelation_ck
CHECK (dbo.TestTable_GxpCountForJobId_fn(JobId) <= 1)
GO

INSERT INTO TestTable VALUES (1,1)
INSERT INTO TestTable VALUES (2,3)
INSERT INTO TestTable VALUES (1,1)

-- This will fail
INSERT INTO TestTable VALUES (1,2)

Thanks, that looks useful. Re schema design, each job must have at least one row with a GxpId populated, but can have more rows with other optional columns populated so long as the GxpId is the same. What would be a better design to capture this? — David Clarke, Apr 24 '14 at 02:20
The GpxId FK should probably be on the Job table, and then have table that has the relation information with a FK to Job. Having multiple rows makes me think that the relation is more complex than should be described in a single entity. — AlwaysLoadingData, Apr 24 '14 at 02:47

Require uniqueness with group by

1 Answers1