On my job (non programming job that takes advantage of some of my programming skills), I was asked to generate a report from our outsourced, custom made program's SQL database.
When looking at the tables I see that many of them have multiple fields assigned as PK, why is that? What advantage does that have over a serial, numeric id?
Data integrity. A key means a uniqueness constraint that prevents duplicate data entering the database. Keys help ensure that facts in the database can be properly identified with the real-world objects or concepts that they are supposed to describe. In practice not everything can or should be identified by a single attribute in a single column.
Note: A key with more than one attribute is not multiple keys. It's still just one key (called a composite key).
It is called a composite key. If order_no is primary key in table Order and in table Order_Item there is a unique sequence number within order assigned to each order item, i.e. all orders have items beginning with sequence number 1 then the primary key for Order_Item would be order_no, item_sequence_no.
Contrast this with a generated primary key for Order_Item: you would still need a unique index on order_no, item_sequence_no. If you think about the queries you might find that more rely on the composite key.
The business dependent and system generated keys are technically known as domain key and surrogate key respectively. c2 wiki has a good discussion on it.
Sometimes multiple values in a table will form a natural primary key, as opposed to a surrogate, auto-generated primary key.
A few of the advantages of this would be that if those multiple values uniquely identify a record, then the PK enforces uniqueness, and in MS SQL Server, by default that PK will also be the clustered index on the table.
That depends on the table and the columns used as PK.
If the table if question is a join table used in many-to-many association, then you can use a composite primary key from the foreign key columns (as their combination will be unique in join table) and no additional synthetic primary keys will be needed.
Other example is a natural key, when some combination of existing column values can be used to uniquely refer to a row in table (for example, first and last name for people). They aren't much used, as most such candidate natural keys don't have desired properties (that is, they are not unique or not immutable).
