We want to add a new column LastModifiedBy to all tables in an existing schema.

Value is updated by the application platform on all inserts/updates and is populated with the application user (not database user) running the transaction.

Column values cannot be NULL (our schema is like this).

What default value would you put in here when upgrading existing customer databases? A dummy value, or add a new "migration" user, or something else?

closed as primarily opinion-based by Philᵀᴹ, Colin 't Hart, mustaccio, Marco, McNets Jan 11 at 9:27

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

  • So you said LastModifiedBy column is NOT NULL, what datatype it is [Varchar(50)]?? You can update with last updated person name or number.. I don't think we have default value for column like this. – CR241 Jan 9 at 22:32
  • 2
    This is entirely your decision based on your application needs – Philᵀᴹ Jan 9 at 22:32
  • @CR241: in our case the user id is a guid. If I add a dummy value not present in the user table, then joins to that would not work on migrated values. Maybe just add a migration user to user table and use that – Kennie Nybo Pontoppidan Jan 9 at 22:35
  • I think in this case it doesn't matter what value you add from application. What you tried till now??? and post the current and expected results for better understanding. – CR241 Jan 9 at 22:40
  • Please tag your RDBMS version included. – McNets Jan 11 at 9:27

What use do you make of the value in LastModifiedBy? Is it important to know a row was touched by the migration? Is it (more) important to know which actual person amended the row, even before the migration? Answer that and you have your solution.

The implementation is then just a matter of satisfying the technical needs. Since the column is varchar(50) "migration" is as good a sequence of characters as any other. I could also see "upgrade" or the target version number being acceptable. If there is a foreign key defined then it must be respected: add the corresponding row to the referenced table. Make sure you document it's special purpose - perhaps there is a description column on that table?

Not the answer you're looking for? Browse other questions tagged or ask your own question.