How To Rename a column which is foreign key in another table?

We can not rename this type of column as normal columns.To rename these type of columns first

 we have to drop the foreign key,                                   
        then rename the column,
        now add the foreign key.

How to rename a normal column in mysql?

This is simple query to rename a column in mysql
                                                                                                       

ALTER TABLE  table_name CHANGE  old_column_name  new_column_name datatype;

How to backup only database schema without data in mysql?

Here is the command to backup only schema without data


                                                                                                               

 >mysqldump -u root -p****  --no-data database_name > filename.sql

How to delete foreign key from a table in mysql?

The following command drops foreign KEY Constraint from table in mysql.

                       
                                                                 

 alter table table_name drop foreign key foreignkey_name;
Eg:
 alter table MerchantOfferImage drop foreign key Merchant_ibfk_1;

How to add Foreign Key to existing table column in mysql?

It is simple to add a foreign key to a column in Existing table.The Following command adds foreign key to the table

alter table table_name add CONSTRAINT foreign_key_constraint_name FOREIGN KEY (column_name)  REFERENCES reference_table_name (reference_table_column_name) ON UPDATE CASCADE ON DELETE CASCADE;  
                       
Eg:    
alter table MerchantOfferImage add CONSTRAINT banner_image_id_fk FOREIGN KEY (banner_image_id)  REFERENCES MerchantOffer (offer_id) ON UPDATE CASCADE ON DELETE CASCADE;
                                                                   or
alter table MerchantOfferImage add foreign key(offer_id) references MerchantOffer (offer_id)  ON UPDATE CASCADE ON DELETE CASCADE;

How to remove primary key Constraint in mysql?

.

We should remove the autoincrement property before dropping the key

ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

Eg: 
ALTER TABLE User MODIFY id int NOT NULL;

Then delete the primary key Constraint            

ALTER TABLE table_name DROP PRIMARY KEY;                      
Eg: 
ALTER TABLE User DROP PRIMARY KEY;

Example on Transaction Management with Spring+Hibernate+Mysql

In this post i would like to discuss about Spring Tx.Magmt with Annotations with "Spring + Hibernate + Mysql "
In this Example i take

Demo(I)                                                                                  

DemoBean(c)
DemoClient(c)
Spring.xml(Spring configuration file.)
hibernate.cfg.xml(Hibernate configuration file.)
Employee.hbm.xml(Hibernate Mapping File.)

Demo:

package p1;

public interface Demo {
 public void bm1()throws RuntimeException,Exception;
}

DemoBean:

package p1;

public class DemoBean implements Demo{
private SessionFactory sf;

/**
* @param sf the sf to set
*/
public void setSf(SessionFactory sf) {
this.sf = sf;
}

@Override
@Transactional(propagation=Propagation.REQUIRED)
public void bm1()throws Exception {

Session session=sf.getCurrentSession();
int r1=session.createQuery("UPDATE Employee set salary=1 where sno=6").executeUpdate();
int r2=session.createQuery("UPDATE Employee set name='kkk' where sno=57").executeUpdate();

if(r1==0 || r2==0){
System.out.println("Tx is rolling back.");
throw new RuntimeException();
}else{
System.out.println("Tx is Committed");
}
}
}

Spring.xml:

<beans...
     .....>
    <tx:annotation-driven transaction-manager="hbt"/>

    <bean id="mySessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
             <property name="configLocation" value="hibernate.cfg.xml"/>
             <property name="dataSource" ref="drds" />
    </bean>
 
    <bean id="drds" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/TEST"/>
        <property name="username" value="username"/>
        <property name="password" value="password"/>
    </bean>
 
    <bean id="hbt" class="org.springframework.orm.hibernate4.HibernateTransactionManager">
        <property name="sessionFactory" ref="mySessionFactory"/>
    </bean>
   
    <bean id="db" class="p1.DemoBean">
         <property name="sf" ref="mySessionFactory"/>
     </bean>
</beans>

hibernate.cfg.xml

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>

<property name="hibernate.connection.url">jdbc:mysql://localhost:3306/databasename</property>
<property name="hibernate.connection.username">username</property>
<property name="hibernate.connection.password">password</property>
<property name="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</property>
<property name="hibernate.connection.autocommit">false</property>
<property name="hibernate.show_sql">true</property>
<mapping resource="Employee.hbm.xml" />
</session-factory>
</hibernate-configuration>

Employee.hbm.xml:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
    <class name="p1.Employee" table="Employee" catalog="database name">
        <id name="sno" type="java.lang.Integer">
            <column name="sno" />
            <generator class="identity" />
        </id>
        <property name="name" type="string">
            <column name="name" length="30" not-null="true" />
        </property>
        <property name="salary" type="float">
            <column name="salary" precision="8" not-null="true" />
        </property>
        <property name="eid" type="int">
            <column name="eid" not-null="true" />
        </property>
    </class>
</hibernate-mapping>

DemoClient:

package p1;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class DemoClient {
public static void main(String s[]){
ApplicationContext ctx=new ClassPathXmlApplicationContext("Spring.cfg.xml");
Demo d=(Demo)ctx.getBean("db");
try {
d.bm1();
} catch (RuntimeException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
}

Run it.