- SQL Basics
- SQL Introduction
- SQL Syntax
- SQL Sample Database
- SQL SELECT
- SQL WHERE
- SQL ORDER BY
- SQL DISTINCT
- SQL LIMIT
- SQL FETCH
- SQL AND
- SQL OR
- SQL BETWEEN
- SQL IN
- SQL LIKE
- SQL IS NULL
- SQL Comparison Operators
- SQL Logical Operators
- SQL Alias
- SQL CASE
- Joins and Subqueries
- SQL INNER JOIN
- SQL LEFT JOIN
- SQL RIGHT JOIN
- SQL FULL OUTER JOIN
- SQL SELF JOIN
- SQL CROSS JOIN
- SQL Subquery
- SQL Correlated Subquery
- SQL UNION
- SQL INTERSECT
- SQL EXCEPT
- Aggregate Functions
- SQL AVG
- SQL COUNT
- SQL MAX
- SQL MIN
- SQL SUM
- SQL GROUP BY
- SQL HAVING
- SQL ROLLUP
- SQL CUBE
- SQL GROUPING SETS
- Database Management
- SQL CREATE DATABASE
- SQL ALTER DATABASE
- SQL DROP DATABASE
- SQL BACKUP DATABASE
- SQL SHOW DATABASES
- SQL SELECT DATABASE
- Table Management
- SQL CREATE TABLE
- SQL ALTER TABLE
- SQL ADD COLUMN
- SQL DROP COLUMN
- SQL DROP TABLE
- SQL TRUNCATE TABLE
- SQL SHOW TABLES
- SQL RENAME TABLE
- SQL Constraints
- SQL Primary Key
- SQL Foreign Key
- SQL UNIQUE Constraint
- SQL CHECK Constraint
- SQL NOT NULL Constraint
SQL ALTER DATABASE
The ALTER DATABASE statement is a powerful administrative tool used to modify the global characteristics of an existing database. While you might spend most of your time altering tables, ALTER DATABASE is essential when you need to change how the database handles text encoding, language sorting, or even its read-write status.
ALTER DATABASE as the "Settings" menu for your entire database. It sets the default behavior that new tables will inherit.
Syntax of SQL ALTER DATABASE
While the exact syntax can vary slightly between database engines (like MySQL, PostgreSQL, or SQL Server), the most common use case is updating the character set and collation to support modern data requirements, such as emojis or specific language symbols.
ALTER DATABASE database_name
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
- database_name: The unique identifier of the database you want to modify.
- CHARACTER SET: This defines how characters are stored. Using
utf8mb4is the modern standard because it supports the full range of Unicode, including emojis. - COLLATE: This defines the rules for comparing and sorting strings (e.g., whether 'A' is equal to 'a').
Key Features of SQL ALTER DATABASE
- Global Configuration: It modifies settings that affect the entire database environment rather than individual rows.
- Internationalization: It is the primary way to prepare a database for multiple languages by updating its character encoding.
- Security & Permissions: To run this command, you usually need Superuser or DBA privileges. Regular developers often don't have permission to run this on production servers.
- Read-Only Mode: In some environments (like SQL Server),
ALTER DATABASEcan be used to set a database toREAD_ONLYorREAD_WRITE.
ALTER DATABASE while other users are actively connected to the database. Some systems require "Exclusive Access" to make these structural changes.
Example of SQL ALTER DATABASE
Imagine you have a legacy application called SchoolDB that was originally built using basic Latin encoding. You now need to support international student names with accents and special characters. You would upgrade the database like this:
ALTER DATABASE SchoolDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Output:
The database configuration is updated. Any new tables created in SchoolDB will now default to utf8mb4, ensuring they can store diverse linguistic data correctly.
ALTER DATABASE command. Even though it is generally safe, metadata changes can sometimes cause unexpected behavior in legacy applications.
Checking Current Database Settings
Before making changes, it is a good idea to see what your current settings are. You can query the information_schema, which acts as a dictionary for your database structure:
SELECT schema_name, default_character_set_name, default_collation_name
FROM information_schema.schemata
WHERE schema_name = 'SchoolDB';
This query is helpful for auditing your environments (Development vs. Production) to ensure their settings are perfectly synchronized.
Summary
ALTER DATABASEis used to modify high-level database settings and defaults.- The most common modifications are changing the character set (for data storage) and collation (for sorting/comparison).
- It acts as a default template for future tables but does not retroactively change existing table data.
- Use the
information_schema.schematatable to verify your settings before and after making changes.