|I am not really in agreement with the other comments since they somehow seem to be drilling down on a 'primary' key even though I see nothing in your request that suggests that.
Your posting should focus on creating an 'invoice number'
It will need to have the following
1. Be unique
2. Be human readable
3. Be small enough that humans will not be annoyed by it.
Presumably the 'prefix_text' is unique by itself. It does not rely on the 'table_name'. If that is not the case then the uniqueness constraint for the invoice number will not be met by the form that you posted.
I would suggest that you do not use underscores. A separator is ok but use a dash since it tends to be more obvious. However you can also consider the dash as being a display character only. So for example credit card numbers often use a dash but the numbers are actually the only part of the actual id. So your UI could take it with dashes but remove it for searches. And in the database you store it without the dashes.
Without the separator the format must be fixed however. So it is a design decision.
Volume of the business and generation of the code, now and for some time into the future, impacts how you might want to do this. If you are producing say 100 of them a day then it is easier than if you are producing 100 million in a day.
There might be a security concern if you generate numbers that are easily guessed. So for example if you use the current date along with an incrementing number then it is easy to guess valid invoice numbers. This also is a matter for how the business works. It isn't much of a concern if there are other authentication factors used to limit access to the invoices.
I don't see anything conceptually wrong with your basic idea although I would format the id (pad zeros to the left.)
Fidele Okito wrote:using entity framework.
I am guessing that this in fact is your real problem.
What you want to do is just use native SQL. Myself I would probably use a stored proc but you can do it with SQL in the application.
You need the following in a transaction. This is pseudo code but the idea is basically sound.
declare @lastId int;
update table1 set next_id = next_id + 1 where prefix_text='xxx' and table_name='fff';
select @lastId = next_id from table1 where prefix_text='xxx' and table_name='fff';
insert into table2 (invoice_number) values('xxx' + '-' + @lastId + '-' + ...);
The last part of that returns the newest id to your application. Not needed if that is not something that is needed at that point.
The transaction is necessary to insure uniqueness.