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

Solution 1:

Use the DEFAULT keyword instead:

INSERT INTO users (TimeZoneId) VALUES (DEFAULT);

Solution 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.

Solution 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)