Blog

  • Many people face problems when they use MySQL queries in existing table fields. In this blog, we will learn about MySQL queries which will use existing table fields.
     Here are following queries-

    First, we create a table using the following query-

     CREATE TABLE candidate_info(  
       id INT NOT NULL AUTO_INCREMENT,  
       ctc VARCHAR(255),  
       date_of_joining DATETIME,
       full_name VARCHAR(255),
       is_deleted BIT(1) NOT NULL,  
       PRIMARY KEY ( id )  
    );  
    

    (1):- How to set DEFAULT NULL on existing table fields?

    Syntax:- ALTER TABLE table_name MODIFY column_name<data_type> DEFAULT NULL;
    Example:- ALTER TABLE candidate_info modify is_deleted BIT(1) DEFAULT NULL;
    

    (2):- How to change the column name of the existing table?

    Note:- (i) This query can only be executed when old_column_name and new_column_name data type is same.
           (ii) In case, you want to execute the same query with different data types, the table should be empty.

    Syntax:- ALTER TABLE table_name CHANGE COLUMN old_column_name new_column_name<data_type>;
    Example:- ALTER TABLE candidate_info CHANGE COLUMN date_of_joining last_followed_up_on DATETIME;
    

    (3):- How to add a new column in the existing table?

    Syntax:- ALTER TABLE table_name ADD column_name<data_type>;
    Example:- ALTER TABLE candidate_info ADD first_name DATETIME;
    

    (4):- How to drop a column from the existing table?

    Syntax:- ALTER TABLE table_name DROP COLUMN column_name;
    Example:- ALTER TABLE candidate_info DROP COLUMN full_Name;
    

    (5):- How to update the column value of the existing table?

    Syntax:- UPDATE table_name SET column_name = 'new_value' WHERE column_name = 'old_value';
    Example:- UPDATE candidate_info SET full_name = 'Rohit Kumar' WHERE full_name = 'Mohit Kumar';
    

    (6):- How to delete foreign key constraint from the existing table?

    Note:- Before deleting the foreign key constraint from the existing table, We can see all foreign key constraint of the table using the following query.

    Syntax:- SHOW CREATE TABLE table_name;
    Example:- SHOW CREATE TABLE demand;
    

    Foreign key constraint as follow-

    CREATE TABLE `demand` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `version` bigint(20) NOT NULL,
      `date_created` datetime NOT NULL,
      `number_of_openings` int(11) NOT NULL,
      `recruiter_id` bigint(20) DEFAULT NULL,
      `remarks` varchar(255) DEFAULT NULL,
      `team_lead_id` bigint(20) NOT NULL,
      `project_obj_id` bigint(20) DEFAULT NULL,
      `grades` varchar(255) DEFAULT NULL,
      `cost_of_joinee` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `FK_n7ekk8b7yxkumjppllpi5vnt4` (`recruiter_id`),
      KEY `FK_9nmork72np3xr56jogw01vaqj` (`team_lead_id`),
      KEY `FK_mh9avuc3x9211jhu5jc91yu3s` (`project_obj_id`),
      CONSTRAINT `FK_9nmork72np3xr56jogw01vaqj` FOREIGN KEY (`team_lead_id`) REFERENCES `user` (`id`),
      CONSTRAINT `FK_mh9avuc3x9211jhu5jc91yu3s` FOREIGN KEY (`project_obj_id`) REFERENCES `project` (`id`),
      CONSTRAINT `FK_n7ekk8b7yxkumjppllpi5vnt4` FOREIGN KEY (`recruiter_id`) REFERENCES `user` (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=300 DEFAULT CHARSET=latin1 
    

    Above this line, you can see foreign key constraint, like this - FK_9nmork72np3xr56jogw01vaqj

    Now we can drop the foreign keys of demand table by using the following statement:

    Syntax:- ALTER TABLE table_name DROP FOREIGN KEY foreign_key_constraint_name;
    Example:- ALTER TABLE demand DROP FOREIGN KEY FK_9nmork72np3xr56jogw01vaqj;
    

    Conclusion:-
              In this article, we have covered all basic sql queries, which are used in existing tables.

Tags: mysql

Mobile Applications

Video Content

Bigdata & NoSQL

SaaS Applications

Miscellaneous

Archives


Alexa Certified Site Stats for www.oodlestechnologies.com