Can I fix this insertion anomaly without using a composite key?

Question

Suppose I have a schema with the following basic elements:

Client : id (PK)

Pet : id (PK)
      owner_id (FK to Client)

Contract : id (PK)
           client_id (FK to Client)


PetInContract : pet_id (FK to Pet)
                contract_id (FK to Contract)
                PK(pet_id, contract_id)

So a client may have multiple pets and multiple contracts, and each contract may involve any number of the client's pets.

This schema has the problem that it's possible to insert pets into a contract that are not in fact the pets of the client associated with the contract. I believe adding client_id to the primary key in the Contract table fixes this problem:

Contract : client_id (FK to Client)
           id
           PK(client_id, id)


PetInContract : pet_id (FK to Pet)
                client_id
                contract_id
                PK(pet_id, client_id, contract_id)
                FK(client_id, contract_id) references Contract

My question: is the problem fixable in a way that doesn't involve using a composite key? (ie, what if each table in the database is required to have an identity column?) Are there advantages/disadvantages to going one route vs the other?

Answer 1

You can use a CHECK CONSTRAINT and scalar-function to validate if the pet and contract combo is correct based on the pet owner and contract client being the same ID.

Check out this db<>fiddle for an example of this. Also, see here for more information on CHECK CONSTRAINT.

The other alternative is to create your PK on ID columns and create a UNIQUE constraint on your composite keys, effectively making them surrogate keys.

I would probably opt for a composite key or unique constraint myself, check constraints really only enforce the business/app logic on INSERT/UPDATE to prevent the mismatch scenario, whereas keys can provide other benefits.

This is simply a solution that wasn't a composite key as per your question.