Create T-SQL Constraint to prevent x amount of duplicate records in table?

Question

In table A I have 2 columns:

ID (int, PK)
MaxUsers (int)

In table B I have 2 columns:

ItemID (int)
UserID (int)

The number of records in table A with matching ItemID's cannot exceed the MaxUsers value.

Is it possible to write a T-SQL Table Constraint so that it's not physically possible for this to ever happen?

Cheers! Curt

Answer 1

You could write an on-insert/update trigger that does a rollback of the query when the conditions are no longer met.

Answer 2

You can do this with 'vanilla' constraints e.g. row-level CHECK constraints, UNIQUE constraints, FOREIGN KEYS, making it highly portable e.g.

CREATE TABLE TableA 
(
 ID INTEGER NOT NULL PRIMARY KEY,
 MaxUsers INTEGER NOT NULL CHECK (MaxUsers > 0), 
 UNIQUE (ID, MaxUsers)
);

CREATE TABLE TableB
(
 ID INTEGER NOT NULL,
 MaxUsers INTEGER NOT NULL, 
 FOREIGN KEY (ID, MaxUsers) 
    REFERENCES TableA (ID, MaxUsers), 
 ID_occurrence INTEGER NOT NULL, 
 CHECK (ID_occurrence BETWEEN 1 AND MaxUsers), 
 UNIQUE (ID, ID_occurrence)
);

To maintain the ID_occurrence sequence, you could create a 'helper' stored proc or a trigger.