I have three tables: Headers, LineItems, and Receivers.

The primary key for Headers is ReqId. The primary keys for the other two are ReqId+LineItemNumber.

12345 1
12345 2
12345 3 etc.

Since these are printed on the Purchase order with the line item number they have to keep the correct order. 1, 2, 3, etc.

If the user deletes a line item:
12345 1
12345 3

these two tables have to be renumbered to keep the line item numbers in correct sequence. Since the two primary keys make the row unique, I am trying to think of the best way to handle this.

I think I am going to have to do this through a stored procedure, and I think I need to create a temporary table to do this. But I haven't figured out the sequence yet.

I need some ideas as to how to do this in the best and fastest way. Any ideas?

Thanks.