Alter table

Altering SQL table refers to the process of modifying the structure of an existing table in the database.

You can use "ALTER TABLE" statement to modify the structure of an existing table(s) in the database. It enables the user to add, modify or remove columns and constraints in a table and is a powerful tool for maintaining and updating a table in the database.

The syntax of the ALTER TABLE statement generally looks like this :

ALTER TABLE table_name
ADD [column_name] data_type [column_constraint],
ALTER COLUMN [column_name] data_type [column_constraint],
DROP COLUMN [column_name],
ADD CONSTRAINT constraint_name constraint_definition,
DROP CONSTRAINT constraint_name;
        

Specify the table name after the 'ALTER TABLE' keywords. Specify the column name, data type and constraint that you want to add, remove or modify in the place of '[column_name]', 'data type' and '[column_constraint]' respectively.

To alter SQL table, you can use the graphical user interface (GUI) or Transact-SQL (T-SQL).

Here's an example of how to alter table using T-SQL :

Suppose you have a table called "[Product]" with columns 'DepartmentID', 'Name', 'GroupName' and 'ModifiedDate'.

1. To add a new column "[ProductName]" to the table and set its default value to 0, you could use the following query:

ALTER TABLE [Product]
ADD [ProductName] VARCHAR(150) DEFAULT 0;
        

2. To change the data type of the "[ModifiedDate]" column from 'DATE' to 'DATETIME', you could use the following query:

ALTER TABLE [Product] 
ALTER COLUMN [ModifiedDate] DATETIME;
        

3. To drop the "[ModifiedDate]" column from the "[Product]" table, you could use the following query:

ALTER TABLE [Product]
DROP COLUMN [ModifiedDate];
        

4. To add a primary key constraint on the "[DepartmentID]" column, you could use the following query:

ALTER TABLE [Product]
ADD CONSTRAINT pk_product PRIMARY KEY (DepartmentID);
        

5. Similarly, to drop this primary key constraint, you could use the following query:

ALTER TABLE [Product]
DROP CONSTRAINT pk_product;
        

That's it! You have successfully altered the [Product] table using T-SQL.

Here's an example of how to alter table using GUI :

Let's modify the [ModifiedDate] column data type from "DATE" to "DATETIME".

1. Right-click on the table name that you want to alter and select the "Design" option from the context menu.

2. "Alter table window" will appear, you can add, modify or remove columns, data types and constraints. Let's change [ModifiedDate] column data type to "DATETIME".

2. Next, click on "SAVE" button or close the window. it will promote to you new window asking whether you want to make changes or not >> Click on the "YES" button.

3. There you go, You have changed [ModifiedDate] column data type to "DATETIME". To verify expand the "[Product]" table >> Expand "Columns folder".

That's it! You have successfully altered the [Product] table using GUI.

Frequently Asked Questions :

ALTER TABLE in SQL is a command used to modify the structure of an existing table.

To edit a SQL table, use ALTER TABLE followed by the specific modifications such as adding, removing, or modifying columns.

The ALTER TABLE method in SQL allows for the modification of table structure, including adding or dropping columns, changing data types, or altering constraints.

You can replace a table in SQL by creating a new table with the desired structure and then copying data from the old table into the new one using INSERT INTO and SELECT statements.