oracle11 ora 00922,How to fix this error ORA-00922: missing or invalid option

I think we are coming to the same conclusion: use system generated values for primary keys.

The thing I think you are still questioning has to do with "separate sequence numbers for each shift". You suggest there may be a requirement that each shift in a shop should have its own sequence number. Two questions about that - first, WHY?, and second - does that have to be the PK value? If you need a sequential number that starts from 1 for each shift, why does that have to be the same as the PK used in your table in the database? That should, perhaps, be a separate column. How to populate it is a separate question.

My comment about the primary key not encoding any useful information... here is what I meant. Suppose you have an invoice number (and you will see from my comments that I don't actually know anything about how invoice numbers work) - so, suppose you have an invoice number that's 12 digits long. Somewhere among these digits, say the 7th and 8th digits together encode the vendor's state (as in, which state in the U.S.). The first three digits are the code of the specific employee who prepared the invoice. Etc. Different parts of the invoice number have a meaning. Well, that may be OK for whatever purposes, but is that how data should be stored in a relational database? No, absolutely not (well, "generally" not - I am sure there will be exceptions, although I can't think of any myself). That violates first normal form - each bit of information should be in a different column, with its proper name and data type, and in many cases it should simply be a code pointing to a dimension table. For example: to encode the state you would have a "states" table, with state code, state name and other relevant information about each state, and in the "invoices" table you would have a "state" column where you enter codes. This is a good example, by the way, where you shouldn't need system-generated sequences for PK: in the "countries" table, the state code could very well be PK.

Same way in your example, with several shifts. You should record which shift originated an invoice in a specific column called "shift". This would be a FK pointing to a small "shifts" table, which records shift numbers and perhaps additional information, such as start and end times for each shift. Then, given an invoice number (ANY invoice number), you could query the table to find out which shift that invoice came from. In this setup, why do you need the PK sequence numbers to begin with 1 for each shift? If, for example, you need a manager to quickly "know" which shift initiated an invoice without hitting the DB, that's fine: include a "shift" field on the invoice. Do you need the PK value (or the invoice number or whatever) to tell you how many orders have been filled by that shift so far? Why? For what purpose? (Then we can think about how to address that need... probably not through the PK value!)

I believe what we are discussing here, at a more general level, is using one thing for two very different purposes. The PK is used for establishing relationships between tables in your DB. On the other hand you want to encode useful information (like the date and time of an invoice, which shift it originated from, or other similar bits of information) Whenever possible, we try not to ask one column to serve both purposes. Often that leads to more problems than it solves. There are benefits, of course, but in many (most?) cases the cost is greater than the benefit.

Cheers,   mathguy