Not Null Columns

[basemap]

Hi

I have created a table with not null columns. If i insert a record with a null

into the column i would expect to be shown an error message that a value must be inserted (as in SQL Server), but the table allows a null to be inserted, is this a valid action if so what is the purpose of NOT NULL?

Thanks

Richard

[peterlaursen]

in 'non-strict' mode the server inserts 0 (zero) for a undefined numerical and '' (empty string) for an undefined string, 0000-00-00 for an undefined date etc.

in 'strict mode' an error ” out of range value …” occurs.

We have been discussing 'strict mode' a few times recently!

Strict mode is new with SQL version 5. With earlier version stric mode is not available.

You can go through:

http://www.webyog.com/forums/index.php?act…ite=strict+mode

You will find link to the MySQL docs explaining in those threads ….

And a FAQ entry:

http://www.webyog.com/faq/28_72_en.html

[peterlaursen]

It also has to do with the workings of SQLyog

for instance, with this table

Code:

CREATE TABLE `tt` (                        
         `id` bigint(20) NOT NULL auto_increment,  
         `t` varchar(50) NOT NULL,                
         `n` bigint(20) NOT NULL,                  
         PRIMARY KEY  (`id`)                      
       ) ENGINE=MyISAM DEFAULT CHARSET=latin1      

SQLyog wil generate this SQL

Code:

insert into `test`.`tt` ( `id`, `t`, `n` ) values (  '',  'yy',  '' )

when inserting the singe value 'yy' in field `t` from the data-pane.

Now '' (empty string) is a legal string value, but not a legal numerical value!

It is a problem that the grid view of DATA and RESULT panes do not distinguish between NULL's and empty strings. I know it is on the TODO.

[peterlaursen]

to pin it out:

execute this SQL with the above table

Code:

set sql_mode = '';
insert into `test`.`tt` (`t` ) values ('nn');

and a row is inserted. The MySQL server 'unstrictly' replaces the undefined numerical value with a zero. This applies when coloumn is defined as 'NOT NULL'. If column is defined as 'NULL' the value of n-column stays NULL with this statement.

However

Code:

set sql_mode = 'strict_all_tables';
insert into `test`.`tt` (`t` ) values ('nn');

raises the error

Quote:

Field 'n' doesn't have a default value

So what we have here is that traditional mySQL syntax is very 'forgiving' and 'relaxed' and 'non-strict'. That is true for versions up to 4.0. From 4.1 and 5.0 in particular a more 'strict' approach is used by MySQL defaults. And SQLyog was written for 3.x and 4.0 originally.

A parsing for NULL, type, (predefined) variables and functions in the grids is becoming even more important for each new MySQL release. It is for the same reason that we have the problem with CURRENT_TIMESTAMP for instance.

But with MySQL up to 4.0 it worked perfectly fine just to get the grid value and send it to the server as a string value.

[Author]

Posts