Changing Data Fields

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.  

Data Field Name Changes

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 change	Table 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.

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;

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]