Mysql Alter FOREIGN key using java servlet example

In this section we will discuss how can you create a foreign key constraint when a table is existed already.

Mysql Alter FOREIGN key using java servlet example

In this section we will discuss how can you create a foreign key constraint when a table is existed already.

A FOREIGN key is a reference of a primary key field of one table in the another table. Adding of Foreign key constraint is useful because of it prevents those actions by which the links could be destroyed between tables. Now let's come to the point that we have to apply this query in our java program. So at first I have created a table in mysql named 'employee' into which I made the fields Id, Name, Department, Salary, DepartmentId into which I make a field 'Id' = primary key. And the other table named 'Department' with the fields DepartmentId and Department into which make a field 'DepartmentId' = primary key. Now I made a servlet class named AlterForeignKeyServlet which extends the HttpServlet class. In the body of class I overridden the method doGet() and created an objects of HttpServletRequest and HttpServletResponse into its parameter. Inside the doGet() method first I set the mime type that in which format the browser will show the output. In the next line I used the getWriter() method of ServletResponse interface with the object of HttpServletResponse. HttpServletResponse interface extends this method from the ServletResponse. In continue since we have to use a SQL query so at first we will have to make a connection between java code and database system. I am using here Mysql so in the example given below I am loading the driver of mysql and make a connection by using the getConnection() method of DriverManager class. It returns a Connection. Further I passed the query "ALTER TABLE employee ADD CONSTRAINT fk_PerEmp FOREIGN KEY
(DepartmentId) REFERENCES Department(DepartmentId)" into the parameter of prepareStatement() method of Connection interface which gives an object of PreparedStatement interface. In the next line I used the executeUpdate() method of PreparedStatement interface which returns an integer value of row affected.

Example :

AlterForeignKeyServlet.java

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class AlterForeignKeyServlet extends HttpServlet
{
public void doGet(HttpServletRequest req, HttpServletResponse res)
throws IOException, ServletException
{
res.setContentType("text/html");
PrintWriter pw = res.getWriter();
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://192.168.10.13/data";
String uid = "root";
String psw = "root";
String sql = "ALTER TABLE employee ADD CONSTRAINT fk_PerEmp FOREIGN KEY 
(DepartmentId) REFERENCES Department(DepartmentId)";
//or can be used 
// ALTER TABLE employee ADD FOREIGN KEY (DepartmentId) REFERENCES 
//Department(DepartmentId)
try
{
Class.forName(driver);
Connection con = DriverManager.getConnection(url,uid,psw);
PreparedStatement ps = con.prepareStatement(sql);
int i = ps.executeUpdate();
pw.println(i+" row(s) affected");
}
catch(SQLException sx)
{
pw.println(sx);
}
catch(ClassNotFoundException cx)
{
pw.println(cx);
}
}
}

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app id="WebApp_ID" version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
<display-name>join</display-name> 

<servlet>
<servlet-name>AlterForeignKeyServlet</servlet-name>
<servlet-class>AlterForeignKeyServlet</servlet-class>
</servlet>

<servlet-mapping>
<servlet-name>AlterForeignKeyServlet</servlet-name>
<url-pattern>/AlterForeignKeyServlet</url-pattern>
</servlet-mapping>

</web-app>

Output :

1. Description of the table 'employee' when I had first created it without making foreign key

2. Description of the table 'Department' when I had first created it.

3. When you will execute the above example you will get the output as

4. After executing the above example when you will see the description of table 'employee' you will find the change in the column 'key' of field 'DepartmentId' as

Download Source Code