0

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.

3
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.