My. SQL column defined with NOT NULL is allowing nulls. What version of mysql is this? What mode are you running in? SELECT @@GLOBAL. SQL_MODE, @@SESSION. SQL_MODE. (This should be run in the context of your application, just in case it is changing it). My. SQL is documented thus: http: //dev. As of My. SQL 5. 0. DEFAULT value, My. SQL determines the default value as follows. Introduction to MySQL NULL values. In MySQL, a NULL value means unknown. A NULL value is different from zero or an empty string ''. MySQL SET NULL in UPDATE. Update If Value Is Not Null Mysql WorkbenchIf the column can take NULL as a value, the column is defined with an explicit DEFAULT NULL clause. This is the same as before 5. If the column cannot take NULL as the value, My. SQL defines the column with no explicit DEFAULT clause. For data entry, if an INSERT or REPLACE statement includes no value for. My. SQL handles the column according to the SQL mode in effect at the time. If strict SQL mode is not enabled, My. SQL sets the column to the implicit default value for the column data type. My own testing fails to duplicate your issuemysql> CREATE TABLE `my_table` (. NOT NULL AUTO_INCREMENT. NOT NULL. - > `follow_up_to` int(1. DEFAULT NULL. - > PRIMARY KEY (`entry_id`). KEY `follow_up_to` (`follow_up_to`). ENGINE=Inno. DB AUTO_INCREMENT=5. DEFAULT CHARSET=latin. Update If Value Is Not Null Mysql CreateBEFORE triggers and NOT NULL columns in MySQL. trigger, setting NOT-NULL value. that sets the ‘c1’ column to NOT NULL and the statement UPDATE t1 SET. MySQL: Select a default value for NULL fields. If you need to do calculations inside the database and can not use Ruby objects you may run into problems when. Query OK, 0 rows affected, 2 warnings (0. INSERT INTO my_table VALUES (NULL, NULL, NULL). ERROR 1. 04. 8 (2. Column 'address' cannot be null. INSERT INTO my_table (follow_up_to) VALUES (NULL). Query OK, 1 row affected, 1 warning (0. SHOW WARNINGS. +- -- -- -- -- +- -- -- -+- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -+. Level | Code | Message |. Warning | 1. 36. 4 | Field 'address' doesn't have a default value |. SELECT * FROM my_table. NULL |. +- -- -- -- -- -+- -- -- -- -- +- -- -- -- -- -- -- -+. The fact the old data had nulls shouldn't matter. The Alter table should have 'truncated' the nulls into empty stringsmysql> SHOW CREATE TABLE my_table\G. Table: my_table. Create Table: CREATE TABLE `my_table` (. NOT NULL AUTO_INCREMENT. NOT NULL. `follow_up_to` int(1. DEFAULT NULL. PRIMARY KEY (`entry_id`). KEY `follow_up_to` (`follow_up_to`). ENGINE=Inno. DB AUTO_INCREMENT=5. DEFAULT CHARSET=latin. ALTER TABLE my_table MODIFY address VARCHAR(5. NULL DEFAULT NULL. Query OK, 1 row affected (0. Records: 1 Duplicates: 0 Warnings: 0. INSERT INTO my_table VALUES (NULL, NULL, NULL), (NULL, NULL, NULL). Query OK, 2 rows affected (0. Records: 2 Duplicates: 0 Warnings: 0. SELECT * FROM my_table. NULL |. | 5. 36 | NULL | NULL |. NULL | NULL |. ALTER TABLE my_table MODIFY address VARCHAR(5. NOT NULL. Query OK, 3 rows affected, 2 warnings (0. Records: 3 Duplicates: 0 Warnings: 2. SHOW WARNINGS. +- -- -- -- -- +- -- -- -+- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -+. Level | Code | Message |. Warning | 1. 26. 5 | Data truncated for column 'address' at row 2 |. Warning | 1. 26. 5 | Data truncated for column 'address' at row 3 |. SELECT * FROM my_table. NULL |. | 5. 36 | | NULL |. NULL |. +- -- -- -- -- -+- -- -- -- -- +- -- -- -- -- -- -- -+. INSERT INTO my_table VALUES (NULL, NULL, NULL), (NULL, NULL, NULL). Query OK, 2 rows affected, 2 warnings (0. Records: 2 Duplicates: 0 Warnings: 2. SELECT * FROM my_table. NULL |. | 5. 36 | | NULL |. NULL |. | 5. 38 | | NULL |. NULL |. +- -- -- -- -- -+- -- -- -- -- +- -- -- -- -- -- -- -+.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
November 2017
Categories |