Arithmetic overflow error converting expression to data type int, How do I resolve this?

Question

This is the code that created the table.

CREATE TABLE CUSTOMERS
(
    Customer_ID INT NOT NULL,
    CHECK(Customer_ID <= 11),
    First_Name varchar(20) NOT NULL,
    Last_Name varchar(30),
    Home_Street varchar(30),
    Home_City varchar(20),
    Home_State varchar(2),
    Home_Zip varchar(5),
    PhoneNumber varchar(11) NOT NULL
);

ALTER TABLE CUSTOMERS
ADD CONSTRAINT PK_CUSTOMERS PRIMARY KEY(Customer_ID);

Then I try to insert data (using this code) into the table and that is where I get this error.

INSERT INTO dbo.CUSTOMERS(Customer_ID, First_Name, Last_Name, Home_Street, Home_City, Home_State, Home_Zip, PhoneNumber)
VALUES (11223344556, 'John', 'Doe', '1234 Hand Street', 'Wahiawa', 'HI', 96786, 2535551267);

What am I doing wrong and what can I do to fix this issue?

Home_Zip and Phonenumber are VARCHAR , so you need to enclose the values in quotes ,e,g,'96786','2535551267') — Satya
– Satya, Commented Aug 30, 2018 at 7:03
Customer_ID is an int. The largest number it can contain is 2147483647. You're trying to insert something longer than that (and, to boot, you have a CHECK constraint that means the only positive numbers it will accept are 1-11) — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Aug 30, 2018 at 7:05
So using CHECK function will only allow me to use numbers 1-10? I'm still trying to understand what I'm doing. I'm trying to set the int to 11. I thought what I was doing limited it to 11 digits. — Kratocus
– Kratocus, Commented Aug 30, 2018 at 7:09

Chanukya · Accepted Answer · 2018-08-30 07:07:16Z

3

AS per my understanding you are checking length of Customer_ID <=11 so you should mention len(Customer_ID)<=11 it will work and you should alter datatype of Customer_ID int to bigint

CREATE TABLE CUSTOMERS
    (
    Customer_ID bigINT NOT NULL,
    CHECK(len(Customer_ID)<=11),
    First_Name varchar(20) NOT NULL,
    Last_Name varchar(30),
    Home_Street varchar(30),
    Home_City varchar(20),
    Home_State varchar(2),
    Home_Zip varchar(5),
    PhoneNumber varchar(11) NOT NULL
    );
    ALTER TABLE CUSTOMERS
    ADD CONSTRAINT PK_CUSTOMERS
    PRIMARY KEY(Customer_ID);

    INSERT INTO dbo.CUSTOMERS(Customer_ID,First_Name,Last_Name,Home_Street,
    Home_City,Home_State,Home_Zip,PhoneNumber)
    VALUES(11223344556,'John','Doe','1234 Hand Street', 
    'Wahiawa','HI',96786,2535551267);

answered Aug 30, 2018 at 7:07

Chanukya

5,8911 gold badge24 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Juozas Over a year ago

@Icodin my answer was first and correct. Thank you for marking later posted same answer as right.

Juozas · Accepted Answer · 2018-08-30 07:04:23Z

1

You need customer id to be bigint:

CREATE TABLE CUSTOMERS
(
Customer_ID BIGINT NOT NULL,
CHECK(Customer_ID<=11),
First_Name varchar(20) NOT NULL,
Last_Name varchar(30),
Home_Street varchar(30),
Home_City varchar(20),
Home_State varchar(2),
Home_Zip varchar(5),
PhoneNumber varchar(11) NOT NULL
);
ALTER TABLE CUSTOMERS
ADD CONSTRAINT PK_CUSTOMERS
PRIMARY KEY(Customer_ID);

answered Aug 30, 2018 at 7:04

Juozas

94310 silver badges18 bronze badges

1 Comment

Chanukya Over a year ago

how this answer will be correct still after seeing my answer you are not getting what i wrote check it once and comment

Saravanan · Accepted Answer · 2018-08-30 07:05:54Z

1

The problem may be due to CHECK(Customer_ID<=11) since Customer_ID id integer datatype the server may check for integer validation and not length validation. Try to change the validation.

answered Aug 30, 2018 at 7:05

Saravanan

8299 silver badges16 bronze badges

Collectives™ on Stack Overflow

Arithmetic overflow error converting expression to data type int, How do I resolve this?

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related