0

I have a table that looks like this:

database table

I would like to have a sequence that would increment the version from 1 for each brand that is saved into the table. Currently in my service I am grabbing the highest version for a brand, incrementing it by one, and using that for the new version (or using 1 if the results come back as empty).

However, whenever I delete a version, and insert a new one, it will reuse the version that was just deleted, which is undesirable. Is there any kind of sequence that I can use that pays attention to the values of a specific column and will increment for specific values? Like if the brand were McDonalds, it would have its own sequence that starts from 1, and if the brand were Burker King, it would have its own sequence that starts from 1.

The only alternative solution I can come up with is to create a new table that keeps track of all of the brand and version pairs that have ever been made (initially I would take the highest version and make a row for each brand and version combo for each version under the current version), but I would like to avoid making a new table if possible.

Sean San
  • 43
  • 1
  • 5
  • 1
    https://stackoverflow.com/questions/6369152 or https://stackoverflow.com/questions/6821871 –  Jul 16 '19 at 23:29

1 Answers1

0

In MS SQL, you can the table with identity key as: 

CREATE TABLE vanities 
(id INT, 
version INT IDENTITY (1,1), 
description VARCHAR)

So, whenever you delete a record from values already inserted, the new insertion won't take a deleted identity column value.

lije
  • 420
  • 2
  • 15