Maduranga's Blogs: MySql

If you have explicitly stated the name of a constraint in a database, you can easily drop the constraint using the name specified. But if you haven't specified a name for a constraint at the time of the construction of the table, database engine will assign a name automatically and you will have to find the constraint name from the system tables of the database.

From this post we are going to see how to find the name of a foreign key constraint and drop it in a single script for different database systems.

1. H2

CREATE ALIAS IF NOT EXISTS DROP_FK AS $$ void executeSql(Connection conn, String sql)
throws SQLException { conn.createStatement().executeUpdate(sql); } $$;

call drop_fk('ALTER TABLE TABLE_NAME DROP CONSTRAINT ' ||
(SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINTS 
WHERE TABLE_NAME = 'TABLE_NAME' AND COLUMN_LIST  = 'COLUMN_NAME'));

DROP ALIAS IF EXISTS DROP_FK;

2. Microsoft SQL Server

DECLARE @COMMAND NVARCHAR(200);

SELECT @COMMAND= 'ALTER TABLE TABLE_NAME DROP CONSTRAINT ' + RC.CONSTRAINT_NAME + ';'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KF ON RC.CONSTRAINT_NAME = KF.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KP ON RC.UNIQUE_CONSTRAINT_NAME = KP.CONSTRAINT_NAME
WHERE KF.TABLE_NAME = 'TABLE_NAME';

EXEC (@COMMAND);

3. MySQL

SET @databasename = "sample";

SELECT CONCAT("ALTER TABLE TABLE_NAME DROP FOREIGN KEY ",constraint_name)
INTO @sqlst
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where TABLE_SCHEMA = @databasename and TABLE_NAME = "TABLE_NAME"
and referenced_column_name is not NULL limit 1;

PREPARE stmt FROM @sqlst;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @sqlstr = NULL;

4. Oracle

declare
  con_name varchar2(100);
  command varchar2(200);
  databasename VARCHAR2(100);
BEGIN
select constraint_name into con_name from all_constraints where table_name='TABLE_NAME' AND owner=databasename AND 
constraint_type = 'R';

    if TRIM(con_name) is not null
    then
      command := 'ALTER TABLE TABLE_NAME DROP CONSTRAINT ' || con_name;
      dbms_output.Put_line(command);
      execute immediate command;
    end if;

    exception
    when NO_DATA_FOUND
    then
    dbms_output.Put_line('Foreign key not found');
END;

5. PostgreSQL

DO $$ DECLARE con_name varchar(200); 
BEGIN 

SELECT 'ALTER TABLE table_name DROP CONSTRAINT ' || tc .constraint_name || ';' INTO con_name 
FROM information_schema.table_constraints AS tc 
JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name 
JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name 
WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name = 'table_name' AND kcu.column_name = 'column_name';
 
EXECUTE con_name; 
END $$;

If you know the basic syntax of the sql, you can customize them according to your requirements.

In any software, if a user is needed to add a database connection details, a test connection button will come in handy. In this blog, I'm trying to test a database connection using a JDBC driver. First we need to download a driver. From the bellow link you can download the MySql driver.

http://dev.mysql.com/downloads/connector/j/

When we are working with the driver, first it need to be added to the CLASSPATH. It can be done as described in,
http://dev.mysql.com/doc/connector-j/en/connector-j-installing.html

For just test purposes, adding it to the build path of the IDE will be sufficient.

If we need to do some operation on the database using the driver, first we need to create a connection to the database. For this you will need driver name (for MySql, driver name is com.mysql.jdbc.Driver ), database url (for MySql database running in the localhost, the url will be jdbc:mysql://localhost:3306/db_name ), username and the password. When creating the connection, if the driver couldn't establish a valid connection, it will throw an exception depending on the course. We can easily use it to test the connection.

First we need to create a custom exception.

public class DataSourceException extends Exception {
 /**
  * 
  */
 private static final long serialVersionUID = 1L;

 public DataSourceException(String message) {
  super(message);
 }

 public DataSourceException(String message, Throwable throwable) {
  super(message, throwable);
 }
}

Now we can create a method to get the connection. This method will return a connection to the database. But to test the connection we are not much interested about the connection. We are checking whether it is throwing any exception or not and if it is the error message. Bellow method can be used to get the connection.

public Connection getConnection(String driver, String url, String uname,
   String pwd) throws DataSourceException {
  Connection conn = null;

  try {
   Class.forName(driver).newInstance();
   conn = DriverManager.getConnection(url + "?user=" + uname + "&password=" + pwd);

  } catch (SQLException ex) {
   throw new DataSourceException(
     "Error establishing data source connection: "
       + ex.getMessage(), ex);
  } catch (InstantiationException e) {
   e.printStackTrace();
  } catch (IllegalAccessException e) {
   e.printStackTrace();
  } catch (ClassNotFoundException e) {
   throw new DataSourceException("Error establishing data source connection: " + e.getMessage(), e);
  }

  return conn;
 }

Now we want to check if the connection is throwing any exception. If it is not, the connection is healthy. But if it is not, the connection is not valid and we can find the cause from the error message of the exception. test connection method will look like this,

public boolean testConnection(String driver, String url, String uname, String pwd) throws DataSourceException {
  try {
   getConnection(driver, url, uname, pwd);
  } catch (DataSourceException e) {
   throw e;
  }

  return true;
}

So now we can check the validity of the database connection.

String driverName = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/driver_test";

try {
 boolean valiedConnection = testConnection(driverName, url, "maduranga", "maduranga");
 if (valiedConnection) {
  System.out.println("Connection is Healthy");
 }
}catch (DataSourceException e) {
 System.out.println(e.getMessage());
}

The error messages will be shown like bellow.

For a false driver name - Error establishing data source connection: com.mysql.jdbc.Drive
For a false connection url - Error establishing data source connection: Unknown database 'userstor'
For a false username - Error establishing data source connection: Access denied for user 'madurang'@'localhost' (using password: YES)
For a false password - Error establishing data source connection: Access denied for user 'maduranga'@'localhost' (using password: YES)
For correct data - Connection is Healthy

As the error messages are really meaningful, this will help a great deal for the user to add the database connection.

Maduranga's Blogs

Sunday, September 20, 2015

Dropping foreign key constraints from database

1. H2

2. Microsoft SQL Server

3. MySQL

4. Oracle

5. PostgreSQL

Saturday, May 17, 2014

Test Java Database Connection Using JDBC Driver