Creating a UDT
We will create a UDT named ‘Employee”, which will represent employee details.
CREATE TYPE Employee AS (
id INT,
name TEXT,
age INT,
department TEXT
);
Thus, in the above code, we have defined the UDT “Employee” which contains four fields, namely: “id” which has “INT” type and stores the employee id, “name” which has “TEXT” type and stores the employee’s name, “age” which has “INT” type and stores employee’s age, and lastly we have “department” which has “TEXT” types and stores employee’s department.
Since we already created the UDT, we can use it as a custom datatype within our database. So we will create a new database named “EmployeeData” with the column type “Employee“.
CREATE TABLE EmployeeData (
employee_details Employee
);
Thus, by defining the UDT named “Employee”, we can use it as many times as necessary, which will represent the employee information. Thus, we can define the tables or columns by creating a custom data structure. It will provide us with a consistent and structured way of storing employee information in our database.
Modifying the UDT
Over time, we might need to do some changes to the database to adapt to the changing times. Thus modifying a user-defined type is needed to help adapt to the structure and behavior of the custom datatypes we created earlier in the database. There are commonly 2 modifications that are done:
- Adding a New Column
- Renaming an Existing Column
- Changing the Data Type of the Column
- Dropping a UDT
Adding a new column
The most common example of modifying a user-defined type is adding a new column or field to the existing UDT. Earlier in the blog, we created a UDT named "Employee". So now, to add a new column to the existing UDT, we need to alter the UDT definition and include the new column and its details. So now we will add a new column called salary of type DECIMAL to store employee salaries in the existing Employee UDT.
-- Original UDT definition (as defined previously also)
CREATE TYPE Employee AS (
id INT,
name TEXT,
age INT,
department TEXT
);
-- Modified UDT with an added column
CREATE OR REPLACE TYPE Employee AS (
id INT,
name TEXT,
age INT,
department TEXT,
salary DECIMAL
);
In the above code, we first define the original “Employee” UDT. Then, we use the “CREATE OR REPLACE TYPE” statement to modify the UDT by adding the salary column. This alteration ensures that the UDT retains the existing columns (“id”, “name”, “age”, “department”) while incorporating the new “salary” column.
Renaming the column
Another example of modifying a user-defined type is renaming the column. We have seen how to add a new column to the existing UDT. Now we will see how to rename a column in a UDT. To do that, we need to alter the UDT definition and provide a new name for the column. We will again use the previously defined UDT named Employee UDT and rename the department column to department_name.
-- Original UDT definition
CREATE TYPE Employee AS (
id INT,
name TEXT,
age INT,
department TEXT
);
-- Modified UDT with a renamed column
CREATE OR REPLACE TYPE Employee AS (
id INT,
name TEXT,
age INT,
department_name TEXT
);
In the above code, we use the CREATE OR REPLACE TYPE statement to modify the UDT. We specify the new name, department_name, for the originally named department column. This alteration updates the UDT definition and reflects the column name change in the UDT structure.
Changing the Data Type of a Column
When one wants to change the datatype of the column in the previously defined “Employee”, we can use the “ALTER TYPE” command with the “ALTER” keyword.
ALTER TYPE Employee ALTER age TYPE FLOAT;
Thus, in the above example, we have changed the datatype of the column “age” to “FLOAT” type from previously defined as “INT” type.
Dropping a UDT
There might be a situation where one doesn't need the user-defined type; then, we can drop it. Since the YDT takes up space, we should delete it if the UDT is no longer required.
DROP TYPE Employee;
This command removes the Employee UDT from the database and all tables or columns that reference it.
Also see, Recursive Relationship in DBMS
Frequently Asked Questions
Can I modify the structure of a UDT after it has been created?
Yes, we can modify the UDT by adding a new column, renaming the existing field/column, or changing some properties to improve data compatibility.
What considerations should I consider when adding a new column to a UDT?
While modifying a user-defined type, some considerations that need to be kept while adding a new column in the UDTs are data compatibility, code adjustments, and potential conflicts when adding a new column in the UDT.
How can I handle existing data when renaming a column in a UDT?
It's essential to manage the existing data well by renaming the existing column, so we can migrate the data to update the existing data so that it reflects the new column name. Thus updating the code and queries will help avoid these errors.
Conclusion
In conclusion, we have seen in this blog definitions of user-defined types, their benefits, and use cases. We then read about how to create and modify a UDT by adding or renaming a new column. The central command used to perform both operations is the “CREATE OR REPLACE TYPE” statement to add or rename columns from the existing UDTs. Also, to change the data type of a column, we can use the “ALTER TPE” statement, while for dropping a UDT, we can use the “DROP” statement.
And if you would like to explore more about MySql variables and multi-user OS, do refer to the below blogs for more information:
For more information, refer to our Guided Path on Coding Ninjas Studio to upskill yourself in Python, Data Structures and Algorithms, Competitive Programming, System Design, and many more! Head over to our practice platform, Coding Ninjas Studio, to practice top problems, attempt mock tests, read interview experiences and interview bundles, follow guided paths for placement preparations, and much more!
569+ registered 
