How to unique a field for each group?

Question

I have following table:

CardsTable:
-------
CardID int
PersonID int
Number int
-----------------------
CardID    PersonID    Number
  1          10         22
  2          10         22
  3          11         24
  4          11         24
  5          10         22

Number field should be unique for all personID that have the same IDs.

Following example is invalid:

 CardID   PersonID    CardID
    6         11         22   
    7         12         22
    8         12         24

How to use conditional unique constraint to implement it?

http://stackoverflow.com/questions/866061/conditional-unique-constraint — juergen d, Mar 11 '17 at 15:59

score 1 · Answer 1 · answered Mar 13 '17 at 15:09

You can use a check constrain with a user defined function:

First, create the function:

CREATE FUNCTION dbo.CheckCards
(
    @PersonId int,
    @Number int
)
RETURNS bit
AS
BEGIN

IF EXISTS
(
    SELECT 1 
    FROM CardsTable 
    WHERE PersonId = @PersonId 
    AND Number <> @Number
)
BEGIN
    RETURN 1
END
RETURN 0

END

GO

Now, create and populate sample table:

CREATE TABLE CardsTable
(
    CardID int,
    PersonID int,
    Number int,
    CONSTRAINT ck CHECK (dbo.CheckCards(PersonId, Number) = 0)
)

INSERT INTO CardsTable VALUES
(1, 10, 22),
(2, 10, 22),
(3, 11, 24),
(4, 11, 24),
(5, 10, 22)

Then test the check constraint:

BEGIN TRY
   INSERT INTO CardsTable VALUES (3, 10, 24)
   SELECT 'Success' As [Insert]
END TRY
BEGIN CATCH
    SELECT 'Failed' As [Insert]
END CATCH

SELECT *
FROM CardsTable

Results:

Insert
Failed

CardID  PersonID    Number
1       10          22
2       10          22
3       11          24
4       11          24
5       10          22

Amir H. Bagheri · Answer 2 · 2017-03-11T14:37:54.257

You should be implementing a trigger over the table for "insert" and "update" such as following:

 Create trigger trigger_cardstable_insert on cardstable
 after insert
 as
 Begin
 set nocount on;
 if (select count(c.personid) from cardstable c inner join inserted i on i.personid group by i.personid, c.number) > 1
  Begin

      Delete cardstable
      From cardstable c inner join inserted i on i.cardid = c.cardid
 End
 End

For update, it would be a little different and you need to add one more condition:

 Create trigger trigger_cardstable_update on cardstable
 after update
 as
 Begin
 set nocount on;
 if update(number) and personid(select count(c.personid) from cardstable c inner join inserted i on i.personid group by i.personid, c.number) > 1
  Begin

      Delete cardstable
      From cardstable c inner join inserted i on i.cardid = c.cardid
 End
 End

I am afraid you cannot. – Amir H. Bagheri Mar 11 '17 at 14:38 — Amir H. Bagheri, Mar 11 '17 at 14:38

How to unique a field for each group?

2 Answers2