Post
Ask Question

Altering the table Structure using the DDL command ALTER in SQL - Part 1

Introduction 
 
We can change the structure of the table anytime after the table is created. When we change the schema or specifications of a table, the table is considered as 'altered'. The following are the situations in which a table in a database is considered to be altered.
  1. When we add a new column to the table after the table is created. 
  2. When we remove a column from the table. 
  3. When we want to increase/decrease the width of the column that is, changing the size of the column.
  4. When we want to change the datatype of the column.
  5. When we want to add a constraint on a column of a table after the table is created. 
  6. When we want to drop/remove a constraint on a column of a table. 
  7. When we want to rename a column in a table.
  8. When we want to rename the table itself. 
CREATE DATABASE mysampdb1
use mysampdb1
 
General Syntax to add new column(s) to the table, we can add one or more than one column to a table at a time.
 
ALTER TABLE <TabName>
ADD <col1> <datatype>[,<col2> <datatype>,......,<coln> <datatype>]
 
General Syntax to remove column(s) from the table, we can remove one or more than one column from a table at a time.
 
ALTER TABLE <TabName>
DROP COLUMN <col1>[,<col2>,....,<coln>]
 
General Syntax to increase or decrease or change the datatype of the column.
 
The following syntax can be used to change the datatype of a column as well as to increase or decrease the width of a column.
 
ALTER TABLE <TabName>
ALTER COLUMN <ColName> <NewDataType>/<NewSize>
  1. CREATE TABLE stud  
  2. (rno int,  
  3. sname varchar(10))  
  4. --Adding a new column to the table when there is no data in the table.  
  5. SELECT * FROM stud  
  6. --Adding a new column gender to the above table tab1  
  7. ALTER TABLE stud  
  8. ADD gen char(1)  
  9. SELECT * FROM stud  
  10. --Adding more than one column to the table tab1  
  11. ALTER TABLE stud  
  12. ADD age numeric(2,0),email varchar(200)  
  13. SELECT * FROM stud  
  14. DROP TABLE stud  
  15. --Adding column(s) to the table when there is data.....  
  16. CREATE TABLE stud  
  17. (rno int,  
  18. sna varchar(10))  
  19. INSERT INTO stud VALUES(1,'John'),(2,'Sam')  
  20. SELECT * FROM stud  
  21. --Adding a new column to the table stud when there is data in the table  
  22. ALTER TABLE stud  
  23. ADD gen char(1)  
  24. SELECT * FROM stud  
  25. INSERT INTO stud VALUES(3,'Smith','M'),(4,'Kamal','M')  
  26. SELECT * FROM stud  
  27. --Adding more than one column when there is data in the table....  
  28. ALTER TABLE stud  
  29. ADD age numeric(2,0), email_id varchar(200)  
  30. SELECT * FROM stud  
  31. DROP TABLE stud  
  32. --Removing column(s) from the table  
  33. --The following is the scenario where there is no data in the table we are performing these operations..  
  34. CREATE TABLE stud  
  35. (rno int,  
  36. sna varchar(20),  
  37. age numeric(2,0),  
  38. gen char(1),  
  39. email_id varchar(100))  
  40. SELECT * FROM stud  
  41. --Removing one column "sna" from the table stud  
  42. ALTER TABLE stud  
  43. DROP COLUMN sna  
  44. SELECT * FROM stud  
  45. --Removing multiple columns "email_id" and "age" from the table.  
  46. ALTER TABLE tab1  
  47. DROP COLUMN email_id,age  
  48. SELECT * FROM stud  
  49. ALTER TABLE stud  
  50. DROP COLUMN gen  
  51. SELECT * FROM stud  
  52. --When there is only one column in the table and we want to remove that column?  
  53. --ERROR - Why? Because a table to exist in the database it must have at least one column.  
  54. --If we need to remove the last column from the table better drop the table....  
  55. --ERROR - The following command will result in error for the above stated reason  
  56. ALTER TABLE stud  
  57. DROP COLUMN rno  
  58. DROP TABLE stud  
  59. SELECT * FROM stud  
  60. --Following is the scenario for removing column from a table WHEN THERE IS data in the table...  
  61. CREATE TABLE stud  
  62. (rno int,  
  63. sna varchar(20),  
  64. age numeric(2,0),  
  65. gen char(1))  
  66. INSERT INTO stud VALUES(1,'Amit',21,'M'),(2,'James',23,'M'),(3,'Smith',24,'M')  
  67. SELECT * FROM stud  
  68. ALTER TABLE stud  
  69. DROP COLUMN age  
  70. SELECT * FROM stud  
  71. --Removing more than one column "gen" and "rno" from the table when we have data....  
  72. ALTER TABLE stud  
  73. DROP COLUMN gen,rno  
  74. SELECT * FROM stud  
  75. DROP TABLE stud  
  76. SELECT * FROM stud  
  77. --Increasing the width of the column (when there is no data in the column)  
  78. CREATE TABLE stud  
  79. (rno int,  
  80. sna varchar(10),  
  81. age numeric(2,0),  
  82. gen char(1))  
  83. sp_help stud  
  84. SELECT * FROM stud  
  85. --Increasing the width for "gen" column from char(1) to char(6)  
  86. ALTER TABLE stud  
  87. ALTER COLUMN gen char(6)  
  88. sp_help stud  
  89. ALTER TABLE stud  
  90. ALTER COLUMN gen varchar(6)  
  91. sp_help stud  
  92. ALTER TABLE stud  
  93. ALTER COLUMN rno VARCHAR(6)  
  94. Sp_help tab1  
  95. --Reducing the width of the column from 6 character width back to 1 character width  
  96. ALTER TABLE stud  
  97. ALTER COLUMN gen char(1)  
  98. sp_help stud  
  99. --Increasing the width of the column (when there IS DATA in the column)  
  100. DROP TABLE stud  
  101. CREATE TABLE stud  
  102. (rno int,  
  103. sna varchar(10),  
  104. age numeric(2,0),  
  105. gen char(1))  
  106. INSERT INTO stud  
  107. VALUES(1,'Amit',21,'M'),(2,'Sunil',25,'M')  
  108. SELECT * FROM stud  
  109. sp_help stud  
  110. --ERROR - Because the column width is of 1 character and cannot store more than 1 character.  
  111. UPDATE stud  
  112. SET gen='Male'  
  113. WHERE gen='M'  
  114. sp_help stud  
  115. --Increasing the width of the column when there is data in the table.  
  116. ALTER TABLE stud  
  117. ALTER COLUMN gen varchar(6)  
  118. SELECT * FROM stud  
  119. sp_help stud  
  120. UPDATE stud  
  121. SET gen='Male'  
  122. WHERE gen='M'  
  123. SELECT * FROM stud  
  124. sp_help stud  
  125. INSERT INTO stud VALUES(5,'Reema',22,'Female')  
  126. SELECT * FROM stud  
  127. --Decreasing the width of the column gen back to char(1) when there is data in the table.  
  128. --Please note that when we have data in the column of a table we cannot reduce the size less than  
  129. --Maximum data occupying data.  
  130. --ERROR  
  131. ALTER TABLE stud  
  132. ALTER COLUMN gen char(1)  
  133. --In order to reduce the column width from char(6) to char(1) we better modify the contents of data  
  134. UPDATE stud  
  135. SET gen='M'  
  136. WHERE gen='male'  
  137. SELECT * FROM stud  
  138. UPDATE stud  
  139. SET gen='F'  
  140. WHERE gen='female'  
  141. SELECT * FROM stud  
  142. sp_help stud  
  143. --Now the following command works as we have reduced the content of data in the column that is from Male to M and Female to F  
  144. ALTER TABLE stud  
  145. ALTER COLUMN gen char(1)  
  146. sp_help stud  
In this blog, which is Part 1 of ALTER table structure, we have discussed adding a new column, removing unwanted column(s), and increasing as well as decreasing the width of the column in a table. In the next blog which will be Part 2, a continuation of this blog. We will cover the topics related to altering table structure (i.e. adding constraints, removing constraints, renaming the column of the table and renaming the table).
Next Recommended Reading Alter Table Columns And Datatypes Without Deleting The Table