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