CODEWITHSUNDEEP
mysqlsqloutofrangeexception
Maz
Maz

Reputation: 118

Illogical SQL out of range value for integer column

I am getting an odd SQL error. I have a table client which contains the following attributes:

Points is set up as an integer of length 11 with NULL values accepted

I try to insert a new client using the following code:

INSERT into client VALUES (154,"Mr","Ted","Jones","[email protected]","07951234567",0,0);

And I get the following error:

1264 - Out of range value for column 'Points' at row 1

Just in case it is needed, the data in the first row is:

111 Mr  Rob Taylor  [email protected]  07523363535 438 0

I am finding it hard to understand what the error is about. It's not as if points will ever get to be so large that it needs to become a BigInt. 

CREATE TABLE client ( 
    ClientID int(11) NOT NULL, 
    Title varchar(45) NOT NULL, 
    Name varchar(45) NOT NULL, 
    LastName varchar(45) NOT NULL, 
    Email varchar(45) NOT NULL,
    Cellphone bigint(11) DEFAULT NULL, 
    Points int(11) DEFAULT NULL, 
    Balance decimal(10,0) NOT NULL 
) 
ENGINE=InnoDB DEFAULT CHARSET=utf8;

Upvotes: 0

Views: 1679

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270091

Always specify the columns when using insert. Presumably you intend:

INSERT into client(ClientId, Title, Name, LastName, email, Cellphone, Points, Balance)
    VALUES (154, 'Mr', 'Ted', 'Jones', '[email protected]', '07951234567', 0, 0);

My first guess is that the columns are in a different order in the table -- but don't bother figuring that out, just be explicit about the columns you are using.

Another possibility is that the cell phone column is declared as some sort of numeric value. If that is the case, fix the data and change the column to a varchar!

Upvotes: 4

Related Questions