Syntax for SQL ALTER TABLE Statement
Add Column
ALTER TABLE table_name
ADD column_name datatype;
DROP COLUMN
ALTER TABLE table_name
DROP COLUMN column_name;
MODIFY COLUMN
ALTER TABLE table_name
ALTER COLUMN column_name datatype;
ADD UNIQUE CONSTRAINT
ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);
ADD PRIMARY KEY
ALTER TABLE table_name
ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);
Lets Learn.
1.Create database and table.
Student table.
StudentID | StudentName | Address | Country | Section | Branch | Percentage | CourseID |
---|---|---|---|---|---|---|---|
1 | Liam | 23 Andrew road | USA | A | ISE | 66 | 5 |
2 | Andrew | 2415 Anthony market str | Germany | A | ISE | 80 | 6 |
4 | Ryan | 18 Christopher sq. | INDIA | B | EE | 67 | 5 |
5 | Charles | 878 Eli Berguvsvägen 8 | UK | C | ECE | 52 | 8 |
Example (1)
ALTER TABLE student
ADD DOB date;
for above student table add new column as DOB
Example (2)
ALTER TABLE student
DROP COLUMN DOB
dropped or deleted column DOB from student table
2.Complete code of SQL ALTER TABLE Statement in PHP script.
<!DOCTYPE html>
<html lang="en">
<head>
<title>SQL ALTER TABLE Statement</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<br>
<br>
<br>
<div class="text-center">
<h1>SQL ALTER TABLE Statement</h1>
</div>
<br>
<br>
<br>
<div class="well">
<?php
$connect = mysqli_connect("localhost", "root", "", "school_management");
if($connect === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}
$sql = "ALTER TABLE student ADD DOB date";
if(mysqli_query($connect, $sql)){
echo "<br><h1 class='text-success'>table alert successfully.</h1>";
} else{
echo "ERROR: Could not able to execute $sql. " . mysqli_error($connect);
}
mysqli_close($connect);
?>
</div>
<br>
</div>
</body>
</html>