The ALTER TABLE proclamation, permits you to modify a current table. Utilizing this you can do the accompanying −
- Add a section, add a requirement
- Drop a section, drop a requirement
- Change the column level locking of a table
Allow us to expect we have made a table named Employees as demonstrated underneath −
ij> CREATE TABLE Employees (
Id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
Name VARCHAR(255),
Salary INT NOT NULL,
Location VARCHAR(255),
PRIMARY KEY (Id)
);
Also, embedded four records utilizing the supplement articulation as −
ij> INSERT INTO Employees (Name, Salary, Location) VALUES
('Amit', 30000, 'Hyderabad'),
('Kalyan', 40000, 'Vishakhapatnam'),
('Renuka', 50000, 'Delhi'),
('Archana', 15000, 'Mumbai');
Following is the linguistic structure to add a section to a table utilizing ALTER proclamation.
ALTER TABLE table_name ADD COLUMN column_name column_type;
Example
Utilizing ALTER articulation, we are attempting to add another section named Age with the sort whole number.
ALTER TABLE Employees ADD COLUMN Age INT;
0 rows inserted/updated/deleted
Add another section named Phone_No with the sort whole number.
ALTER TABLE Employees ADD COLUMN Phone_No BIGINT;
0 rows inserted/updated/deleted
The DESCRIBE order portrays determined table by posting the sections and their subtleties, if the table exists. On the off chance that you DESCRIBE, the table Employees you can notice the recently added segments as demonstrated underneath −
ij> DESCRIBE Employees;
COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
------------------------------------------------------------------------------
ID |INTEGER |0 |10 |10 |AUTOINCRE&|NULL |NO
NAME |VARCHAR |NULL|NULL|255 |NULL |510 |YES
SALARY |INTEGER |0 |10 |10 |NULL |NULL |NO
LOCATION |VARCHAR |NULL|NULL|255 |NULL |510 |YES
AGE |INTEGER |0 |10 |10 |NULL |NULL |YES
PHONE_NO |INTEGER |0 |10 |10 |NULL |NULL |YES
6 rows selected
Adding a constraint to a table
Following is the punctuation to add a requirement to a section of a table utilizing ALTER explanation.
ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint (column_name);
Where requirement can be NOT NULL, NULL, PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK.
Example
Utilizing ALTER articulation, we are attempting to add imperative UNIQUE to the Phone_No segment.
ij> ALTER TABLE Employees ADD CONSTRAINT New_Constraint UNIQUE(Phone_No);
0 rows inserted/updated/deleted
Once, you add a UNIQUE imperative to a segment, it can't have similar qualities for two lines, i.e., telephone number ought to be novel for every representative.
On the off chance that you attempt to add two segments with an equivalent telephone number, you will get a special case as demonstrated beneath.
ij> INSERT INTO Employees (Name, Salary, Location, Age, Phone_No) VALUES
('Amit', 30000, 'Hyderabad', 30, 9848022338);
1 row inserted/updated/deleted
ij> INSERT INTO Employees (Name, Salary, Location, Age, Phone_No) VALUES
('Sumit', 35000, 'Chennai', 25, 9848022338);
ERROR 23505: The statement was aborted because it would have caused a duplicate
key value in a unique or primary key constraint or unique index identified by
'NEW_CONSTRAINT' defined on 'EMPLOYEES'.
Following is the punctuation to drop a requirement of a section −
ALTER TABLE table_name DROP CONSTRAINT constraint_name;
Example
The accompanying inquiry erases the requirement name New_Constraint on the section Phone_No made previously.
ij> ALTER TABLE Employees DROP CONSTRAINT New_Constraint;
0 rows inserted/updated/deleted
Since we have eliminated the UNIQUE requirement on the section Phone_No, you can add segments with a similar telephone number.
ij> INSERT INTO Employees (Name, Salary, Location, Age, Phone_No) VALUES
('Sumit', 35000, 'Chennai', 25, 9848022338);
1 row inserted/updated/deleted
You can check the substance of the table ij> select * from Employees as follows −
ID |NAME |SALARY |LOCATION |AGE |PHONE_NO
-------------------------------------------------------------------------
1 |Amit |30000 |Hyderabad |30 |9848022338
2 |Sumit |35000 |Chennai |25 |9848022338
2 rows selected
Dropping a column from a table
Following is the linguistic structure to drop a section of a segment.
ALTER TABLE table_name DROP COLUMN column_name;
Example
Following question erases the section named age of the representative −
ij> ALTER TABLE Employees DROP COLUMN Age;
0 rows inserted/updated/deleted
On the off chance that you portray the table, you can see just 4 segments.
ij> DESCRIBE Employees;
COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
------------------------------------------------------------------------------
ID |INTEGER |0 |10 |10 |AUTOINCRE&|NULL |NO
NAME |VARCHAR |NULL|NULL|255 |NULL |510 |YES
SALARY |INTEGER |0 |10 |10 |NULL |NULL |NO
LOCATION |VARCHAR |NULL|NULL|255 |NULL |510 |YES
PHONE_NO |BIGINT |0 |10 |19 |NULL |NULL |YES
Altering table using JDBC program
Following is the JDBC program to change a table utilizing the ALTER question −
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class AlterTableExample {
public static void main(String args[]) throws Exception {
//Registering the driver
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
//Getting the Connection object
String URL = "jdbc:derby:sampleDB;create=true";
Connection conn = DriverManager.getConnection(URL);
//Creating the Statement object
Statement stmt = conn.createStatement();
//Executing the query
String createQuery = "CREATE TABLE Employees( "
+ "Id INT NOT NULL GENERATED ALWAYS AS IDENTITY, "
+ "Name VARCHAR(255), "
+ "Salary INT NOT NULL, "
+ "Location VARCHAR(255), "
+ "PRIMARY KEY (Id))";
stmt.execute(createQuery);
System.out.println("Table created");
System.out.println(" ");
//Executing the query
String insertQuery = "INSERT INTO Employees("
+ "Name, Salary, Location) VALUES "
+ "('Amit', 30000, 'Hyderabad'), "
+ "('Kalyan', 40000, 'Vishakhapatnam'), "
+ "('Renuka', 50000, 'Delhi'), "
+ "('Archana', 15000, 'Mumbai'), "
+ "('Trupti', 45000, 'Kochin')";
stmt.execute(insertQuery);
System.out.println("Values inserted");
System.out.println(" ");
//Executing the query
String selectQuery = "SELECT * FROM Employees";
ResultSet rs = stmt.executeQuery(selectQuery);
System.out.println("Contents of the table after inserting the table");
while(rs.next()) {
System.out.println("Id: "+rs.getString("Id"));
System.out.println("Name: "+rs.getString("Name"));
System.out.println("Salary: "+rs.getString("Salary"));
System.out.println("Location: "+rs.getString("Location"));
}
System.out.println(" ");
//Altering the table
stmt.execute("ALTER TABLE Employees ADD COLUMN Age INT");
stmt.execute("ALTER TABLE Employees ADD COLUMN Phone_No BigINT");
stmt.execute("ALTER TABLE Employees " + "ADD CONSTRAINT New_Constraint UNIQUE(Phone_No)");
stmt.execute("INSERT INTO Employees "
+ "(Name, Salary, Location, Age, Phone_No) "
+ "VALUES ('Amit', 30000, 'Hyderabad', 30, 9848022338)");
ResultSet alterResult = stmt.executeQuery("Select * from Employees");
System.out.println("Contents of the table after altering "
+ "the table and inserting values to it: ");
while(alterResult.next()) {
System.out.println("Id: "+alterResult.getString("Id"));
System.out.println("Name: "+alterResult.getString("Name"));
System.out.println("Salary: "+alterResult.getString("Salary"));
System.out.println("Location: "+alterResult.getString("Location"));
System.out.println("Age: "+alterResult.getString("Age"));
System.out.println("Phone_No: "+alterResult.getString("Phone_No"));
}
}
}
Output
On executing the above program, the accompanying yield will be produced −
Table created
Values inserted
Contents of the table after inserting the table
Id: 1
Name: Amit
Salary: 30000
Location: Hyderabad
Id: 2
Name: Kalyan
Salary: 40000
Location: Vishakhapatnam
Id: 3
Name: Renuka
Salary: 50000
Location: Delhi
Id: 4
Name: Archana
Salary: 15000
Location: Mumbai
Id: 5
Name: Trupti
Salary: 45000
Location: Kochin
Contents of the table after altering the table and inserting values to it:
Id: 1
Name: Amit
Salary: 30000
Location: Hyderabad
Age: null
Phone_No: null
Id: 2
Name: Kalyan
Salary: 40000
Location: Vishakhapatnam
Age: null
Phone_No: null
Id: 3
Name: Renuka
Salary: 50000
Location: Delhi
Age: null
Phone_No: null
Id: 4
Name: Archana
Salary: 15000
Location: Mumbai
Age: null
Phone_No: null
Id: 5
Name: Trupti
Salary: 45000
Location: Kochin
Age: null
Phone_No: null
Id: 6
Name: Amit
Salary: 30000
Location: Hyderabad
Age: 30
Phone_No: 9848022338