dwechter
asked on
Composite Key With "Dynamic" Identity Column or Something Similar
Hi All -
Relatively simple idea here, but one that has me a bit stumped as to the best solution. I want to create an MS SQL table that will store comments related to account issues. I want to identify, in a composite key, the account number and the incremental comment number (1, 2, 3). When a new account number is added, again I want to identify that comment number incrementally. A composite of ACCT_NUM (integer) and COMMENT_NUM (identity integer) works for the first account, but the identity number will continue upward indefinitely and will not properly reset to number the comment by account.
So, experts, any ideas to create the following? The preference would be that it happens at the table level, and not in an insert command (if possible)!
ACCT_NUM COMMENT_NUM
987654 1
987654 2
456123 1
456123 2
456123 3
222222 1
333333 1
Thanks!
Relatively simple idea here, but one that has me a bit stumped as to the best solution. I want to create an MS SQL table that will store comments related to account issues. I want to identify, in a composite key, the account number and the incremental comment number (1, 2, 3). When a new account number is added, again I want to identify that comment number incrementally. A composite of ACCT_NUM (integer) and COMMENT_NUM (identity integer) works for the first account, but the identity number will continue upward indefinitely and will not properly reset to number the comment by account.
So, experts, any ideas to create the following? The preference would be that it happens at the table level, and not in an insert command (if possible)!
ACCT_NUM COMMENT_NUM
987654 1
987654 2
456123 1
456123 2
456123 3
222222 1
333333 1
Thanks!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry, the order by ID should be ASC not DESC...
ASKER
Perfect! I'll use the ACCT_NUM and a DTTM as a composite key, and extract and display using the query above, which works perfectly. Thanks all.
ASKER
I suppose that would work, thanks. The ideal solution is still a number though, because it's easier to discuss over the phone. For example, 2 users at different locations looking at an ASP page (fed from this data) with an account with 20 comments. It would be easier for them to say something like "refer to comment 13, that's where the issue is."
Dan