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.
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;
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 |
+----+----------+-----+-----------+----------+------+
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 |
+----+----------+-----+-----------+----------+
SQL Server Connection String Formats
posted on 2019-08-27 05:53:53 - SQL Server Tutorials