CODEWITHSUNDEEP
mysql
TheCoolestPenguin
TheCoolestPenguin

Reputation: 87

Insert a NOT NULL value into a NOT NULL field causes error

I created a table using the following command: 

CREATE TABLE SpatialInfo (ItemID Integer NOT NULL, Latitude decimal(9,6) null, Longitude decimal(9,6) null) ENGINE=MyISAM;

INSERT INTO SpatialInfo (ItemID, Latitude, Longitude)
VALUES
(
 (SELECT ID AS ItemID from Item WHERE ID IS NOT NULL and Latitude IS NOT NULL and Longitude IS NOT NULL),
 (SELECT Latitude from Item),
 (SELECT Longitude from Item)
);

Where table Item has following description:

+-------------+---------------+------+-----+---------------------+----------------------------+
| Field       | Type          | Null | Key | Default             | Extra                       |
+-------------+---------------+------+-----+---------------------+-----------------------------+
| ID          | int(11)       | NO   | PRI | NULL                |                                 |
| Name        | varchar(300)  | NO   |     | NULL                |                             |
| Currently   | decimal(8,2)  | NO   |     | NULL                |                             |
| Buy_price   | decimal(8,2)  | YES  |     | NULL                |                             |
| First_bid   | decimal(8,2)  | NO   |     | NULL                |                             |
| Number_bids | int(11)       | YES  |     | NULL                |                             |
| Latitude    | decimal(9,6)  | YES  |     | NULL                |                             |
| Longitude   | decimal(9,6)  | YES  |     | NULL                |                             |
| Location    | varchar(60)   | YES  |     | NULL                |                             |
| Country     | varchar(60)   | YES  |     | NULL                |                             |
| Start       | timestamp     | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| End         | timestamp     | NO   |     | 0000-00-00 00:00:00 |                             |
| SellerID    | varchar(60)   | NO   | MUL | NULL                |                             |
| Description | varchar(4000) | YES  |     | NULL                |                             |
+-------------+---------------+------+-----+---------------------+-----------------------------+

One thing I have noticed is that the default value if ID in table Item is NULL. However, ID is a primary key so it is impossible to be NULL.

When I run the INSERT INTO SpatialInfo command, mysql gives me this error:

ERROR 1048 (23000): Column 'ItemID' cannot be null

I wonder how I can solve this problem.

Upvotes: 0

Views: 461

Answers (2)

Pritesh Usadadiya
Pritesh Usadadiya

Reputation: 29

Null indicates a absent value or we can say it's unknown. so, you can have multiple records that have a null for the field.Even though you have specified as a Primary Key Field. so, be sure to use NOT NULL on a Primary-key Field.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Your error is probably caused by the fact that you have multiple rows in the item table, so the subqueries are returning more than one row. Try doing it this way:

INSERT INTO SpatialInfo (ItemID, Latitude, Longitude)
    SELECT ID AS ItemID, Latitude, Longitude
    from Item
    WHERE ID IS NOT NULL and Latitude IS NOT NULL and Longitude IS NOT NULL;

I'm not sure why you are getting that particular error. I would expect something more like "multiple rows returned where one is expected".

Upvotes: 2

Related Questions