Post
Ask Question

Altering table structure using the DDL command ALTER

Introduction

 
This blog post is related to the ALTER command. Whenever we want to change the structure of the table we can use the following commands as mentioned in this blog. This post covers complete syntax and examples related to the ALTER command. 
 

Altering the SQL Table 

 
Whenever we change or execute commands that change/affect the table structure, the table is considered to be altered. A table in the database is considered to be altered when any of the following are applied to the table structure.
  1. When we want to add a new column or columns as part of the table.
  2. When we want to remove an existing column or columns from the table.
  3. When we want to change the size of the column (that is when we want to increase the width or decrease the width of the column).
  4. When we want to change the datatype of the column.
  5. When we want to add a constraint on the column of the table.
  6. When we want to remove an existing constraint on the column of the table.
  7. When we want to change the name of the column of a table.
  8. When we want to change the name of the table.
Let's look at the general Syntax for changing the structure of the table in the above-mentioned scenarios. We must use the DDL command ALTER to change the structure of the table.
 
1. Syntax to add a new column 
 
ALTER TABLE <TableName>
ADD <ColName> <DataType>
 
Syntax to add more than one column 
 
ALTER TABLE <TableName>
ADD <ColName-1> <DataType>[,<ColName-2> <DataType>,.....,<ColName-n> <DataType>]
 
2. Syntax to remove a column from a table 
 
ALTER TABLE <TableName>
DROP COLUMN <ColName> 
 
Syntax to remove more than one column from a table.
 
ALTER TABLE <TableName>
DROP COLUMN <ColName-1>[,<ColName-2>,...,<ColName-n>] 
 
3. Syntax to change the size of the column (Increasing the width or decreasing the width of the column).
 
ALTER TABLE <TableName>
ALTER COLUMN <DataType> <NewSize> 
 
4. Syntax to change the datatype of a column.
 
ALTER TABLE <TableName>
ALTER COLUMN <NewDataType> 
 
5. Syntax to add a constraint on the column.
 
ALTER TABLE <TableName>
ADD CONSTRAINT <Constraint-Name> <ConstraintType>(<ColName-1>[,<colName-2>,...,<ColName-n>]) 
 
6. Syntax to remove a constraint from a table's column.
 
ALTER TABLE <TableName>
DROP CONSTRAINT <Constraint-Name-1>[,<Constraint-Name-2>,...,<Constraint-Name-n>] 
 
7. Syntax to change the name of a column in a table.
 
sp_rename '<TableName>.<ExistingColName>','<NewColName>' 
 
8. Syntax to change the name of the table.
 
sp_rename '<ExistingTableName>','<NewTableName>' 
 
Some sample code...
  1. use samp1  
  2. SELECT * FROM stud  
  3. DROP TABLE stud  
  4. CREATE TABLE stud  
  5. (rno int,  
  6. sname varchar(20))  
  7. --Adding a new column to the table when there is no data in the table  
  8. ALTER TABLE stud  
  9. ADD age numeric(2,0)  
  10. SELECT * FROM stud  
  11. --Adding more than one column to the table when there is no data....  
  12. ALTER TABLE stud  
  13. ADD gen char(1), email varchar(200)  
  14. SELECT * FROM stud  
  15. DROP TABLE stud  
  16. --Looking at a scenario when we want to add column(s) when there is data in the table.  
  17. CREATE TABLE stud  
  18. (rno int,  
  19. sname varchar(20))  
  20. SELECT * FROM stud  
  21. INSERT INTO stud VALUES(1,'Amit'),(2,'Anil')  
  22. SELECT * FROM stud  
  23. ALTER TABLE stud  
  24. ADD gen char(1)  
  25. SELECT * FROM stud  
  26. ALTER TABLE stud  
  27. ADD age numeric(2,0), email varchar(200)  
  28. SELECT * FROM stud  
  29. --Removing column(s) from the table....  
  30. --When we have data. When we have no data also we can remove the columns in the same way.  
  31. ALTER TABLE stud  
  32. DROP COLUMN age  
  33. SELECT * FROM stud  
  34. ALTER TABLE stud  
  35. DROP COLUMN email,sname  
  36. SELECT * FROM stud  
  37. ALTER TABLE stud  
  38. DROP COLUMN gen  
  39. SELECT * FROM stud  
  40. --Error - When there is only one column we cannot remove it. It is better to drop the table.  
  41. ALTER TABLE stud   
  42. DROP COLUMN rno  
  43. DROP TABLE stud  
  44. --Changing the size of the column....  
  45. CREATE TABLE stud  
  46. (rno int,  
  47. sname varchar(10),  
  48. gen char(1))  
  49. INSERT INTO stud VALUES(1,'Amit','M'),(2,'Tina','F')  
  50. --Error - As the column "gen" can store only 1 character.  
  51. INSERT INTO stud VALUES(3,'Harry','Male')  
  52. sp_help stud  
  53. ALTER TABLE stud  
  54. ALTER COLUMN gen varchar(6)  
  55. INSERT INTO stud VALUES(3,'Harry','Male')  
  56. SELECT * FROM stud  
  57. --Error  
  58. ALTER TABLE stud  
  59. ALTER COLUMN gen char(1)  
  60. UPDATE stud SET gen='M' WHERE rno=3  
  61. SELECT * FROM stud  
  62. --Reducing the width of the column from varchar(6) back to char(1)  
  63. ALTER TABLE stud  
  64. ALTER COLUMN gen char(1)  
  65. sp_help stud  
  66. DROP TABLE stud  
  67. --When we want to add a constraint on a column of a table when there is no data. It is easy to remove a constraint on the column of the table whether there is data or not.  
  68. --It is difficult to add a constraint on the column of a table when there is data but, easy to add when there is no data.  
  69. CREATE TABLE stud  
  70. (rno int,  
  71. sname varchar(20))  
  72. sp_help stud  
  73. SELECT * FROM stud  
  74. ALTER TABLE stud  
  75. ADD CONSTRAINT unq1 UNIQUE(rno)  
  76. sp_help stud  
  77. --Removing the constraint   
  78. ALTER TABLE stud  
  79. DROP CONSTRAINT unq1  
  80. sp_help stud  
  81. --Error – Cannot add a PRIMARY KEY constraint on a column which is not defined with a NOT NULL constraint. Therefore first add NOT NULL constraint and then add the Primary Key constraint.  
  82. ALTER TABLE stud  
  83. ADD CONSTRAINT pk1 PRIMARY KEY(rno)  
  84. --Adding NOT NULL to the rno column.  
  85. ALTER TABLE stud  
  86. ALTER COLUMN rno int NOT NULL  
  87. sp_help stud  
  88. ALTER TABLE stud  
  89. ADD CONSTRAINT pk1 PRIMARY KEY(rno)  
  90. SELECT * FROM stud  
  91. ALTER TABLE stud  
  92. ADD age numeric(2,0) NOT NULL  
  93. ALTER TABLE stud  
  94. DROP COLUMN age  
  95. SELECT * FROM stud  
  96. INSERT INTO stud VALUES(1,'Anil')  
  97. SELECT * FROM stud  
  98. --ERROR - When we have data in the table we cannot add NOT NULL we must use DEFAULT constraint also.  
  99. ALTER TABLE stud  
  100. ADD age numeric(2,0) NOT NULL  
  101. ALTER TABLE stud  
  102. ADD age numeric(2,0) NOT NULL DEFAULT 20  
  103. SELECT * FROM stud  
  104. --Error - We cannot drop a column when we have a constraint associated with it.  
  105. ALTER TABLE stud  
  106. DROP COLUMN age  
  107. --Therefore first remove/drop the constraint and then we can drop/remove the column  
  108. ALTER TABLE stud  
  109. DROP CONSTRAINT <DefaultConstraintName>  
  110. ALTER TABLE stud  
  111. DROP COLUMN age  
  112. SELECT * FROM stud  
  113. ALTER TABLE stud  
  114. ADD age numeric(2,0) DEFAULT 20 NOT NULL check(age between 15 and 20)  
  115. sp_help stud  
  116. SELECT * FROM stud  
  117. --Renaming the column of a table....  
  118. SELECT * FROM stud  
  119. sp_rename 'stud.rno','roll_number'  
  120. SELECT * FROM stud  
  121. --Renaming the name of a table...  
  122. sp_rename 'stud','student'  
  123. SELECT * FROM stud  
  124. SELECT * FROM student