Mysql Alter Not Null
Mysql Alter Not Null is used to perform when we want records of a table have mandatory values or not to be remain Null.
Understand with Example
The Tutorial illustrate an example from 'Mysql Alter Not Null'. To understand and grasp this example we create a table 'usertable' that have a required fieldnames and datatypes respectively. Each records in the table created with Null values.
Create a table "user_table":
| CREATE TABLE `user_table` ( `username` varchar(20) default NULL, `first_name` varchar(20) default NULL, `last_name` varchar(20) default NULL ) |
Describe table "user_table":
The Describe table is used to show the fieldname, Type, Null, Key ,Default value of table 'user_table'.
| mysql> describe user_table; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | username | varchar(20) | YES | | | | | first_name | varchar(20) | YES | | | | | last_name | varchar(20) | YES | | | | +------------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) |
Query to insert the record in table "user_table":
Now we add the records or rows into the table 'user_table'. The insert into is used to add the records or rows into table 'user_table'.
| insert into user_table (username,first_name) values('vineet','Vineet'); insert into user_table (username,first_name) values('srbh','Sourabh'); |
Query to select all the records of table "user_table":
The Query Select return all the records from user_table.
| mysql> select * from user_table; +----------+------------+-----------+ | username | first_name | last_name | +----------+------------+-----------+ | vineet | Vineet | | | srbh | Sourabh | | +----------+------------+-----------+ 2 rows in set (0.00 sec) |
Query to alter the column last_name to be NOT NULL
The Query Alter is used to change the structure and definition of table 'user_table' and modify the last_name column to be NOT NULL. It is mandatory to place the records value in a column of a table.
| mysql> ALTER TABLE user_table Modify last_name varchar(20) NOT NULL; Query OK, 2 rows affected (0.16 sec) |
Describe "user_table"
| mysql> describe user_table; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | username | varchar(20) | YES | | | | | first_name | varchar(20) | YES | | | | | last_name | varchar(20) | NO | | | | +------------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) |
