My SQL Queries/Questions

1. How to rename a column which is foreign key in another table?

We cannot 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.

2. 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;

3. 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

4. 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;

5. 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;

6. 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;