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.


 
Ads

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)

Advertisements

Share on Google+Share on Google+

Mysql Alter Not Null

Posted on: January 16, 2009 If you enjoyed this post then why not add us on Google+? Add us to your Circles

Advertisements

 

Discuss: Mysql Alter Not Null   View All Comments

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments:1
Chris Kavanagh
January 28, 2012
SQL MODIFY

Thank you so much for this website! I found your examples to very easy to follow, and well thought out. keep up the good work!!

Ads

 

Ads