Changing Data Fields
  • 24 Oct 2023
  • 3 Minutes to read
  • Dark
    Light

Changing Data Fields

  • Dark
    Light

Article summary

Note:
Changing the Name or Type of a data field can significantly impact the data structure. It is necessary to have a backup of the data before making such changes.

Overview

A Data Structure represents real or abstract objects through data fields, which are basic units of information that are variables of the represented object. An example is a Data Structure called Person with data fields such as HairColor, ShoeSize, and DateofBirth. The data fields and their types can be edited after initially creating the Data Structure.

This article demonstrates the outcome of changing the Name or Type of a data field.  

Default Text Length
The default setting for all text stored in the database is 255 characters. However, creating a Data Structure can modify this using the Text Length setting.

Data Field Name Changes

Data Field Naming Rules
Data Types cannot have some special characters and database-reserved keywords. For more information, refer to Data Type Naming Rules.

If the name of a data field is changed within the data structure, SQL will generate a new column with the updated name, even if the table contains data. However, this new column will not initially have any data associated with it.

Original table with no name changeTable after the 'my_age' was changed to 'yearsold'

Data Field Type Changes

Changing the Data Field type may cause duplication of tables in the database. This duplication happens if the data type is incompatible with the newly configured data type. E.g., Changing the data field type from String [Text] to Date Time or enabling the Encrypt option on a data field, etc.

The following section demonstrates Duplicate Tables Warning and Notifications, Retrieving Duplicate Tables, and Inserting Data from Duplicate Tables back into the Original Table.


Duplicate Tables Warning and Notification

Changes made to a data field from one type to another will result in a warning being displayed about this change causing potential problems and may not be possible for certain Data Structures.

However, a user can still save the changes, which will update the SQL table. If the transformed data field is compatible with the new data type, it will be saved with a code compile confirmation message. Conversely, any data that cannot be effectively converted will trigger a code compile error, and the user will be informed through a system notification.

Example: If altering the data type from String [Text] to Date Time, a compile error will pop up. This notification states that the system was unable to migrate the data in column [Column_Name] in [Table_Name] to the new structure. Necessary changes to the structure and the backup of the earlier data is stored in [Duplicate_Table_Name].

Note: Ensure to copy the New Table Name from the notification.

A new duplicate SQL table will be generated after saving the modifications. This duplicated table will encompass all the preexisting data from the data field for which the data type was altered. Simultaneously, the data for the same field will be deleted from the original table.    


Retrieving Data from Duplicate Table

Using the Query Editor, run the query:

select * from [Duplicate_Table_Name]

The query will retrieve the table containing the column with the altered data type and an accompanying ID column. This ID is a duplicate of the one found in the original table. The following section demonstrates how we can insert the data from the duplicate table back into the Original table.


Inserting Data from Duplicate Tables back into the Original Table

To insert the data back into the original table, run the following query in the query editor and alter the query parameters following the table below.

Note:
Data will not be successfully inserted into a table if the data types are incompatible. For instance, attempting to insert a String [Text] into a column with a Date Time data type will result in an error in SQL.
UPDATE target_table
SET target_table.column_to_update = source_table.column_to_copy
FROM target_table
JOIN source_table
ON target_table.common_column = source_table.common_column;
ParameterDescription
target_tableThe name of the table you want to update
column_to_updateThe column name in the 'target_table' you want to update.
source_tableThe table name from which you want to copy the column value.
column_to_copyThe column name in the 'source_table' you want to copy.
common_columnThe common column that is used to match rows in both tables.


Deleting the Duplicate Table

After migrating the entire data, users can delete the duplicate table from the database. To delete the table, run the following query.

DROP TABLE [Duplicate_Table_Name]

Was this article helpful?