0

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?

  • Hm, good catch ypercube. And that means Pet's needs PK(owner_id, id) as well then, yes? – ralbatross Oct 16 at 15:30
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.

  • I would be interested in hearing your thoughts on the advantages/disadvantages of the two approaches, composite key vs check constraint – ralbatross Oct 16 at 15:26
  • 1
    I would probably opt for a composite key 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 answer was merely a solution that wasn't a composite key as per your question. 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. – HandyD Oct 16 at 22:30
  • I've edited my question to include my request for an opinion on which approach is best. If you add the comment you just made to your answer I'll mark this as the correct answer. – ralbatross Oct 17 at 14:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.