Description:
Dear Members ,
Step 1: I created an table with an column namely ID having DEFAULT value of 5.
Step 2: I altered same table modify column ID from null to not nullable.
But after step 2 default value I have set in create table (in step 1) is reset to 0. It seems that modify column of alter table requires entire column defintion even if column defintion given during create table is not modified.
My view is when I modify particular column I will think over the my constraint only I don't think of redefining the entire column definition again,
If I am wrong kindly correct me,
Below is the list of sql commands and result I have got while I observed this behaviour,
mysql> create table MTEST(ID INTEGER DEFAULT 5 , SALARY INTEGER) type =InnoDB;
Query OK, 0 rows affected (0.20 sec)
mysql> insert into MTEST (SALARY) values(10000);
Query OK, 1 row affected (0.00 sec)
mysql> select * from MTEST;
+------+--------+
| ID | SALARY |
+------+--------+
| 5 | 10000 |
+------+--------+
1 row in set (0.00 sec)
mysql> desc MTEST;
+--------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------+------+-------+---------+-------+
| ID | int(11) | YES | | 5 | |
| SALARY | int(11) | YES | | NULL | |
+--------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> alter table MTEST MODIFY COLUMN ID INTEGER NOT NULL;
Query OK, 1 row affected (0.21 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into MTEST (SALARY) values(20000);
Query OK, 1 row affected (0.00 sec)
mysql> select * from MTEST;
+----+--------+
| ID | SALARY |
+----+--------+
| 5 | 10000 |
| 0 | 20000 |
+----+--------+
2 rows in set (0.00 sec)
mysql> desc MTEST;
+----------------+---------+--------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------+--------+------+---------+-------+
| ID | int(11) | | | 0 | |
| SALARY | int(11) | YES | | NULL| |
+---------------+---------+--------+------+---------+-------+
2 rows in set (0.00 sec)
How to repeat:
created table with an column namely ID having DEFAULT value of 5.
Then altered same table by modifying column ID from null to not nullable.
check for table description
