MySQL ALTER TABLE ADD UNSIGNED

Introduction

In MySQL, the ALTER TABLE statement is used to modify the structure of an existing table. One common modification is to add a new column to the table. When adding a new column, you can specify whether the column should be treated as signed or unsigned. In this article, we will explore the concept of unsigned columns in MySQL and how to use the ALTER TABLE statement to add unsigned columns to a table.

What are Signed and Unsigned Columns?

In MySQL, numeric data types such as INT, BIGINT, FLOAT, etc., can be signed or unsigned. A signed column can store both positive and negative values, whereas an unsigned column can only store positive values or zero.

For example, an INT column can store values from -2147483648 to 2147483647 if it is signed, but it can store values from 0 to 4294967295 if it is unsigned. The same applies to other numeric data types.

Syntax to Add Unsigned Columns

To add an unsigned column to an existing table, you can use the ALTER TABLE statement with the ADD clause, as shown in the following syntax:

ALTER TABLE table_name
ADD column_name datatype UNSIGNED;

Here, table_name is the name of the table to which you want to add the unsigned column, column_name is the name of the new column, and datatype is the data type of the column.

Code Example

Let's say we have a table called employees with the following structure:

id name age
1 John 25
2 Alice 30
3 Bob 35

Now, we want to add a new column called salary to the employees table, which should store positive integer values only. We can achieve this by adding an unsigned INT column to the table using the ALTER TABLE statement.

ALTER TABLE employees
ADD salary INT UNSIGNED;

After executing the above statement, the employees table will have the following structure:

id name age salary
1 John 25 NULL
2 Alice 30 NULL
3 Bob 35 NULL

Note that the salary column is added at the end of the table, and all existing rows will have a NULL value in the salary column.

Benefits of Using Unsigned Columns

Using unsigned columns in MySQL has several benefits:

  1. Memory Optimization: Unsigned columns use a smaller range of values compared to signed columns, which can save memory storage.
  2. Data Validation: Unsigned columns restrict the values that can be stored, ensuring that only positive integers or zero are allowed.
  3. Improved Performance: By using the appropriate data type, MySQL can optimize queries that involve unsigned columns, resulting in faster execution times.

Class Diagram

Here is a class diagram that illustrates the structure of the employees table after the addition of the salary column:

classDiagram
    Table <|-- Employees
    class Table {
        +id: INT
        +name: VARCHAR
        +age: INT
        +salary: INT
    }

Sequence Diagram

Let's consider a scenario where we want to insert a new employee into the employees table. The following sequence diagram depicts the steps involved:

sequenceDiagram
    participant App
    participant Database

    App->>Database: INSERT INTO employees (name, age, salary) VALUES ('Jane', 28, 50000)
    Database->>Database: Validate data
    Database->>Database: Insert new row
    Database->>App: Success message

In the above sequence diagram, the application sends an INSERT statement to the database. The database validates the data and inserts a new row into the employees table. Finally, the database sends a success message back to the application.

Conclusion

The ALTER TABLE ADD UNSIGNED statement in MySQL allows you to add unsigned columns to an existing table. Unsigned columns are useful when you want to store positive integer values only. By using the correct data type and column attribute, you can optimize your database storage, improve data validation, and enhance query performance.

In this article, we explored the concept of unsigned columns, learned the syntax to add unsigned columns using the ALTER TABLE statement, and saw a code example. We also discussed the benefits of using unsigned columns and provided a class diagram and sequence diagram for better understanding.

Next time you need to add an unsigned column to a MySQL table, you will have the knowledge and confidence to do so. Happy coding!