Question about composite primary keys

Question

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?

Answer 1

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)

Answer 2

If I understand you correctly - the foreign key in TicketItem should reference both the id and tenant_id fields in the Ticket table. A foreign key should reference a primary key - if you were to only reference the id, you would not be referencing the primary key of the ticket table, as the Ticket table contains a composite key that includes both the id and the tenant_id fields.

If you have a foreign key in TicketItem that references the Ticket table's primary key (both id and tenant_id), then you will not be able to insert/update a record in the TicketItem table that does not have a corresponding id + tenant_id record in the Ticket table (this is what you desire).

TicketItem: Foreign Key should reference ticket_id -> Ticket.id AND tenant_id -> Ticket.tenant_id

As far as a "good" use of the composite key - it depends on your design/requirements, but there is not anything "bad" about it.

Answer 3

"where the Ticket with id=5 has tenant_id=2"

Based on that wording ("the ticket"), is there only ever one ticket with id = 5? If so, that's your primary key and using the tenant_id to make a composite key is just making things more cumbersome.

If you can have multiple id = 5 then you can use the composite key, and yes it'll need to match both correctly in order for the reference to work.