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).