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

Question

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.

I am no MySQL expert, but I would expect you to be able to insert a row without having to specify the values for the columns that have default values specified on them in the table.

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

thanks in advance.

UPDATE: Sorry I removed the SiteName column, and a few others that will just clutter the query, but have now put the original back.

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

Insert Sample: 

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)

UPDATE: Sorry, I missed pasting 3 columns.

Answer 1

Use the DEFAULT keyword instead:

INSERT INTO users (TimeZoneId) VALUES (DEFAULT);

Answer 2

Do not insert NULL values. I'm assuming you were trying this syntax:

INSERT INTO users VALUES (null, 'Jones', 'yarg', 1, null, null, null);

Instead, use this syntax:

INSERT INTO users SET UniqueName='Jones', Password='yarg';

For more info, see the MySQL docs on INSERT.

Answer 3

You have "NOT NULL" set on fields that you are trying to INSERT NULL on.

eg. CountryId, CultureInfoId, TimeZoneId

execute the following:

ALTER TABLE `users` MODIFY `CountryId` int(10) DEFAULT '0' NULL;
ALTER TABLE `users` MODIFY `CultureInfoId` int(10) unsigned DEFAULT '0' NULL;
ALTER TABLE `users` MODIFY `TimeZoneId` varchar(255) DEFAULT 'UTC' NULL;

EDIT: Didn't realize he wanted the default value instead of NULL on "null" insert. Basically as already has been suggested use the DEFAULT keyword in place of NULL on the values.

OR leave the NULL fields and values out altogether and mysql will use the defined defaults eg.

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

Answer 4

As an alternative to using the DEFAULT keyword you can also just not specify values for the fields which you want to have default values. For instance if you just removed TimeZoneId, CountryId and CultureInfoId from your query entirely those columns will receive the default values automatically:

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

I'm not sure how that would work in the context of NHibernate however as that part of the question wasn't quite as well explained.

Answer 5

TimeZoneIdvarchar(255) NOT NULL DEFAULT 'UTC', CultureInfoId` int(10) unsigned NOT NULL DEFAULT '0',

For this fields you have set constraints as "Not Null" and hence values inserted can't be null and hence either alter the table structure or just not specify values for the fields which you want to have default values.