Menu
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) 
434
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.
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' ).
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 .
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); If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
