Automatic Default Values For "not Null" Fields?

[kybert]

sqlyog V9.33GA

mysql 5.0.15-nt

Hi,

When editing a table, if have noticed that if i have a field that has “not null” ticked, a default value is automaticly used. E.g. a bigint, a default of 0 is being used, and a varchar, a default of '' is being used.

How can i remove the automatic default values?

If i forget to add a value in a SQL statement, i want the database to show the error: “field cannot be null”

http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html

I have just reinstalled mysql, apache2, and the latest version of sqlyog on a new PC, so it could be a configuration problem, but prevously a default value was not shown on webyog. I cant remember what version webyog i was using before 9.33, nor can i find a “download older versions” section of the website.

[Chirantan Thakkar]

Hi,

please refer: http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html

Quote:

If the column cannot take NULL as the value, MySQL defines the column with an explicit DEFAULT clause, using the implicit default value for the column data type

Prior to MySQL 5.0.2, if a column definition includes no explicit DEFAULT value and the “Not Null” is ticked, then mysql will automatically assign default value to the column based on datatype. So, we can not control it.

By creating a unique index on each column, You can assure a unique value in each column. This will accept NULL for the first time for the column but not again for the same column(ie, MySQL will give you error except for the first time you insert NULL). But again, that will not accept any duplicate value.

[peterlaursen]

It also depends on the sql_mode with servers > 5.02. Now default is applied in 'strict' modes. But you will then get an error if not specifying a value yourself.

You can definde strict mode ('strict_all_tables') for the connection in the 'advanced' tab of SQLyog connection manager. Same page in the MySQL documentation explains:

Quote:

“MySQL handles the column according to the SQL mode in effect at the time: If strict SQL mode is not enabled, MySQL sets the column to the implicit default value for the column data type. If strict mode is enabled, an error occurs for transactional tables and the statement is rolled back. For nontransactional tables, an error occurs, but if this happens for the second or subsequent row of a multiple-row statement, the preceding rows will have been inserted.”

[kybert]

Thanks for the reply. Strict mode shows no setting in the setting viewer.

I installed mysql 5.1.60 and it has fixed the problem.

[Author]

Posts