SQL Server Assign a Resetting Per-Parent Incrementing Column to Each Child Record

jxct1oxe 于 2023-02-18 发布在其他

关注(0)|答案(1)|浏览(86)

I have two tables, a SalesOrderHeader that has an identity primary key TxnID column and a TxnNumber column that I obtain from external software. For example sake I will omit any other columns and lets say the values in the first 3 rows are like so:
| TxnID | TxnNumber |
| ------------ | ------------ |
| 1 | 00001 |
| 2 | 00002 |
| 3 | 00003 |
| ... | ... |

Then I have a SalesOrderDetail table where TxnNumber is a foreign key. I would like for TxnDetailID to behave as follows:

TxnDetailID	TxnNumber
1	00001
2	00001
1	00002
...	...

Basically kind of act as an identity but on the TxnNumber basis, resetting back to 1 once TxnNumber changes. Is there a way to do so in SQL Server so that it will be continuously follow this rule on data insertion? Because I would like to make a composite primary key that is defined something like: TxnNumber - TxnDetailID

sql-server

来源：https://stackoverflow.com/questions/75478054/assign-a-resetting-per-parent-incrementing-column-to-each-child-record