Naomi,

>I think it's very hard to achieve what you're trying to achieve and probably not worth the effort. Your Order number should be a unique key and user should not even see it. If the later is the case, then it doesn't matter if some numbers are skipped.

Sorry, but I must disagree with you on this one. Many systems use a "meaningful" Order # for printing on the sales order / invoice and for lookups by the number that the customer sees. If I order something from a company, I want to have a reference number that the system's user and I as the customer know about.

This, however, does not have to be used as the primary key, which can itself be a "meaningless" auto-assigned number that the customer never sees. You are correct that it doesn't matter if numbers are skipped in the PK, but the customer's requirement in this case seems to be that they don't want skips in the sequence of meaningful order numbers.