Forcing Sequence at Database Level 1

[BoulderBum]

I have a table with the key:

PK id_num
PK sequence_num

Basically, for each ID, there can be 0 to N records in the table, but I'd like the sequence value to be, well, sequential, like:

id_num = 090
sequence_num = 1

id_num = 090
sequence_num = 2

etc.

In other words, I don't want the sequence numbers to jump from 1 to 5 or something.

Is there a way to set up this constraint in SQL Server, or do I need to do this programmatically?

 

[RonPro]

A constraint will only validate data within a row. It can't populate the row.

You could set up an instead of trigger to perform that logic. Or maybe an insert trigger with the field content being optional (simply to let the row get into the table initially).

What if sequence numbered rows are deleted?

You can use an identity column type, but that will not guarantee consecutive numbers, only ascending (there are other discussions on that topic in here...)

 

[BoulderBum]

"What if sequence numbered rows are deleted"

One of the other problems is that parts of my business code rely on the sequence numbers. Fortunately, only the developers will have access to this database (it's used to dynamically build a page), but you're right about the possible maintenance issues. Even given this concern, however, the current approach will probably be the easiest and most maintainable, believe it or not.

I was hoping that there might be some mechanism to clump numbers for a similar category to cut down on the amount of developer written validation code, but I guess there isn't. I thought as much, but just wanted to be sure.

Thanks!

PS- Can identity columns increment by group? I always thought of them as a unique identifyer for the entire table.

 

[RonPro]

You are correct - identity columns increment within the table's life-cycle - that's important as the numbers will change if you reload (not restore, though) the table entries.

 

[RonPro]

In my last post I had a bit of a mind lapse. You can reload a table if you use DTS for the import process and that will give you the ability to "Allow identity insert' (or text similar to that). That allows you to maintain the identity values for the data brought in.