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?