Validate number of rows depending on master row field value

Question

I need to add a constraint to validate that number of rows referencing master table is lower than value in master row, e.g we have a table master(master_id int pk, max_val int) and slave(slave_id int pk, master_id fk ref master(master_id)) (so slave is de facto a colection of something), and I want that count(master_id) in slave is <= than max_val for this master_id. I have a constraint

 constraint NO_MORE_PASS check ((select count(head_id) from parts p where 
 p.head_id = head_id) <= (select max_val from head where id = head_id));

(not sure if it is correct, however SQL Server tells that subqueries are not allowed (sql server 2017) so...).

I have also read Check Constraint - Subqueries are not allowed in this context, so the question: is there any other alternative (I would like to avoid using trigger)?.

I'am using this in spring app with spring data jpa (and hibernate) - may be useful, but would like to make it on db side rather than in the app. Nethertheless entity it is like:

@Entity
@Table(name = "route_parts")
data class RoutePart(
  @Id
  @Column(name = "route_part_id")
  @GeneratedValue(strategy = GenerationType.AUTO)
  var id: Long? = null,
//...

  @Column(nullable = false)
  var slots: Int? = null,

  @ManyToMany(fetch = FetchType.LAZY)
  @JoinTable(name = "route_part_passengers",
    joinColumns = [(JoinColumn(name = "route_part_id"))],
    inverseJoinColumns = [(JoinColumn(name = "user_id"))]
  )
  var passengers: Set<ApplicationUser> = setOf()
) 

and in that case ApplicationUser is a slave (or better - another table will be created, and actually this will be that slave table) limited by slots value.

So the question is...

How can I achieve limiting number of ApplicationUser attached to each RoutePart

Answer 1

If you want your check constraints to be based on queries, you must use a user defined function for the check constraint to work with.
Here is a quick example:

Tables:

CREATE TABLE dbo.Parent
(
    Id int,
    MaxNumberOfChildren int NOT NULL
);

CREATE TABLE dbo.Child
(
    Id int,
    ParentId int
);

User defined function (All it does is return the difference between the MaxNumberOfChildren and the number of records in the Child table with the same ParentId):

CREATE FUNCTION dbo.RestrictNumbrOfChildren
(
    @ParentId int
)
RETURNS int
AS
BEGIN

    RETURN 
        (
            SELECT MaxNumberOfChildren
            FROM dbo.Parent
            WHERE Id = @ParentId
        )
        - 
        (
            SELECT COUNT(Id) 
            FROM dbo.Child
            WHERE ParentId = @ParentId
        )
END;

Add the check constraint to the Child table:

ALTER TABLE dbo.Child
    ADD CONSTRAINT chk_childCount CHECK (dbo.RestrictNumbrOfChildren(ParentId) >= 0);

And that's basically all you need, unless MaxNumberOfChildren is nullable.
In that case, you should add ISNULL() to the first query, with either 0 if null means no children are allowed, or the maximum value of int (2,147,483,647) if null means no restriction on the number of children - so it becomes SELECT ISNULL(MaxNumberOfChildren, 0)... or SELECT ISNULL(MaxNumberOfChildren, 2147483647)....

To test the script, let's insert some data to the Parent table:

INSERT INTO Parent (Id, MaxNumberOfChildren) VALUES
(1, 3), (2, 2), (3, 1);

And insert some valid data to the Child table:

INSERT INTO Child (Id, ParentId) VALUES
(1, 1), (2, 2);

So far, we have not exceeded the maximum number of records allowed. Now let's try to do that by insert some more data to the Child table:

INSERT INTO Child (Id, ParentId) VALUES
(3, 1), (4, 1), (5, 1);

Now, this insert statement will fail with the error message:

The INSERT statement conflicted with the CHECK constraint "chk_childCount". The conflict occurred in database "<your database name here>", table "dbo.Child", column 'ParentId'.

You can see a live demo on rextester.