In this section we will discuss how can we remove a foreign key from a table.
MySQL ALTER DROP FOREIGN KEY using Java Servlet
In this section we will discuss how can we remove a foreign key from a table.
FOREIGN key is used in a table to give reference of primary key column of a one table to the same column in another table. Once a foreign key/constraint is created in a table it can be removed also. In the further lines you will learn about how can you remove a foreign key. So, lets first start with database system, since we are going to learn about how to remove a foreign key in mysql I assumed here that you are aware with how to create a table and how a foreign key can be applied into the table (for help to apply foreign key in to the existing table Click Here. Since we have to use the query into our java servlet program so, I had made two tables earlier were named 'employee(Id, Name, Department, Salary, DepartmentId)' and 'department(DepartmentId, Department)' in the database 'data' in mysql, make a field DepartmentId of table 'employee' as foreign key which is a primary key of table 'department'. Made a foreign key by the query "ALTER TABLE employee ADD CONSTRAINT fk_PerEmp FOREIGN KEY (DepartmentId) REFERENCES Department(DepartmentId)". Then as per the example about we are discussing I used the query "ALTER TABLE employee DROP FOREIGN KEY fk_PerEmp" to remove a foreign key. Sometimes mysql makes an index for their keys so for remove the keys permanently with their index can use the query "ALTER TABLE employee DROP INDEX fk_PerEmp". In the example given below I used the both. Now I made a servlet class in java named 'AlterDropForeignKeyServlet' 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 query1 "ALTER TABLE employee DROP FOREIGN KEY fk_PerEmp" for removing the foreign key and query2 "ALTER TABLE employee DROP INDEX fk_PerEmp" for removing the index 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 :
AlterDropForeignKeyServlet.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 AlterDropForeignKeyServlet 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 sql1 = "ALTER TABLE employee DROP FOREIGN KEY fk_PerEmp";
String sql2 = "alter table employee drop index fk_PerEmp";
try
{
Class.forName(driver);
Connection con = DriverManager.getConnection(url,uid,psw);
PreparedStatement ps1 = con.prepareStatement(sql1);
int i1 = ps1.executeUpdate();
pw.println(i1+ " row(s) affected 'foreign' key removed <br>");
PreparedStatement ps2 = con.prepareStatement(sql2);
int i2 = ps2.executeUpdate();
pw.println(i2+ " row(s) affected 'foreign' key index removed ");
}
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>AlterDropForeignKeyServlet </servlet-name> <servlet-class>AlterDropForeignKeyServlet </servlet-class> </servlet> <servlet-mapping> <servlet-name>AlterDropForeignKeyServlet </servlet-name> <url-pattern>/AlterDropForeignKeyServlet </url-pattern> </servlet-mapping> </web-app>
Output :
Tables with the primary key and foreign key that I had made earlier are as follows
1. description of table employee
2. description of table department
3. When you will execute the above example you will get the output as
4. And after executing the java program successfully when you will go to look your table the description of your table will look as
[ 0 ] Comments