Alter Table Statements In SQL
The ALTER TABLE statement is used to alter the structure of an existing table in a database. It allows you to add, modify, or delete columns, change the data type of columns, and rename columns. You can also use it to add or remove constraints on a table.
Here are some examples of how you can use the ALTER TABLE statement in SQL:
Adding a new column to a table:
ALTER TABLE table_name ADD column_name data_type;
For example, if you want to add a new column called email to a table called users, you can use the following statement:
ALTER TABLE users ADD email VARCHAR(255);
Modifying the data type of a column:
ALTER TABLE table_name ALTER COLUMN column_name data_type;
For example, if you want to change the data type of a column called age to INTEGER, you can use the following statement:
ALTER TABLE users ALTER COLUMN age INTEGER;
Renaming a column:
ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;
For example, if you want to rename a column called first_name to firstName, you can use the following statement:
ALTER TABLE users RENAME COLUMN first_name TO firstName;
Adding a constraint to a table:
ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_type (column_name);
For example, if you want to add a UNIQUE constraint to a column called email, you can use the following statement:
ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE (email);
Removing a constraint from a table:
ALTER TABLE table_name DROP CONSTRAINT constraint_name;
For example, if you want to remove the uq_email constraint from the users table, you can use the following statement:
ALTER TABLE users DROP CONSTRAINT uq_email;
Conclusion:
The ALTER TABLE statement is used to alter the structure of an existing table in a database. It allows you to add, modify, or delete columns, change the data type of columns, rename columns, and add or remove constraints on a table. The syntax of the ALTER TABLE statement varies depending on the specific action you want to perform. Some common examples include adding a new column to a table, modifying the data type of a column, renaming a column, adding a constraint to a table, and removing a constraint from a table.
