unique constraint on window function output

Question

I want to create lists of items and prevent the entry of 2 identical lists, using a unique constraint on a computed column.

CREATE TABLE test_cc
(
    list_id int,
    list_item int,
    list_items AS STRING_AGG(CONVERT(varchar(10), list_item),',') OVER (PARTITION BY list_id) WITHIN GROUP (ORDER BY list_item),
    UNIQUE(bs)
);

INSERT INTO test_cc VALUES (1, 1),(1,2),(2,1),(2,2);

/*should not be possible.*/

Executing this on SQL Server 2019 returns Error Msg 4113 Level 16 during table creation.

Is declaring a unique constraint on an expression a good practice ?

My data volume for this table is not huge.

`STRING_AGG` is an aggregate function; you can't use them in computed columns. — Thom A, Jul 01 '22 at 09:06
You are likely going to need to use a well written set-based trigger for this; which may well not be particularly performant as your data grows. — Thom A, Jul 01 '22 at 09:08

score 1 · Accepted Answer · answered Jul 01 '22 at 09:41

Making sure that lists are unique are difficult. As I mentioned in the comments you can't use aggregate function in a computed column; a computed column is a value calculated based on the row, not the table.

You also can't use an Indexed View with a UNIQUE INDEX on a STRING_AGG'd column, as STRING_AGG isn't allowed to be used in an indexed view.

One method, therefore, is to use a TRIGGER, however, this won't be performant; in fact as your table grows this is going to get increasingly slower. For a small dataset it should be fine.

CREATE TRIGGER dbo.UnqTrg_list_items_test_cc ON dbo.test_cc
AFTER INSERT,UPDATE,DELETE AS
BEGIN

    IF EXISTS (SELECT 1
               FROM (SELECT STRING_AGG(cc.list_item,',') WITHIN GROUP (ORDER BY cc.list_item) AS list_items
                     FROM dbo.test_cc cc
                     GROUP BY cc.list_id) SA
               GROUP BY list_items
               HAVING COUNT(list_items) > 1)
        THROW 96432, N'Violation of Unique Trigger logic ''UnqTrg_list_items_test_cc''. Cannot insert duplicate list in object ''dbo.test_cc''. The statement has been aborted.',10;
               
END;

db<>fiddle demonstrating INSERT,DELETE and UPDATE failing.

I'm thinking that the trigger should update a separate table which would mimic an indexed view. Then you can use that for the lookup — Charlieface, Jul 04 '22 at 10:30
That could work, @Charlieface . Any solution isn't exactly intuitive. — Thom A, Jul 04 '22 at 10:33

unique constraint on window function output

1 Answers1