SQL Query To Allow Nulls

Sometime, when we create the tables in database we do not allow null for a field value, but after some data is inserted into table, we realize the null should be be allowed to be inserted in a that field. In such case, we do not need to delete the table completely. We can alter the table to allow null values for a particular field.

Assume there exist a table with id(int) and name(varchar) attribtues where name do not receive NULL values. Such table was created using SQL query:

CREATE TABLE `university_db`.`students` (
  `id` INT NOT NULL,
  `name` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id`));

Where NOT NULL indicates, name's value should not be null for any record. We can use below query to alter the table design such that it allows NULL to be inserted into the name field:

ALTER TABLE `university_db`.`students` 
CHANGE COLUMN `name` `name` VARCHAR(45) NULL DEFAULT NULL ;

"NULL DEFAULT NULL" and end of the query means:
NULL is allowed for field name
and default value is also NULL

Comments