<p>I need to create a unique ID for a given location, and the location's ID must be sequential.  So its basically like a primary key, except that it is also tied to the locationID.  So 3 different locations will all have ID's like 1,2,3,4,5,...,n</p>

<p>What is the best way to do this? 
I also need a safe way of getting the nextID for a given location, I'm guessing I can just put a transaction on the stored procedure that gets the next ID?</p>

You'll want to wrap both the code to find the next ID and the code to save the row in the same transaction.  You don't want (pseudocode):
```
```
transaction {
   id = getId
}

... other processing

transaction {
   createRowWithNewId
}
```
```
Because another object with that id could be saved during "... other processing"