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?

score 1 · Accepted Answer · answered Apr 03 '11 at 19:18

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)

answered Apr 03 '11 at 19:18

marc_s

732,580
175
1,330
1,459

Thanks Mark +1. Clearly I have much to learn regarding composite primary keys. Does this mean that `TicketItem`'s foreign key will be a composite key in itself? Also, does this mean that I can have the same composite primary key in each table (`tenant_id` and `id`) and that when I try to link a `TicketItem` with `tenant_id=2` to a `Ticket` with `tenant_id=1` the database will throw an integrity error? I suppose the reason I'm asking these things is that I don't otherwise see the value of a composite primary key. – orokusaki Apr 04 '11 at 00:58
@mark_s - also, would you or wouldn't you avoid using composite fields for the most part (including multi-tenancy like this). – orokusaki Apr 04 '11 at 04:43
@orokusaki: by default, I always try to avoid composite keys - they just get hard to use, they negatively affect performance, and they're just a pain. Unless I have a very compelling reason not to, I always use a single column key - and use a surrogate `INT IDENTITY` if no natural key is easily available. – marc_s Apr 04 '11 at 07:59
@marc_s: You said, "You cannot have a reference to just parts of a (compound) primary key". Actually, you can. If ticket.id were declared unique, you could reference ticket.id alone, even if the primary key were (id, tenant_id). – Mike Sherrill 'Cat Recall' Apr 04 '11 at 10:38
@catcall: ok, yes - if you can define parts of a PK as unique, then you can reference it separately. But this is a hack - you need to do this explicitly - it's not just possible without this hack. And: **WHY** would you have a composite key, if even a part of your key is already unique?? Wouldn't that part alone be good enough for a PK then?? – marc_s Apr 04 '11 at 11:10
@marc_s: Ordinarily, I'd agree with you. But auto-incrementing id numbers are guaranteed to be unique. It's almost a knee-jerk response for some designers to also declare them `UNIQUE`. I'd call that a mistake here, but certainly not a hack. Why have a reducible composite key? To work around limitations in SQL. Multi-tenant, shared schema designs require it. (http://iablog.sybase.com/kleisath/index.php/2009/11/multi-tenant-database-architecture-part-5/) So do supertype/subtype designs. (http://stackoverflow.com/questions/4969133/database-design-problem) – Mike Sherrill 'Cat Recall' Apr 04 '11 at 11:30
@Catcall - is there a way to make an integer field that is part of a composite primary key auto-increment with regards to the other part of the primary key (ie, When I create `Tenant [id=6]` there are already 325 `Ticket`s in the database. I don't want the first `Ticket` that `Tenant [id=6]` creates to be `Ticket [tenant_id=6, id=326]`. I'd rather it be `Ticket [tenant_id=6, id=1]`, if the `id` field doesn't need to be unique on its own. Is this possible? – orokusaki Apr 04 '11 at 14:33
@orokusaki: It's not possible using only DDL. You'd have to code a stored procedure or a function to do the hard work. The way I look at it, a 32-bit unsigned integer gives me 4 billion rows, a 64-bit integer gives me 18 quintillion rows (?) and a GUID gives me, um, a lot more rows. I think I'd probably just pick one of those, and live with it. – Mike Sherrill 'Cat Recall' Apr 04 '11 at 15:05

dugas · Answer 2 · 2011-04-03T19:16:30.740

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.

score 0 · Answer 3 · answered Apr 03 '11 at 19:23

"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.

Question about composite primary keys

3 Answers3