[SOLVED] Question about composite primary keys

Question about composite primary keys

Example table:

Ticket
    - id
    - tenant_id
    - foo
TicketItem
    - id
    - tenant_id
    - ticket_id
    - bar

Assuming that id and tenant_id on each table make up composite primary keys, and that ticket_id is a foreign key to Ticket will this setup protect me from a circumstance where a TicketItem has tenant_id=1 and ticket_id=5 where the Ticket with id=5 has tenant_id=2? In simpler words, would the database allow me to link rows from 2 tables - each with different tenant_id - together, ruining my data, or does it protect me from this?

Also, does the above example seem like a "good" use of a composite primary key?

Solution

IF your Ticket table has a primary key on (TicketID, TenantID), then any table referencing the Ticket table would also have to reference both columns, e.g.

TicketItem(TicketID,TenantID) ==> Ticket(TicketID,TenantID)

You cannot have a reference to just parts of a (compound) primary key, e.g. you cannot have TicketID in TicketItem reference the Ticket table - you need both parts of a compound primary key in every single foreign key referencing it (one of the major drawbacks of compound indices, in my opinion - it makes joins cumbersome)