I had created a table a while back and started adding data to it. Recently I added a new column (address
) to it with NOT NULL
as part of the new column. The old rows (pre-addition) are still null, which created a warning as part of the definition. However, new rows with the new column are still being allowed to insert nulls.
Is the new column's pre-addition nulls the source of them being allowed? If so, is there a way to tell MySQL to not allow it even though it was before?
mysql> show create table my_table\G
*************************** 1. row ***************************
Table: my_table
Create Table: CREATE TABLE `my_table` (
`entry_id` int(11) NOT NULL auto_increment,
`address` varchar(512) NOT NULL,
`follow_up_to` int(11) default NULL,
PRIMARY KEY (`entry_id`),
KEY `follow_up_to` (`follow_up_to`),
CONSTRAINT `my_table_ibfk_1`
FOREIGN KEY (`follow_up_to`)
REFERENCES `my_table` (`entry_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=535 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql>
Best Answer
What version of mysql is this?
What mode are you running in?
(This should be run in the context of your application, just in case it is changing it).
MySQL is documented thus: http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html
My own testing fails to duplicate your issue
The fact the old data had nulls shouldn't matter. The Alter table should have 'truncated' the nulls into empty strings