Menu
I have a table where default value is not specified at the time of table creation. Now I want to change default value to '' (empty string). When I run alter table query it get success but still on new row insertion table consider NULL as default value if column value is not specified.
TABLE Schema::
CREATE TABLE `table1` (
`col1` INT(11) NOT NULL AUTO_INCREMENT,
`col2` TEXT,
`col3` INT(11) DEFAULT NULL,
`col4` TINYINT(1) DEFAULT '0',
PRIMARY KEY (`id`)
);
ALTER Query::
ALTER TABLE `table1` change `col2` `col2` text default '';
822
A column's default value is part of its definition, but can be modified separately from other aspects of the definition. To change a default value, use ALTER col_name SET DEFAULT : ALTER TABLE mytbl ALTER j SET DEFAULT 1000; Default values must be constants.
You need to use NULLIF() function from MySQL. The syntax is as follows: SELECT NULLIF(yourCoumnName,' ') as anyVariableName from yourTableName; In the above syntax, if you compare empty string( ' ') to empty string( ' '), the result will always be NULL.
For each row in table, a column can contain a value or NULL which indicates "no value." If a column is declared NOT NULL, it must always contain a non-NULL value; NULL is not allowed. Columns can have a default value.
Blob and text columns cannot have a DEFAULT value (Ref). Depending on platform, MySQL may generate a warning or error when you try to do that.
Change the datatype to something more appropriate (e.g. VARCHAR).
149
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
