SQL Alter Table is a command used to modify the structure of an existing table in a database. It allows you to add, modify or delete columns, constraints, indexes, and other attributes of a table. Altering a table is a common task in database management, as it allows you to adapt the table to changing business requirements or to correct errors in the table structure.
The syntax for the SQL Alter Table command is as follows:
ALTER TABLE table_name ADD column_name data_type [constraint], MODIFY column_name data_type [constraint], DROP column_name, ADD CONSTRAINT constraint_name constraint_definition, DROP CONSTRAINT constraint_name, ADD INDEX index_name (column_name), DROP INDEX index_name;
The Alter Table command consists of several sub-commands, each of which performs a specific action on the table. The ADD sub-command is used to add a new column to the table, along with its data type and any constraints that apply to it. The MODIFY sub-command is used to change the data type or constraints of an existing column. The DROP sub-command is used to remove a column from the table. The ADD CONSTRAINT sub-command is used to add a new constraint to the table, such as a primary key or foreign key constraint. The DROP CONSTRAINT sub-command is used to remove a constraint from the table. The ADD INDEX sub-command is used to create a new index on one or more columns of the table, while the DROP INDEX sub-command is used to remove an existing index.
Let's take a look at some examples of how to use the SQL Alter Table command:
To add a new column to an existing table, use the ADD sub-command:
ALTER TABLE customers ADD email VARCHAR(255) NOT NULL;
This command adds a new column called "email" to the "customers" table, with a data type of VARCHAR(255) and a NOT NULL constraint.
To modify an existing column in a table, use the MODIFY sub-command:
ALTER TABLE customers MODIFY email VARCHAR(320) NOT NULL;
This command changes the data type of the "email" column to VARCHAR(320) and adds a NOT NULL constraint.
To remove a column from a table, use the DROP sub-command:
ALTER TABLE customers DROP email;
This command removes the "email" column from the "customers" table.
To add a new constraint to a table, use the ADD CONSTRAINT sub-command:
ALTER TABLE orders ADD CONSTRAINT fk_customer_id FOREIGN KEY (customer_id) REFERENCES customers(id);
This command adds a foreign key constraint to the "orders" table, linking the "customer_id" column to the "id" column of the "customers" table.
To remove a constraint from a table, use the DROP CONSTRAINT sub-command:
ALTER TABLE orders DROP CONSTRAINT fk_customer_id;
This command removes the foreign key constraint named "fk_customer_id" from the "orders" table.
To create a new index on one or more columns of a table, use the ADD INDEX sub-command:
ALTER TABLE customers ADD INDEX idx_last_name (last_name);
This command creates a new index called "idx_last_name" on the "last_name" column of the "customers" table.
To remove an existing index from a table, use the DROP INDEX sub-command:
ALTER TABLE customers DROP INDEX idx_last_name;
This command removes the index named "idx_last_name" from the "customers" table.
These are just a few examples of how to use the SQL Alter Table command. There are many other sub-commands and options available, depending on the specific database management system you are using. By using the Alter Table command, you can easily modify the structure of your database tables to meet changing business requirements or to correct errors in the table structure.