Keep a column in sync with another column of the same table

advertisements

I'm wondering if it's possible to have a column always kept in sync with another column in the same table.

Let this table be an example:

+------+-----------+
| name | name_copy |
+------+-----------+
| John | John      |
+------+-----------+
| Mary | Mary      |
+------+-----------+

I'd like to:

Be able to INSERT into this table, providing a value only for the name column - The name_copy column should automatically take the value I used in name

When UPDATE-ing the name column on a pre-existing row, the name_copy should automatically update to match the new & updated name_column.

Some solutions

  • I could do this via code but that would be terribly bad as there's no guarantee the data would always be accessible by my code (what if someone changes the data through a DB client?)

Use Computed column

A computed column is a virtual column that is not physically stored in the table, unless the column is marked PERSISTED. A computed column expression can use data from other columns to calculate a value for the column to which it belongs

Alter table tablename add name_copy as (name) persisted

If the column already exists then you need to drop and recreate the column

Alter table tablename DROP COLUMN name_copy

Alter table tablename add name_copy as (name) persisted

Instead of dropping, you can rename the old column and add the new computed column. Check if everything is fine then drop the renamed column. To rename use the below code

EXEC sp_rename 'tablename.name_copy', 'bckup_name_copy', 'COLUMN';