ALTER TABLE



SQL Server - ALTER TABLE Statement


The SQL ALTER TABLE is used to add, delete or modify columns as well as to add and drop various constraints on an existing table.


Syntax

To add a New Column in an existing table:

ALTER TABLE table_name ADD column_name datatype;

To DROP COLUMN in an existing table:

ALTER TABLE table_name DROP COLUMN column_name;

To change the DATA TYPE of a column in a table:

ALTER TABLE table_name MODIFY COLUMN column_name datatype;

To add a NOT NULL constraint to a column in a table:

ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

To ADD UNIQUE CONSTRAINT to a table:

ALTER TABLE table_name 
ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);

To ADD CHECK CONSTRAINT to a table:

ALTER TABLE table_name 
ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);

To ADD PRIMARY KEY constraint to a table:

ALTER TABLE table_name 
ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);

To DROP CONSTRAINT from a table:

ALTER TABLE table_name 
DROP CONSTRAINT MyUniqueConstraint;

Example-1

Consider the CUSTOMERS table having the following records-

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ranith  |  32 | Ahmedabad |  2000.00  |
|  2 | Krupa   |  25 | Delhi     |  1500.00  |
|  3 | kaulik  |  23 | Kota      |  2000.00  |
|  4 | Chaitan |  25 | Mumbai    |  6500.00  |
|  5 | Munny   |  24 | Indore    | 10000.00  |
+----+----------+-----+-----------+----------+

Following is the example to ADD a New Column to an existing table

ALTER TABLE CUSTOMERS ADD SEX char(1);

This would produce the following result:

+----+----------+-----+-----------+----------+------+
| ID | NAME     | AGE | ADDRESS   | SALARY   | SEX  |
+----+----------+-----+-----------+----------+------+
|  1 | Ranith  |  32 | Ahmedabad |  2000.00 |  NULL |
|  2 | Krupa   |  25 | Delhi     |  1500.00 |  NULL |
|  3 | kaulik  |  23 | Kota      |  2000.00 |  NULL |
|  4 | Chaitan |  25 | Mumbai    |  6500.00 |  NULL |
|  5 | Munny   |  24 | Indore    | 10000.00 |  NULL |
+----+----------+-----+-----------+----------+------+

Example-2

Following is the example to DROP sex column from the existing table.

ALTER TABLE CUSTOMERS DROP SEX;

This would produce the following result:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ranith  |  32 | Ahmedabad |  2000.00  |
|  2 | Krupa   |  25 | Delhi     |  1500.00  |
|  3 | kaulik  |  23 | Kota      |  2000.00  |
|  4 | Chaitan |  25 | Mumbai    |  6500.00  |
|  5 | Munny   |  24 | Indore    | 10000.00  |
+----+----------+-----+-----------+----------+


Connection String

SQL Server Connection String Formats

posted on 2019-08-27 05:53:53 - SQL Server Tutorials


UNION

union in SQL server

posted on 2019-08-09 23:05:33 - SQL Server Tutorials


System Functions

System Functions in SQL Server

posted on 2019-08-09 06:14:33 - SQL Server Tutorials


Prompt Examples

ChatGPT Prompt Examples

posted on 2023-06-21 22:37:19 - ChatGPT Tutorials


Use Cases

Chat GPT Key Use Cases

posted on 2023-06-21 21:03:17 - ChatGPT Tutorials


Prompt Frameworks

Prompt Frameworks

posted on 2023-06-21 19:33:06 - ChatGPT Tutorials