Search code examples
sql

Can SQL table have multiple columns with primary key?

Can SQL table have multiple columns with primary key?

Solution

  • If you mean "can a primary key in SQL have multiple columns", the answer is yes.

    To (finally) add to this: for tables that will never grow beyond 10-100 million rows, do whatever you like -- it's not a big deal.

    For anything bigger than that, you should not do this. Composite/compound keys are terrible for performance, they are terrible for maintainability, they are terrible for development -- and you'll notice on these bigger tables. I think it's worth adding a separate single PK just to make writing and maintaining queries easier, but the value of that is debatable.

    Not so much in billion-row+ tables. At that point you should always add a new, properly partitioned/sharded separate PK (large integer type) for other tables to reference it, and a UNIQUE CONSTRAINT to, shockingly, enforce uniqueness.