SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Client (
	[id] INT IDENTITY PRIMARY KEY CLUSTERED,
	[Name] VARCHAR(255)
)

CREATE TABLE Pet (
	[id] INT IDENTITY PRIMARY KEY CLUSTERED,
	[owner_id] INT,
	[Name] VARCHAR(255),
	CONSTRAINT FK_Pet_Owner FOREIGN KEY (owner_id)
	REFERENCES Client (id)
)

CREATE TABLE Contract (
	[id] INT IDENTITY PRIMARY KEY CLUSTERED,
	[client_id] INT,
	[ContractNumber] VARCHAR(25),
	CONSTRAINT FK_Contract_Pet FOREIGN KEY ([client_id])
	REFERENCES Pet (id)
)
CREATE FUNCTION CheckPetContractOwner (@PetId INT, @ContractId INT)
RETURNS BIT
AS
BEGIN
	DECLARE @CorrectOwner BIT
	
	SET @CorrectOwner = (
		SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END
		FROM Pet p
		INNER JOIN Client o ON o.id = p.owner_id
		INNER JOIN Contract c ON c.client_id = o.id
		WHERE p.[id] = @PetId AND c.id = @ContractId
	)

	RETURN @CorrectOwner
END
CREATE TABLE PetInContract (
	[pet_id] INT, --(FK to Pet)
	[contract_id] INT, --(FK to Contract)
	CONSTRAINT PK_PetInContract PRIMARY KEY CLUSTERED ([pet_id], [contract_id]),
	CONSTRAINT FK_PetInContract_Pet FOREIGN KEY ([pet_id])
	REFERENCES Pet (id),
	CONSTRAINT FK_PetInContract_Contract FOREIGN KEY ([contract_id])
	REFERENCES Contract (id),
	CONSTRAINT CK_Check_PetOwner CHECK (dbo.CheckPetContractOwner(pet_id, contract_id) = 1)
)
INSERT INTO Client ([Name]) VALUES ('Bill'), ('Shirley')
INSERT INTO Pet ([owner_id], [Name]) 
VALUES 
	(1, 'Fluffy'), -- Bill's pet
	(2, 'Buster')  -- Shirley's pet
INSERT INTO Contract (client_id, ContractNumber)
VALUES
	(1, 'A001'), -- Bill's contract
	(2, 'B001')  -- Shirley's contract

INSERT INTO PetInContract (pet_id, contract_id)
VALUES
	(1, 1), -- Bill's pet and contract
	(2, 2)  -- Shirley's pet and contract
8 rows affected

INSERT INTO PetInContract (pet_id, contract_id)
VALUES
	(1, 2) -- Bill's pet and Shirley's contract - THIS WILL FAIL
Msg 547 Level 16 State 0 Line 1
The INSERT statement conflicted with the CHECK constraint "CK_Check_PetOwner". The conflict occurred in database "fiddle_e6c537ed9336432cb826453cbd4c2ef2", table "dbo.PetInContract".
Msg 3621 Level 0 State 0 Line 1
The statement has been terminated.