Limit the number of records depends on the part of primary key I created database for football matches.

I have 2 tables with a many-to-many relationship, so I also created a junction table:

Referee

CREATE TABLE referee (
  id_referee INT IDENTITY PRIMARY KEY,
  id_type_referee INT NOT NULL,
  first_name varchar(20) NOT NULL,
  last_name varchar(30) NOT NULL,
  date_of_birth DATE NOT NULL,
  FOREIGN KEY(id_type_referee) REFERENCES type_referee(id_type_referee)
);


Match

CREATE TABLE match (
  id_match INT IDENTITY PRIMARY KEY,
  id_stadium INT NOT NULL REFERENCES stadium(id_stadium),
  id_season INT NOT NULL REFERENCES season(id_season),
  date_of_match DATE NOT NULL,
  audience INT CHECK (audience&gt;=0)
);


Junction table

CREATE TABLE match_referee(
  id_referee INT NOT NULL,
  id_match INT NOT NULL,
  PRIMARY KEY(id_referee,id_match),
  FOREIGN KEY (id_referee) REFERENCES referee(id_referee),
  FOREIGN KEY (id_match) REFERENCES match(id_match)
);


I want to limit the number of referees at a single match to three. How can I do that?
And I want to limit the number of referees on a single match to three,
  How can i do that?


One method is with a trigger:

CREATE TRIGGER tr_match_referee
ON dbo.match_referee
FOR INSERT, UPDATE AS
IF EXISTS(
    SELECT 1
    FROM inserted
    JOIN dbo.match_referee WITH (READCOMMITTEDLOCK) ON
        match_referee.id_match = inserted.id_match
    GROUP BY match_referee.id_match
    HAVING COUNT(*) &gt; 3
    )
BEGIN
    RAISERROR('No more than 3 referees allowed per match', 16, 1);
END;


Another approach is to add a column with a unique referee number per match along with a unique and check constraint. You would need to assign the referee number when assigning a ref to a match.

ALTER TABLE dbo.match_referee
    ADD match_referee_number int NOT NULL
    CONSTRAINT CK_match_referee_match_referee_number
    CHECK (match_referee_number BETWEEN 1 AND 3);
ALTER TABLE dbo.match_referee
    ADD CONSTRAINT UN_match_referee_id_match_match_referee_number
    UNIQUE(id_match, match_referee_number);