Question

How do I start to code in mysql to Alter table to modify column

remove not null constraint

First drop the foreign key associated with

Drop the column

Add the column back with new definition

Add the foreign key back

This Is what I wrote so far

alter table employees modify column repotsTo remove null:

Answer 1

1. To remove the NOT NULL contraints, you need to identify the following:
   • Foreign Key Contraint on Table Employees
     Lets assume that the Foreign Key constraint name is "fk",
   • Name of the column and table name of the other table which the foreign key is bound to. (replace <Table Name> and <column_name> with these values in last part, Make sure you keep the () in the line. )
   • Datatype of the column reportsT, use the below code to get the datatype(I am assuming its varchar(255))
     
     

     SELECT DATA_TYPE 
     FROM INFORMATION_SCHEMA.COLUMNS
     WHERE 
          TABLE_NAME = 'employees' AND 
          COLUMN_NAME = 'reportsTo'
     

2. Now, drop the Foreign Key Constraint,

   ALTER TABLE `employees`
           DROP FOREIGN KEY `fk`;
   

3. Now, Drop the Column,
   ALTER TABLE `employees` DROP `reportsTo`;
   
4. Add the column back,
   ALTER TABLE `employees` ADD `reportsTo` varchar(255);
   
5. Add the foreign key back

   ALTER TABLE employees ADD CONSTRAINT fk FOREIGN KEY (reportsTo) REFERENCES <Table Name>(<column_name>);