If you’re getting error 1048 that reads something like “Column ‘ColumnName’ cannot be null” (where ColumnName is the name of a column you’re trying to insert data into), then it’s probably because you’re trying to insert a NULL
value into that column, but the column has a NOT NULL
constraint (which prevents NULL
values from being inserted).
We have a few options when it comes to fixing this issue. The most obvious is to ensure we provide a non-NULL
value for the column. Alternatively, if the column should be able to accept NULL
values, then we can remove the NOT NULL
constraint from the column. Another option is to use the IGNORE
keyword to ignore the error. And another way to deal with the error is to disable strict mode.
Example of Error
Here’s an example of code that produces the error:
INSERT INTO Products ( ProductName, ProductDescription )
VALUES ( 'Hammer', NULL );
Result:
ERROR 1048 (23000): Column 'ProductDescription' cannot be null
We get an error because we tried to insert NULL
into the ProductDescription
column, but that column doesn’t allow NULL
values. We know this because the error message tells us that the ProductDescription
column cannot be NULL
.
We can use the SHOW CREATE TABLE
statement to check the table’s definition:
SHOW CREATE TABLE Products;
Result:
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Products | CREATE TABLE `Products` ( `ProductName` varchar(255) NOT NULL, `ProductDescription` varchar(1000) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
We can see that this table contains two columns, both of which have NOT NULL
constraints.
Solution 1 – Provide a non-NULL
Value
The most obvious solution is to provide a non-NULL
value for the column.
Example:
INSERT INTO Products ( ProductName, ProductDescription )
VALUES ( 'Hammer', 'Left handed edition' );
Result:
Query OK, 1 row affected (0.00 sec)
Providing a non-NULL
value resolved our issue.
Solution 2 – Remove the NOT NULL
Constraint
If we determine that the column really shouldn’t have a NOT NULL
constraint, we can remove it before inserting data.
Example:
ALTER TABLE Products
MODIFY ProductDescription varchar(1000) NULL;
Result:
Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0
This message indicates that we successfully removed the NOT NULL
constraint. Or perhaps more accurately, it indicates that we modified the column to allow NULL
values.
We could also have omitted the NULL
keyword, and the column would have been redefined without the NOT NULL
constraint, like this:
ALTER TABLE Products
MODIFY ProductDescription varchar(1000);
Now let’s try inserting NULL
into that column again:
INSERT INTO Products ( ProductName, ProductDescription )
VALUES ( 'Monkey Wrench', NULL );
Result:
Query OK, 1 row affected (0.00 sec)
This time it was successful.
Let’s take a look at the data in our table:
SELECT * FROM Products;
Result:
+---------------+---------------------+ | ProductName | ProductDescription | +---------------+---------------------+ | Hammer | Left handed edition | | Monkey Wrench | NULL | +---------------+---------------------+
We can see that both rows from our examples have been inserted successfully.
Solution 3 – Use the IGNORE
Clause
Another way to deal with the issue is to use the IGNORE
clause in our INSERT
statement.
Let’s drop the table and start again:
DROP TABLE Products;
CREATE TABLE Products (
ProductName varchar(255) NOT NULL,
ProductDescription varchar(1000) NOT NULL
);
Now let’s try to insert some values that contain NULL
:
INSERT INTO Products ( ProductName, ProductDescription )
VALUES
( 'Hammer', 'Left handed edition' ),
( 'Saw', NULL ),
( 'Wrench', NULL ),
( 'Screw Driver', 'Right handed edition' );
Result:
ERROR 1048 (23000): Column 'ProductDescription' cannot be null
As expected, we get the error.
Now let’s try again, but with the IGNORE
keyword:
INSERT IGNORE INTO Products ( ProductName, ProductDescription )
VALUES
( 'Hammer', 'Left handed edition' ),
( 'Saw', NULL ),
( 'Wrench', NULL ),
( 'Screw Driver', 'Right handed edition' );
Result:
Query OK, 4 rows affected, 1 warning (0.00 sec) Records: 4 Duplicates: 0 Warnings: 1
This time the data was inserted successfully.
However, we did get a warning. Let’s check it:
SHOW WARNINGS;
Result:
+---------+------+--------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------+ | Warning | 1048 | Column 'ProductDescription' cannot be null | +---------+------+--------------------------------------------+
So the IGNORE
keyword downgraded the error to a warning.
Let’s check the contents of the table:
SELECT
*,
ISNULL(ProductDescription)
FROM Products;
Result:
+--------------+----------------------+----------------------------+ | ProductName | ProductDescription | ISNULL(ProductDescription) | +--------------+----------------------+----------------------------+ | Hammer | Left handed edition | 0 | | Saw | | 0 | | Wrench | | 0 | | Screw Driver | Right handed edition | 0 | +--------------+----------------------+----------------------------+
I added the third column to show that MySQL doesn’t actually insert NULL
into the column when we use the IGNORE
keyword. Instead, it inserts the implicit default value for the data type. In our case, the data type is a string, and so it inserted the empty string.
Solution 4 – Disable Strict SQL Mode
The whole reason we get an error is because we have strict SQL mode enabled. When we used the IGNORE
keyword, we were basically just overcoming the error we’d get due to the fact that we have strict mode enabled.
So another way to deal with the error is to disable strict mode altogether.
Here’s what my SQL mode looks like:
SELECT @@SESSION.sql_mode;
Result:
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Strict SQL mode is in effect if either STRICT_ALL_TABLES
or STRICT_TRANS_TABLES
is enabled. My SQL mode contains STRICT_TRANS_TABLES
, which means I have strict SQL mode enabled. It also contains related modes NO_ZERO_IN_DATE
, NO_ZERO_DATE
, and ERROR_FOR_DIVISION_BY_ZERO
. Depending on your MySQL implementation, you may or may not have these other modes.
Let’s disable strict mode:
SET @@sql_mode = sys.list_drop(@@sql_mode, 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO');
SELECT @@SESSION.sql_mode;
Result:
ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION
We can see that our SQL mode no longer contains STRICT_TRANS_TABLES
. I also removed NO_ZERO_IN_DATE
, NO_ZERO_DATE
, and ERROR_FOR_DIVISION_BY_ZERO
.
Let’s drop the table and start again:
DROP TABLE Products;
CREATE TABLE Products (
ProductName varchar(255) NOT NULL,
ProductDescription varchar(1000) NOT NULL
);
Now let’s try to insert some values that contain NULL
:
INSERT INTO Products ( ProductName, ProductDescription )
VALUES
( 'Hammer', 'Left handed edition' ),
( 'Saw', NULL ),
( 'Wrench', NULL ),
( 'Screw Driver', 'Right handed edition' );
Result:
Query OK, 4 rows affected, 1 warning (0.00 sec) Records: 4 Duplicates: 0 Warnings: 1
We get the same result that we got when using the IGNORE
keyword.
Let’s check the warnings:
SHOW WARNINGS;
Result:
+---------+------+--------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------+ | Warning | 1048 | Column 'ProductDescription' cannot be null | +---------+------+--------------------------------------------+
As expected, the warning tells us that the column can’t be NULL
.
And let’s check the data:
SELECT
*,
ISNULL(ProductDescription)
FROM Products;
Result:
+--------------+----------------------+----------------------------+ | ProductName | ProductDescription | ISNULL(ProductDescription) | +--------------+----------------------+----------------------------+ | Hammer | Left handed edition | 0 | | Saw | | 0 | | Wrench | | 0 | | Screw Driver | Right handed edition | 0 | +--------------+----------------------+----------------------------+
Again, same result as with the IGNORE
keyword.
So, disabling strict SQL mode allowed us to insert NULL
values without getting an error. Or more accurately, it allowed us to attempt to insert NULL
values without error. As stated, using this method doesn’t actually insert NULL
. It inserts the implicit default value for the data type.
When it comes to using IGNORE
vs disabling strict SQL mode, there are a few nuances to consider. The MySQL documentation contains a comparison of IGNORE
and strict SQL mode that can be helpful when deciding which option to use.
I have a query:
SELECT
COUNT(*)
FROM
(SELECT
vendors_id,
merchants_id,
SUM(amount) AS amount,
SUM(commission_amount) AS commission_amount
FROM
(SELECT
vendors_id,
merchants_id,
amount,
commission_amount
FROM
(SELECT
vendors.id AS vendors_id,
merchants_id,
SUM(transactions_cash.amount) AS amount,
SUM(
transactions_cash.commission_amount
) AS commission_amount
FROM
ibaserver.transactions_cash,
ibaserver.vendors,
ibaserver.merchants
WHERE transactions_cash.vendors_id = vendors.id
AND TIME > 1466680920208
AND TIME <= 1466681880067
AND merchants_id = merchants.id
GROUP BY transactions_cash.merchants_id
ORDER BY transactions_cash.merchants_id) a
UNION
ALL
SELECT
vendors_id,
merchants_id,
amount,
commission_amount
FROM
(SELECT
vendors.id AS vendors_id,
merchants_id,
SUM(
transactions_cash_archive.amount
) AS amount,
SUM(
transactions_cash_archive.commission_amount
) AS commission_amount
FROM
ibaserver.transactions_cash_archive,
ibaserver.vendors,
ibaserver.merchants
WHERE transactions_cash_archive.vendors_id = vendors.id
AND TIME > 1466680920208
AND TIME <= 1466681880067
AND merchants_id = merchants.id
GROUP BY transactions_cash_archive.merchants_id
ORDER BY transactions_cash_archive.merchants_id) b) s) q
And when the inner query
SELECT
vendors_id,
name,
amount
FROM ....
returns the data, the result is returned successfully. If the inner query returns only NULL:
the entire query returns an error:
Error Code: 1048
Column 'vendors_id' cannot be null
How to return 0 in the case when the inner query returns all of a NULL?
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (925217, ‘null’, 0, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (1, ‘[]’, 0, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (915156, ‘[97167]’, 97167, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (913286, ‘[5]’, 5, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (913313, ‘[6]’, 6, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (912923, ‘[8]’, 8, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (913435, ‘[10]’, 10, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (912915, ‘[11]’, 11, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (913200, ‘[12]’, 12, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (912910, ‘[14]’, 14, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (913507, ‘[16]’, 16, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (912956, ‘[17]’, 17, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (916658, ‘[18]’, 18, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (913113, ‘[19]’, 19, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (912943, ‘[20]’, 20, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (913177, ‘[24]’, 24, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (913292, ‘[26]’, 26, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (913556, ‘[29]’, 29, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (913070, ‘[30]’, 30, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (914218, ‘[33]’, 33, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (913132, ‘[36]’, 36, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (915191, ‘[37]’, 37, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (912922, ‘[39]’, 39, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (918295, ‘[40]’, 40, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (914929, ‘[1]’, 1, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (913088, ‘[56]’, 56, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (913621, ‘[97210]’, 97210, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (927564, ‘[97147]’, 97147, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (914501, ‘[5, 14]’, 5, 14, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (912947, ‘[11, 14]’, 11, 14, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (912906, ‘[11, 24]’, 11, 24, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (919620, ‘[12, 14]’, 12, 14, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (914603, ‘[14, 11]’, 14, 11, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (920099, ‘[14, 12]’, 14, 12, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (924294, ‘[14, 16]’, 14, 16, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (913985, ‘[14, 24]’, 14, 24, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (914347, ‘[14, 5]’, 14, 5, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (916604, ‘[17, 24]’, 17, 24, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (921085, ‘[16, 14]’, 16, 14, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (916110, ‘[16, 24]’, 16, 24, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (913074, ‘[24, 11]’, 24, 11, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (914512, ‘[24, 14]’, 24, 14, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (913394, ‘[24, 17]’, 24, 17, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (935064, ‘[18, 37]’, 18, 37, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (920771, ‘[39, 17]’, 39, 17, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (920074, ‘[37, 18]’, 37, 18, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937287, ‘[14, 24, 0]’, 14, 24, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (938159, ‘[1, 0, 0]’, 1, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (941325, ‘[40, 0, 0]’, 40, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937426, ‘[19, 0, 0]’, 19, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937185, ‘[11, 24, 0]’, 11, 24, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937349, ‘[56, 0, 0]’, 56, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937558, ‘[14, 11, 5]’, 14, 11, 5);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (916608, ‘[14, 11, 24]’, 14, 11, 24);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (946805, ‘[97147, 0, 0]’, 97147, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937160, ‘[24, 11, 0]’, 24, 11, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (947617, ‘[14, 24, 11]’, 14, 24, 11);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937161, ‘[11, 0, 0]’, 11, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937718, ‘[97210, 0, 0]’, 97210, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937659, ‘[33, 0, 0]’, 33, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937916, ‘[14, 5, 0]’, 14, 5, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937484, ‘[24, 14, 0]’, 24, 14, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (938895, ‘[17, 24, 0]’, 17, 24, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (930430, ‘[97147, 24, 11]’, 97147, 24, 11);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937198, ‘[24, 17, 0]’, 24, 17, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (938854, ‘[5, 14, 0]’, 5, 14, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (938250, ‘[26, 0, 0]’, 26, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (941167, ‘[24, 19, 0]’, 24, 19, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937209, ‘[30, 0, 0]’, 30, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937301, ‘[5, 0, 0]’, 5, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937169, ‘[36, 0, 0]’, 36, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (938038, ‘[6, 0, 0]’, 6, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (913324, ‘[24, 14, 11]’, 24, 14, 11);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (914887, ‘[24, 11, 14]’, 24, 11, 14);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (916501, ‘[24, 17, 11]’, 24, 17, 11);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (930774, ‘[24, 19, 11]’, 24, 19, 11);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (946722, ‘[24, 19, 17]’, 24, 19, 17);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (913486, ‘[24, 16, 11]’, 24, 16, 11);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937158, ‘[39, 0, 0]’, 39, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937159, ‘[17, 0, 0]’, 17, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937157, ‘[8, 0, 0]’, 8, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (939049, ‘[16, 14, 0]’, 16, 14, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (941216, ‘[14, 11, 0]’, 14, 11, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937482, ‘[10, 0, 0]’, 10, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937248, ‘[0, 0, 0]’, 0, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937210, ‘[12, 0, 0]’, 12, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937514, ‘[29, 0, 0]’, 29, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937183, ‘[14, 0, 0]’, 14, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (940628, ‘[37, 0, 0]’, 37, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937219, ‘[16, 0, 0]’, 16, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (913445, ‘[11, 14, 24]’, 11, 14, 24);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (940199, ‘[11, 17, 24]’, 11, 17, 24);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (924258, ‘[11, 16, 24]’, 11, 16, 24);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937449, ‘[11, 14, 0]’, 11, 14, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (937170, ‘[24, 0, 0]’, 24, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (938459, ‘[97167, 0, 0]’, 97167, 0, 0);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (941402, ‘[24, 14, 11, 5]’, 24, 14, 11);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (932540, ‘[24, 16, 14, 11]’, 24, 16, 14);
INSERT INTO `parse` (`id`, `teh_reg_json`, `reg_virtual`, `reg1_virtual`, `reg2_virtual`) VALUES (930755, ‘[24, 19, 11, 5]’, 24, 19, 11);
I am stuck, I cannot fix this error that I am getting, and I think it may be that my database options are messed up
Here’s where the data is put into a form
<form name = "quoted" method="post" onsubmit="get_action(this);">
<input id = "poster" type="text" name="poster" required="required" placeholder = "Credited Individual."> <br>
<textarea class = "actual_quote" name = "actual_quote" required="required" placeholder = "Write the question here!"></textarea><br><br><br>
<div class = "checkboxes" required="required">
<h3 style = "margin-top:-20px;">Please select one catagory that the quote falls into.</h3>
<label for="x"><input type="radio" name="x" value="Inspirational" id = "inspirational.php" checked="checked" /> <span>Inspirational</span></label><br>
<label for="x"><input type="radio" name="x" value="Funny" id = "funny.php" /> <span>Funny</span> </label><br>
<label for="x"><input type="radio" name="x" value="OutofContext" id = "outofcontext.php"/> <span>OutofContext</span></label>
</div>
<input id = "submit1" name="submit1"s type="submit"><br>
</form>
and here’s the php to put that into the database
<?php
$db_name = 'submissions';
$db_user = 'root';
$db_pass = '';
$db_host = 'localhost';
try {
$db = new PDO('mysql:host = localhost;dbname=submissions', $db_user, $db_pass);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}
$actual_quote = (isset($_POST['actual_quote']) ? $_POST['actual_quote'] : null);
$poster = (isset($_POST['poster']) ? $_POST['poster'] : null);
$sql = "INSERT INTO data (actual_quote, poster) VALUES ( :actual_quote, :poster)";
$query = $db->prepare($sql);
$query->execute(array(':actual_quote'=>$actual_quote, ':poster'=>$poster));
?>
( ! ) Fatal error: Uncaught exception ‘PDOException’ with message ‘SQLSTATE[23000]: Integrity constraint violation: 1048 Column ‘actual_quote’ cannot be null’ in C:\wamp\www\Quotr\webweb2.php on line 113 ( ! ) PDOException: SQLSTATE[23000]: Integrity constraint violation: 1048 Column ‘actual_quote’ cannot be null in C:\wamp\www\Quotr\webweb2.php on line 113 Call Stack
Time Memory Function Location
1 0.0015 257160 {main}( ) ..\webweb2.php:0 2 0.0206 267672 execute ( ) ..\webweb2.php:113
When I do click null in the database, and I click the submit button on the form the error goes away and data shows up in the database, but there is no values, there’s nothing there
can someone tell me whats wrong and how to fix this, if it’s in the code, I personally think there’s an issue with the database, here’s some pictures of that.
https://i.stack.imgur.com/JjkYn.png
(ignore the first 3 on the last picture, the last one is where I am at)
Я видел похожие вопросы, но никогда не видел ответа, который работает на меня. У меня есть следующая таблица и триггерные определения…
DROP TRIGGER IF EXISTS c_consumption.newRateHistory;
DROP TABLE IF EXISTS c_consumption.myrate;
DROP TABLE IF EXISTS c_consumption.myratehistory;
USE c_consumption;
CREATE TABLE 'myrate' (
'consumerId' varchar(255) DEFAULT NULL,
'durationType' varchar(50) NOT NULL DEFAULT 'DAY',
'id' bigint(20) NOT NULL AUTO_INCREMENT,
'itemId' varchar(50) NOT NULL,
'quantity' double NOT NULL DEFAULT 1.0,
'quantityType' varchar(100) NOT NULL DEFAULT 'GALLON',
'timePeriod' double NOT NULL DEFAULT 1.0,
PRIMARY KEY ('id'),
UNIQUE INDEX 'UNIQUE_RATE'
('itemId' ASC, 'consumerId' ASC)
) ENGINE=InnoDB AUTO_INCREMENT=314 DEFAULT CHARSET=utf8;
CREATE TABLE 'myratehistory' (
'consumerId' varchar(255) DEFAULT NULL,
'durationType' varchar(50) DEFAULT NULL,
'itemId' varchar(50) NOT NULL,
'quantity' double DEFAULT NULL,
'quantityType' varchar(100) DEFAULT NULL,
'status' varchar(20) NOT NULL DEFAULT 'CREATED',
'timePeriod' double DEFAULT NULL,
'timestamp' DATETIME NULL,
PRIMARY KEY ('itemId', 'consumerId', 'timestamp')
) ENGINE=InnoDB AUTO_INCREMENT=314 DEFAULT CHARSET=utf8;
CREATE TRIGGER 'newRateToHistory'
AFTER INSERT
ON myrate
FOR EACH ROW
INSERT INTO myratehistory
(
consumerId,
durationType,
itemId,
quantity,
quantityType,
status,
timePeriod,
timestamp
)
VALUES(
new.consumerId,
new.durationType,
new.itemId,
new.quantity,
new.quantityType,
'CREATED',
new.timePeriod,
now());
Обратите внимание, что consumerId CAN имеет значение NULL.
Затем я запустил этот оператор SQL:
INSERT INTO c_consumption.myrate (
consumerId,
durationType,
itemId,
quantity,
quantityType,
timePeriod)
VALUES(
null,
'DAY',
'MyItem',
1.0,
'GALLON',
1.0);
Я получаю следующее сообщение:
Error Code: 1048 Column 'consumerId' cannot be null
Очевидно, я делаю что-то неправильно, но я не знаю, что это такое. Любая помощь будет принята с благодарностью.
Благодарю!