Printable View
I was trained to think that one sequence is enough for many tables since the sequence number can go very high and no two picks will be alike. But at my new workplace each table has its own sequence. My question is which way makes better sense, or maybe it doesn't really matter, just depends on what you like?
To me the multiple sequence would make sense, if I were to design an application. Think that you want to assign a unique number to the employee and a unique number to the dept. If you have one sequece you wouldn't be able to achieve this result. So by having two seqence numbers you would be able to uniquely diffrentiate the two table data.
I hope this would explain the things about sequence
Sam
I'm of the EXTREMELY old school in that I think a PK should be defined by the elements in the table and not by some fabricated key.
That being said, it makes more logical sense that each table has it's own sequence. For example, (If I can borrow from a previous post) you have a table of products and one of users. Using two sequences your users would be numbered 1-000 and your products would also be numbered 1-1000. If you used one sequence 1 would be a product, 2-5 would be users, 7,8,13 would be users, etc.
Would one sequence work? Sure. I think it is more difficult to understand though. Also, you would want to keep an eye on performance with alot of users requesting a sequence at the same time...
For my two scents, the answer is "it depends"!
If you already have a column that will meet the requirements for PK (Unique and Not Null values), then I would use it - why create additional storage overhead if you don't need to.
For the tables whose PK values might be used to show relationship between rows (relative age, etc) or should have few or no gaps in the numbering sequence, I would create one sequence per table.
If you have tables for which you just need a PK and don't care about the values, I would use a common sequence.