MySQL

I have a table in MySQL that have a few columns that have default values specified, but when I try to insert a row, (not specifying values for those default columns), it throws an error saying I cannot insert NULL values.

Here is the table example;

CREATE TABLE `users` (   `Id` int(10) unsigned NOT NULL AUTO_INCREMENT,   `UniqueName` varchar(120) NOT NULL,   `Password` varchar(1000) NOT NULL,   `PublicFlag` tinyint(1) NOT NULL,   `NoTimesLoggedIn` int(10) unsigned NOT NULL DEFAULT '0',   `DateTimeLastLogin` timestamp NOT NULL DEFAULT '1971-01-01 00:00:00',   `UserStatusTypeId` int(10) unsigned NOT NULL,   `Private` tinyint(1) NOT NULL DEFAULT '0',   `SiteName` varchar(255) NOT NULL,   `CountryId` int(10) NOT NULL DEFAULT '0',   `TimeZoneId` varchar(255) NOT NULL DEFAULT 'UTC',   `CultureInfoId` int(10) unsigned NOT NULL DEFAULT '0',   `DateCreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,   `UserCreated` int(10) unsigned NOT NULL,   `LastUpdatedBy` int(10) unsigned NOT NULL,   `DateLastUpdated` datetime DEFAULT NULL,   PRIMARY KEY (`Id`),   UNIQUE KEY `UniqueName_UNIQUE` (`UniqueName`),   KEY `Index 3` (`SiteName`) )

It complains about TimeZoneId, and when I populate TimeZoneId, it complains about CultureInforId.

I am using MySQL Version: 5.1.43-community

Here is the insert query I am trying to insert, grabbed from NHibernate Profiler:

INSERT INTO Users            (UniqueName,             Password,             PublicFlag,             NoTimesLoggedIn,             DateTimeLastLogin,             SiteName,             TimeZoneId,             DateCreated,             DateLastUpdated,             Private,             CountryId,             CultureInfoId,             UserCreated,             LastUpdatedBy,             UserStatusTypeId) VALUES     ('[email protected]','u1uhbQviLp89P9b3EnuN/Prvo3A4KVSiUa0=',1, 0,'1/01/1971 12:00:00 AM','V9O1T80Q6D',NULL,'2/08/2010 2:13:44 AM', '2/08/2010 2:13:44 AM',0, NULL, NULL, 4, 4,31)

Can default have NULL values?

If no default value is declared explicitly, the default value is the null value. This usually makes sense because a null value can be considered to represent unknown data. In a table definition, default values are listed after the column data type.

Can we insert NULL value in NOT NULL column?

Code Inspection: Insert NULL into NOT NULL columnYou cannot insert NULL values in col1 and col2 because they are defined as NOT NULL. If you run the script as is, you will receive an error. To fix this code, replace NULL in the VALUES part with some values (for example, 42 and 'bird' ).

How do I change the default value of a NULL column in MySQL?

To drop a default value, use ALTER col_name DROP DEFAULT : ALTER TABLE mytbl ALTER j DROP DEFAULT; In this case, the column's default value reverts to the standard default for the column type. For columns that can contain NULL values, this will be NULL .

How do you insert NULL values in a column while inserting data with example?

You can insert NULL value into an int column with a condition i.e. the column must not have NOT NULL constraints. The syntax is as follows. INSERT INTO yourTableName(yourColumnName) values(NULL);

Use the DEFAULT keyword instead:

INSERT INTO users (TimeZoneId) VALUES (DEFAULT);

MySQL - Cannot insert NULL value in column, but I have a default value specified?

Tags:

Ryk

People also ask

1 Answers

Bill Karwin

Recent Activity

Donate For Us