# Check constraint on grouped conditions

Take the following table as an example:

``````GroupID    AlphaID     BetaID

1          0           1
1          1           1
1          2           2
2          0           1
2          1           2
2          2           2
2          3           2
3          0           1
3          1           1
``````

I would like to write a check constraint that checks the following:

1. AlphaID - Check Constraint

• The first instance of `AlphaID` in a `GroupID` must start at 0
• E.g inserting GroupID = 4 with an AlphaID = 0 will succeed
• E.g inserting GroupID = 4 with an AlphaID = 1 will fail
• `AlphaID` must be unique within each `GroupID` and must be +1 greater than current max `AlphaID`
• E.g inserting GroupID = 3 with an AlphaID = 1 will fail (duplicate)
• E.g inserting GroupID = 3 with an AlphaID = 2 will succeed (current max + 1)
• E.g inserting GroupID = 3 with an AlphaID = 4 will fail (out of order)

2. BetaID - Check Constraint

• The first instance of `BetaID` in a `GroupID` must start at 1
• E.g inserting GroupID = 4 with an BetaID = 0 will fail
• E.g inserting GroupID = 4 with an BetaID = 1 will succeed
• `BetaID` can have duplicates within `GroupID` or may be +1 greater than current max `BetaID`
• E.g inserting GroupID = 3 with an BetaID = 1 will succeed
• E.g inserting GroupID = 3 with an BetaID = 2 will succeed
• E.g inserting GroupID = 3 with an BetaID = 4 will fail (out of order)

A solution that uses a trigger may also be suitable.

• The first instance of The table is a heap. So "first/next/last" not defined in a table. It may be defined only in a query with proper ORDER BY clause. – Akina May 12 '20 at 5:55
• The task may be solved by a trigger with the logic: 1) Do not allow insertion if a record with inserted GroupID value not exists in a table and inserted AlphaID is not 0; 2) Do not allow insertion if a record with inserted GroupID value not exists in a table and inserted BetaID is not 1; 3) Do not allow insertion if a record with inserted GroupID value exists in a table and inserted AlphaID is not MAX(AlphaID)+1 for this GroupID; 4) Do not allow insertion if a record with inserted GroupID value exists in a table and inserted BetaID is not MAX(AlphaID) or MAX(AlphaID)+1 for this GroupID; – Akina May 12 '20 at 6:00
• @Akina as I suspected a trigger is the most suitable way to do this. Thank you for your reply. – Levi May 12 '20 at 8:09