ALTER TABLE

The ALTER TABLE statement is used to modify an existing table’s structure or properties. This operation is essential when you need to make changes to your database schema without affecting the stored data, or with minimal impact. This document explains how to use the SQL ALTER TABLE statement.

Syntax (BNF representation)

ALTER TABLE table_name 
    { 
        RENAME TO new_table_name |
        OWNER TO new_owner |
        RENAME COLUMN old_column_name TO new_column_name |
        ADD COLUMN column_name column_data_type [NULL|NOT NULL] [DEFAULT default_value] [BEFORE|AFTER existing_column_name] |
        DROP COLUMN column_name |
        ADD PRIMARY KEY (column_name) |
        DROP PRIMARY KEY |
        ADD CONSTRAINT constraint_name [UNIQUE|FOREIGN KEY] (column_name) [REFERENCES other_table(other_column)] [ON UPDATE|ON DELETE action] |
        DROP CONSTRAINT constraint_name |
        MODIFY PLACEMENT column_name ON STORE store_name [WITH PARTITIONS (partition_values)] |
        ADD [UNIQUE] INDEX index_name ON (column_name) [USING index_type] [ON STORE store_name] |
        DROP INDEX index_name |
        PARTITION BY (partition_method) [(partition_values)] USING (partition_frequency) INTERVAL interval_value unit WITH number_of_partitions PARTITIONS (column_name) [(PARTITIONS number_of_partitions | WITH (column_names) | (PARTITION partition_name VALUES (partition_values)))] |
        MERGE PARTITIONS |
        MODIFY COLUMN column_name { 
    		SET NOT NULL | 
   			DROP NOT NULL | 
    		[SET TYPE] data_type | 
    		SET POSITION { BEFORE column_name | AFTER column_name } | 
    		SET COLLATION { CASE SENSITIVE | CASE INSENSITIVE } | 
    		SET DEFAULT expression | 
   			DROP DEFAULT 
		}
    }

Rename a Table

The RENAME TO clause of the ALTER TABLE statement is used to rename the name of an existing table to a new name. The syntax to rename a table is as follows:

ALTER TABLE old_table_name RENAME TO new_table_name;

Here, old_table_name represents the current name of the table you wish to rename, and new_table_name is the new name you want to assign to the table.

Please note:

• You must have ALTER privileges on the table to rename it.
• Ensure that the new table name doesn’t already exist in the database schema.
• Changing the table name will not affect the names or data types of the columns in the table or any data stored in it.
• Any views, stored procedures, or other dependent objects on the table will need to be updated to reflect the new table name.

Example

Assume we have a table named Employees and we want to rename it to Staff. The SQL statement will look like this:

ALTER TABLE Employees RENAME TO Staff;

After executing the above SQL statement, the Employees table will be renamed to Staff, and all the columns and data in it will remain unchanged. Now, you should use the new table name, Staff, in all future references to this table in your SQL statements.

Change the Owner of a Table

The OWNER TO clause of the ALTER TABLE statement is used to change the owner of an existing table to a new owner. The syntax to change the owner of a table is as follows:

ALTER TABLE table_name OWNER TO new_owner;

Here, table_name is the name of the table whose ownership you wish to change, and new_owner is the new owner of the table.

Please note:

• You must have the necessary privileges or be the current owner of the table to change its owner.
• Ensure that the new owner is a valid user.
• Changing the owner of a table doesn’t affect the names, data types, or data of the columns in the table. However, the new owner will have all the permissions to modify the table structure, data, and permissions.

Example

Assume we have a table named Staff and we want to change its owner to Manager. The SQL statement will look like this:

ALTER TABLE Staff OWNER TO Manager;

After executing the above SQL statement, the Manager will now be the owner of the Staff table. The Manager can now perform all operations on the Staff table, including modifying its structure, data, and permissions.

Rename a Column

The RENAME COLUMN clause of the ALTER TABLE statement is used to change the name of an existing column to a new name. The syntax to rename a column is as follows:

ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;

Here, table_name is the name of the table that contains the column you wish to rename. old_column_name represents the current name of the column you want to rename, and new_column_name is the new name you want to assign to the column.

Please note:

• You must have ALTER privileges on the table to rename its columns.
• The new column name must not already exist in the table.
• Changing the column name will not affect the data type of the column or any data stored in it.
• Any queries, views, stored procedures, or other dependent objects on the table will need to be updated to reflect the new column name.

Example

Let’s demonstrate the usage of the RENAME COLUMN clause with an example. Suppose we have a table named Staff and we want to rename the column LastName to Surname. The SQL statement will look like this:

ALTER TABLE Staff RENAME COLUMN LastName TO Surname;

After executing the above SQL statement, the LastName column in the Staff table will be renamed to Surname, and all the data in the column will remain unchanged. Now, you should use the new column name, Surname, in all future references to this column in your SQL statements.

Add a New Column

The ADD COLUMN clause of the ALTER TABLE statement provides the capability to add a new column to an existing table. The syntax to add a new column is as follows:

ALTER TABLE table_name 
    ADD COLUMN column_name column_data_type [NULL|NOT NULL] [DEFAULT default_value] [BEFORE|AFTER existing_column_name];

Here:

• table_name is the name of the table where you want to add the new column.
• column_name is the name of the new column.
• column_data_type represents the data type of the new column.
• NULL|NOT NULL determines whether the new column can contain NULL values. If not specified, the default is NULL (allowing null values).
• DEFAULT default_value allows you to specify a default value for the new column.
• BEFORE|AFTER existing_column_name allows you to specify the position of the new column in the table structure.

Please note:

• You must have ALTER privileges on the table to add a new column.
• The new column name must not already exist in the table.
• If you don’t specify the position using the BEFORE|AFTER clause, the new column will be added at the end of the table.
• The new column is initially filled with its default value in all existing rows. If you do not provide a default values but allow null values, the new column is initially filled with its default value in all existing rows.
• You must provide a default value if NOT NULL is being set.

Example

Suppose we have a table named Staff and we want to add a new column DateOfBirth with the data type DATE, which should not be NULL and has a default value of ‘1990-01-01’. The SQL statement will look like this:

ALTER TABLE Staff ADD COLUMN DateOfBirth DATE NOT NULL DEFAULT '1990-01-01' AFTER Surname;

After executing the above SQL statement, the DateOfBirth column will be added to the Staff table right after the Surname column. All existing rows in the Staff table will have ‘1990-01-01’ as the value for DateOfBirth.

Drop a Column

The DROP COLUMN clause of the ALTER TABLE statement is used to drop an existing column from a table. The syntax to drop a column is as follows:

ALTER TABLE table_name DROP COLUMN column_name;

Here, table_name is the name of the table that you want to modify, and column_name is the name of the column that you want to remove.

Please note:

• You must have ALTER privileges on the table to drop a column.
• Dropping a column will permanently delete the column and all the data stored in it.
• Any views, stored procedures, or other database objects that depend on the dropped column will be affected and may become invalid.
• Dropping a column cannot be undone. Be careful when dropping columns, especially in a production environment.

Example

Assume we have a table named Staff and we want to drop the column DateOfBirth. The SQL statement will look like this:

ALTER TABLE Staff DROP COLUMN DateOfBirth;

After executing the above SQL statement, the DateOfBirth column will be removed from the Staff table along with all the data in it.

Add a Primary Key

The ADD PRIMARY KEY clause of the ALTER TABLE statement is used to add a primary key to a table. The syntax to add a primary key is as follows:

ALTER TABLE table_name ADD PRIMARY KEY (column_name1, column_name2, ...);

Here, table_name is the name of the table where you want to add the primary key, and column_name1, column_name2, ... are the names of the columns that compose the primary key.

Please note:

• You must have ALTER privileges on the table to add a primary key.
• A table can have only one primary key.
• The primary key consists of one or more columns on the table. When more than one column is used as a primary key, it is often referred to as a composite primary key.
• Each row in the table must have a unique combination of values for its primary key columns.
• Primary key columns cannot contain NULL values. The columns designated as primary key columns thus need to be declared NOT NULL.
• If the table already has data, Polypheny will verify the existing data for uniqueness abefore adding the primary key.

Example

Suppose we have a table named Staff with columns StaffID, FirstName, and Surname, and we want to make StaffID the primary key of the table. The SQL statement will look like this:

ALTER TABLE Staff ADD PRIMARY KEY (StaffID);

After executing the above SQL statement, StaffID will be the primary key of the Staff table. This means each row in the Staff table must have a unique StaffID, and StaffID cannot be NULL.

If you want to create a composite primary key using the StaffID and FirstName columns, the SQL statement will be:

ALTER TABLE Staff ADD PRIMARY KEY (StaffID, FirstName);

After executing this SQL statement, the combination of StaffID and FirstName must be unique for each row in the Staff table, and neither StaffID nor FirstName can be NULL.

Drop a Primary Key

The DROP PRIMARY KEY clause of the ALTER TABLE statement is used to drop an existing primary key from a table. The syntax to drop a primary key is as follows:

ALTER TABLE table_name DROP PRIMARY KEY;

Here, table_name is the name of the table where you want to drop the primary key from.

Please note:

• You must have ALTER privileges on the table to drop a primary key.
• Dropping a primary key will remove the uniqueness constraint imposed by the primary key on its columns.
• Any foreign keys referencing the dropped primary key will be affected and may become invalid.

Example

Assume we have a table named Staff with a primary key on the StaffID column. If we want to drop this primary key, the SQL statement will look like this:

ALTER TABLE Staff DROP PRIMARY KEY;

After executing the above SQL statement, the primary key constraint on the StaffID column will be removed. This means the StaffID column can now contain duplicate and NULL values.

Add a Unique Constraint

The ADD CONSTRAINT clause of the ALTER TABLE statement is used to add a unique constraint to a table. The unique constraint ensures that all values in a column or a set of columns are unique across the table. The syntax to add a unique constraint is as follows:

ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column_name1, column_name2, ...);

Here:

• table_name is the name of the table where you want to add the unique constraint.
• constraint_name is the name you choose for the unique constraint.
• column_name1, column_name2, ... are the names of the columns that make up the unique constraint.

Please note:

• You must have ALTER privileges on the table to add a unique constraint.
• A unique constraint can be applied to one or more columns.
• Each combination of values in the unique constraint’s columns must be unique across the table.
• Unlike primary keys, unique constraint columns can contain NULL values. However, each NULL is considered distinct, and multiple NULLs are considered unique from each other.

Example

Assume we have a table named Staff with columns StaffID, FirstName, and Surname. We want to ensure that no two staff members have the same combination of FirstName and Surname. The SQL statement will look like this:

ALTER TABLE Staff ADD CONSTRAINT unique_name UNIQUE (FirstName, Surname);

After executing the above SQL statement, the combination of FirstName and Surname must be unique for each staff member in the Staff table.

Add a Foreign Key Constraint

The ADD CONSTRAINT clause of the ALTER TABLE statement can be used to add a foreign key constraint to a table. A foreign key constraint establishes a link between the data in two tables. The syntax to add a foreign key constraint is as follows:

ALTER TABLE table_name 
ADD CONSTRAINT constraint_name 
FOREIGN KEY (column_name1, column_name2, ...) 
REFERENCES referenced_table_name (referenced_column_name1, referenced_column_name2, ...)
[ON UPDATE action] 
[ON DELETE action];

Here:

• table_name is the name of the table where you want to add the foreign key constraint.
• constraint_name is the name you choose for the foreign key constraint.
• column_name1, column_name2, ... are the names of the columns in table_name that the constraint applies to.
• referenced_table_name is the name of the table that the foreign key references.
• referenced_column_name1, referenced_column_name2, ... are the names of the columns in referenced_table_name that the foreign key columns correspond to.
• The ON UPDATE and ON DELETE clauses determine the action that happens when the referenced data is updated or deleted. The actions can be CASCADE, SET NULL, SET DEFAULT, RESTRICT, or NONE.

Please note:

• You must have ALTER privileges on the table to add a foreign key constraint.
• The number and data types of the foreign key columns must match those of the referenced columns.
• The referenced columns must be the columns of a UNIQUE constraint or a PRIMARY KEY in the referenced table.
• If the ON UPDATE or ON DELETE clauses are omitted, the default action is RESTRICT.

Example

Assume we have a table named Orders with columns OrderID, CustomerID, and ProductID. We also have a table named Customers with columns CustomerID, FirstName, and Surname. We want to ensure that every CustomerID in the Orders table matches a CustomerID in the Customers table. The SQL statement will look like this:

ALTER TABLE Orders 
ADD CONSTRAINT fk_orders_customers 
FOREIGN KEY (CustomerID) 
REFERENCES Customers (CustomerID)
ON UPDATE CASCADE 
ON DELETE SET NULL;

After executing the above SQL statement, the CustomerID in the Orders table must exist in the Customers table. If a CustomerID in the Customers table is updated, the corresponding CustomerID in the Orders table will be updated (ON UPDATE CASCADE). If a CustomerID is deleted from the Customers table, the corresponding CustomerID in the Orders table will be set to NULL (ON DELETE SET NULL).

Drop a Constraint

The DROP CONSTRAINT clause of the ALTER TABLE statement is used to drop an existing constraint from a table. The constraint could be a primary key, a unique key, or a foreign key. The syntax to drop a constraint is as follows:

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

Here, table_name is the name of the table from which you want to drop the constraint, and constraint_name is the name of the constraint to be dropped.

Please note:

• You must have ALTER privileges on the table to drop a constraint.
• Dropping a constraint will remove the rules enforced by the constraint on the corresponding column(s).
• If other constraints depend on the one being dropped (such as a foreign key relying on a primary key), dropping the constraint may result in an error unless the dependent constraints are dropped first.

Example

Assume we have a table named Orders with a foreign key constraint named fk_orders_customers linking the CustomerID in the Orders table with the CustomerID in the Customers table. If we want to drop this constraint, the SQL statement will look like this:

ALTER TABLE Orders DROP CONSTRAINT fk_orders_customers;

After executing the above SQL statement, the foreign key constraint fk_orders_customers will be removed. This means that CustomerID values in the Orders table are no longer required to have matching values in the Customers table.

Add an Index

The ADD INDEX clause of the ALTER TABLE statement is used to create an index on one or more columns of a table. An index can significantly speed up data retrieval operations on a database table. The syntax to add an index is as follows:

ALTER TABLE table_name ADD [UNIQUE] INDEX index_name ON (column_name1, column_name2, ...) [USING method] [ON STORE store_name];

Here,

• table_name is the name of the table on which you want to create the index.
• UNIQUE is an optional keyword indicating that the index will enforce uniqueness of the indexed columns’ combination. If it’s not provided, duplicate values can exist.
• index_name is the name you choose for the index.
• column_name1, column_name2, ... are the names of the columns to be included in the index.
• method is an optional name of the index method to be used. The available methods depend on where the index is physically created.
• store_name is an optional name of the store where the index will be placed. If not provided, Polypheny will decide the best place to store the index. This can either be on the level of Polypheny itself or on one or multiple of the underlying data stores.

Please note:

• You must have ALTER privileges on the table to add an index.
• While indexes can speed up data retrieval, they can slow down data modification (INSERT, UPDATE, DELETE) because the index also needs to be updated.
• Indexes take up disk space. The more indexes a table has, and the more columns in each index, the more disk space is used.

Example

Assume we have a table named Orders with columns OrderID, CustomerID, and OrderDate. We frequently run queries to fetch orders based on OrderDate, so we decide to create an index on this column. The SQL statement will look like this:

ALTER TABLE Orders ADD INDEX idx_orderdate ON (OrderDate);

After executing the above SQL statement, an index named idx_orderdate will be created on the OrderDate column in the Orders table. This index will make it faster to retrieve orders by OrderDate.

If we want to create a unique index on OrderID and CustomerID combination, we can add the UNIQUE keyword:

ALTER TABLE Orders ADD UNIQUE INDEX idx_order_customer ON (OrderID, CustomerID);

This SQL statement creates a unique index named idx_order_customer on the combination of OrderID and CustomerID columns. This index not only speeds up queries involving these two columns but also ensures that each combination of OrderID and CustomerID is unique in the Orders table.

Drop an Index

The DROP INDEX clause is used to remove an existing index from a table. This is often used to remove obsolete indexes or indexes that no longer provide a benefit in terms of query performance. The syntax to drop an index is as follows:

ALTER TABLE table_name DROP INDEX index_name;

Here, table_name is the name of the table from which you want to drop the index, and index_name is the name of the index to be dropped.

Please note:

• You must have ALTER privileges on the table to drop an index.
• Dropping an index can potentially slow down the queries that made use of this index for faster data retrieval.
• However, dropping an index can speed up data modification operations like INSERT, UPDATE, and DELETE, because the index doesn’t need to be updated.
• Dropping an index will free up the disk space occupied by the index.

Example

Assume we have a table named Orders with an index named idx_orderdate on the OrderDate column. If we no longer require this index, the SQL statement to drop it will look like this:

ALTER TABLE Orders DROP INDEX idx_orderdate;

After executing the above SQL statement, the idx_orderdate index will be removed from the Orders table. This means that queries that previously benefited from this index might run slower. On the other hand, data modifications on the Orders table might become faster, and some disk space will be freed up.

Horizontally Partition a Table

The PARTITION BY clause of the ALTER TABLE statement is used to horizontally partition a table into smaller, more manageable pieces, called partitions. Each partition stores a subset of the table’s data defined by certain criteria. Partitioning can greatly improve the performance of certain types of queries. See Data Partitioning to learn more on data partitioning in Polypheny.

The general syntax for partitioning a table is as follows:

ALTER TABLE table_name PARTITION BY partition_type(partition_column) [(partition_definition...)]

Here:

• table_name is the name of the table you want to partition.
• partition_type can be RANGE, TEMPERATURE, or the name of a column. This determines how the data is divided among the partitions.
• partition_column is the column used for partitioning. The data in this column decides which partition a row goes into.
• partition_definition is an optional specification of how to divide the table into partitions. It depends on the partition_type.

For example, in case of RANGE, you would define each partition like this:

PARTITION partition_name VALUES LESS THAN (value)

Where partition_name is the name for the partition, and value is the upper limit for the values in this partition.

Please note:

• You must have ALTER privileges on the table to partition it.
• Partitioning a large table can take a lot of time, and might require substantial system resources.

Example

Suppose we have a table named Orders with a column OrderDate of type DATE. We decide to partition this table by range of dates. We want to create one partition for each year from 2020 to 2023. The SQL statement will look like this:

ALTER TABLE Orders PARTITION BY RANGE (OrderDate) (
    PARTITION p0 VALUES LESS THAN ('2021-01-01'),
    PARTITION p1 VALUES LESS THAN ('2022-01-01'),
    PARTITION p2 VALUES LESS THAN ('2023-01-01'),
    PARTITION p3 VALUES LESS THAN ('2024-01-01')
);

After executing the above SQL statement, the Orders table will be divided into four partitions. The partition p0 will contain orders made in 2020, p1 will contain orders from 2021, and so on. This will make queries about orders from a specific year faster, because they only need to scan one partition, instead of the whole table.

Merge Partitions

The MERGE PARTITIONS clause of the ALTER TABLE statement is used to remove the horizontal partitioning of a table. The syntax for merging partitions is:

ALTER TABLE table_name MERGE PARTITIONS;

Here,

• table_name is the name of the table whose partitions you want to merge.

Please note:

• You must have ALTER privileges on the table to merge its partitions.
• Merging partitions is a potentially time-consuming operation, depending on the amount of data in the partitions to be merged.

Example

Consider the scenario where we have a table named Orders that we previously partitioned by range of dates into four partitions: p0, p1, p2, and p3. Suppose we want to remove partitioning. The SQL statement to merge these partitions will look like this:

ALTER TABLE Orders MERGE PARTITIONS;

After executing the above SQL statement, the Orders table will no longer be partitioned.

Change Data Type of a Column

This statement allows to modify the data type of an existing column in a table. The syntax for altering a column’s data type is depicted below:

ALTER TABLE table_name MODIFY COLUMN column_name [SET TYPE] new_data_type;

Here,

• table_name is the name of the table that comprises the column you aim to modify.
• column_name is the name of the column that you desire to alter.
• new_data_type is the new data type that you want to set for the column.

*Please note:

• ALTER privileges on the table are mandatory to modify a column’s data type.
• Altering a column’s data type can potentially lead to data loss if the existing data cannot be cast into the new data type. As a safety measure, always ensure to backup your data before initiating such alterations.
• Additionally, verify that the new data type is compatible with all the current data in the column to avert potential data corruption or loss.
• The SET TYPE type part is optional, allowing a common syntax supported by other DBMS.

Example

Let’s say we have a table entitled Customers with a column named Phone that is currently of type VARCHAR(10). We need to modify the data type of the Phone column to VARCHAR(15) to accommodate international phone numbers. Here’s how the SQL statement would look:

ALTER TABLE Customers MODIFY COLUMN Phone SET TYPE VARCHAR(15);

Upon execution of the aforementioned SQL statement, the data type of the Phone column in the Customers table will be modified to VARCHAR(15). Consequently, the Phone column can now store values encompassing up to 15 characters.

Impose NOT NULL Constraint on Column

This statement allows you to alter the definition of an existing column to not accept null values. The syntax to modify a column’s definition to NOT NULL is as follows:

ALTER TABLE table_name MODIFY COLUMN column_name SET NOT NULL;

Here,

• table_name is the name of the table that includes the column you want to modify.
• column_name is the name of the column you intend to modify.

Please note:

• You must have ALTER privileges on the table to modify a column’s nullability.
• Changing a column’s definition to NOT NULL may fail if the column already contains null values. Before executing this operation, ensure all existing data in the column are non-null values.
• If the operation is successful, any subsequent INSERT or UPDATE operation that would cause a null value to be inserted into the column will fail.

Example

Assume we have a table called Orders with a column named CustomerID which is currently nullable. We have decided that all orders must have a valid CustomerID, so we want to alter the CustomerID column to not accept null values. The SQL statement would look like this:

ALTER TABLE Orders MODIFY COLUMN CustomerID SET NOT NULL;

After running the above SQL statement, the CustomerID column in the Orders table will be changed to NOT NULL. This means that you can no longer insert or update a record with a null value for CustomerID.

Remove NOT NULL Constraint on Column

This statement is used to alter the definition of an existing column to accept null values. The syntax to modify a column’s definition to accept nulls is as follows:

ALTER TABLE table_name MODIFY COLUMN column_name DROP NOT NULL;

Here,

• table_name is the name of the table that includes the column you want to modify.
• column_name is the name of the column you intend to modify.

Please note:

• You must have ALTER privileges on the table to modify a column’s nullability.
• Changing a column’s definition to accept null values will not affect the existing data in the column.
• If the operation is successful, any subsequent INSERT or UPDATE operation can include a null value in this column.

Example

Suppose we have a table named Customers with a column named EmailAddress which is currently not nullable. We have decided to allow customers to not provide their email address, so we want to modify the EmailAddress column to accept null values. The SQL statement would look like this:

ALTER TABLE Customers MODIFY COLUMN EmailAddress DROP NOT NULL;

After executing the above SQL statement, the EmailAddress column in the Customers table will accept null values. This means that you can now insert or update a record with a null value for EmailAddress.

Change Position of Column

This statement is used to alter the position of an existing column in a table. The syntax to change the position of a column is as follows:

ALTER TABLE table_name MODIFY COLUMN column_name SET POSITION (BEFORE | AFTER) existing_column_name;

In this statement,

• table_name is the name of the table that contains the column whose position you want to change.
• column_name is the name of the column whose position you want to change.
• (BEFORE | AFTER) specifies whether you want the column to be positioned before or after the existing_column_name.
• existing_column_name is the name of an existing column relative to which you want to position column_name.

Please note:

• You must have ALTER privileges on the table to change a column’s position.
• Changing a column’s position will not affect the existing data in the table.
• The order of the columns might impact the performance of some queries, especially those that involve scanning large amounts of data.

Example

Suppose we have a table named Customers with columns ID, Name, Phone, and Email. We want to move the Email column to be right after the Name column. The SQL statement would look like this:

ALTER TABLE Customers MODIFY COLUMN Email SET POSITION AFTER Name;

After executing the above SQL statement, the Email column in the Customers table will be positioned after the Name column. The new order of columns will be ID, Name, Email, Phone.

Set the Collation of a Column

This statement is used to alter the collation of an existing column in a table. The collation determines how string comparison is done in SQL. This can be important for sorting or selecting data. The syntax to set the collation of a column is as follows:

ALTER TABLE table_name MODIFY COLUMN column_name SET COLLATION (CASE SENSITIVE | CASE INSENSITIVE);

In this statement:

• table_name is the name of the table that contains the column whose collation you want to set.
• column_name is the name of the column whose collation you want to set.
• (CASE SENSITIVE | CASE INSENSITIVE) is used to set the column’s collation to case-sensitive or case-insensitive.

Please note:

• You must have ALTER privileges on the table to change a column’s collation.
• Changing a column’s collation might affect the result of string comparison operations like =, <>, LIKE, ORDER BY, etc.
• Changing a column’s collation does not change the data stored in the column, but only how the data is compared and sorted.

Understanding Collation

In databases, collation determines how string comparison is performed. It’s a set of rules that govern the proper use of characters for either a language, such as English or Spanish, or an alphabet, such as Latin1 or UTF8.

When you define a collation for a column in a database, you’re defining how the DBMS should consider data in that column for sorting and comparing.

A collation determines:

• Whether the comparison is case-sensitive (e.g., ‘A’ <> ‘a’)
• Whether the comparison is accent-sensitive (e.g., ‘a’ <> ‘á’)
• How special characters are sorted (e.g., whether ‘ö’ is sorted at the end of the alphabet, as in Swedish, or next to ‘o’, as in German)

These rules can influence the results of data retrieval and may even influence the performance of a database due to the indexing that is performed based on the collation.

For example, if a column is set to be case-insensitive (CASE INSENSITIVE), then a query for ‘john’, ‘JOHN’, and ‘John’ would return the same results. If the column is set to be case-sensitive (CASE SENSITIVE), then ‘john’ and ‘JOHN’ would be considered distinct values, and a query for one would not return rows containing the other.

Setting the correct collation for a column in a database is critical for the correct retrieval and sorting of data, and for maintaining the integrity of the data.

Example

Suppose we have a table named Customers with columns ID, Name (collation set to CASE INSENSITIVE), Phone, and Email. We want to set the collation of the Name column to be case-sensitive. The SQL statement would look like this:

ALTER TABLE Customers MODIFY COLUMN Name SET COLLATION CASE SENSITIVE;

After executing the above SQL statement, the Name column in the Customers table will be case-sensitive. This means that when comparing the values in the Name column, ‘John’ and ‘john’ would be considered different.

Set a Default Value for a Column

The SET DEFAULT clause is used to change the default value of a column in a table. A default value is the value that is inserted into a column if no data is provided for that column when inserting a new row. This can be particularly useful when you want to ensure that a column always contains a value.

The syntax to set a default value for a column is as follows:

ALTER TABLE table_name MODIFY COLUMN column_name SET DEFAULT defaultValue;

Here,

• table_name is the name of the table that contains the column for which you want to set the default value.
• column_name is the name of the column for which you want to set the default value.
• defaultValue is the new default value that you want to set for the column.

Please note:

• You must have ALTER privileges on the table to set a default value for a column.
• The defaultValue must be compatible with the data type of the column.

Example

Suppose we have a table named Employees with a column Department that is currently of type VARCHAR(20). We’ve decided to set the default value of the Department column to ‘Sales’. The SQL statement will look like this:

ALTER TABLE Employees MODIFY COLUMN Department SET DEFAULT 'Sales';

After executing the above SQL statement, if a new row is inserted into the Employees table without a specified value for the Department column, the Department column will automatically be set to ‘Sales’.

Drop the Default Value of a Column

The DROP DEFAULT clause is used to remove the default value of a column in a table. This means that if no value is provided for that column when inserting a new row, the column will remain null (assuming it is a nullable column) or an error will occur (if it’s a non-nullable column).

The syntax to drop a default value of a column is as follows:

ALTER TABLE table_name MODIFY COLUMN column_name DROP DEFAULT;

Here,

• table_name is the name of the table that contains the column for which you want to drop the default value.
• column_name is the name of the column for which you want to drop the default value.

Please note:

• You must have ALTER privileges on the table to drop a default value for a column.

Example

Suppose we have a table named Employees with a column Department that has a default value of ‘Sales’. We’ve decided to drop the default value of the Department column. The SQL statement will look like this:

ALTER TABLE Employees MODIFY COLUMN Department DROP DEFAULT;

After executing the above SQL statement, if a new row is inserted into the Employees table without a specified value for the Department column, the Department column will be null (assuming it’s a nullable column) or an error will occur (if it’s a non-nullable column).

Previous

ALTER NAMESPACE