Why am I getting #1264 - Out of range value for column 'Identity_no' at row 1?

Question

I create a table and Identity_no is unique in that but I am getting error when I try to add a integer value with 8 number.Probably I am getting this error because I identified unique to Identity_no.How can I fix that Here is the code for creating table:

CREATE TABLE Members(
    Member_id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
    Identity_no INTEGER NOT NULL UNIQUE,
    Member_Name varchar(80) NOT NULL,
    Member_Surname varchar(80) NOT NULL,
    Member_Phone CHAR(11),
    MemberCityID INTEGER NOT NULL,
    FOREIGN KEY(MemberCityID) REFERENCES Cities(City_id));

Here is the code for adding data:

INSERT INTO Members 
  (Member_id,Identity_no,Member_Name,Member_Surname,Member_Phone,MemberCityID) 
VALUES
  (1,89768434566,"John","W","05379621522",78);

The maximum number for an `INTEGER` column (signed) is `2,147,483,647`, but you are trying to insert the bigger number `89,768,434,566`. — Progman, Dec 25 '21 at 14:29
Does this answer your question? [MySQL Error 1264: out of range value for column](https://stackoverflow.com/questions/14284494/mysql-error-1264-out-of-range-value-for-column) — Progman, Dec 25 '21 at 14:29
Thank you for your answer but at the link that you suggessted to take a look value is not unique should I use varchar for unique values.I would be appreciate it if you answered that. @Progman — , Dec 25 '21 at 14:33
@stck_n The problem is not the `UNIQUE` keyword. The number you want to insert is simply too big for the `INTEGER` column. Depending on what you are trying to do you have to use a different value or a different column type. — Progman, Dec 25 '21 at 14:38

score 3 · Accepted Answer · answered Dec 25 '21 at 14:30

The INTEGER type is a 32 bit signed int, which means numbers bigger than 2^31 cannot be stored in a column with this type. Use BIGINT instead:

CREATE TABLE Members(
Member_id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
Identity_no BIGINT NOT NULL UNIQUE,
Member_Name varchar(80) NOT NULL,
Member_Surname varchar(80) NOT NULL,
Member_Phone CHAR(11),
MemberCityID INTEGER NOT NULL,
FOREIGN KEY(MemberCityID) REFERENCES Cities(City_id));

Why am I getting #1264 - Out of range value for column 'Identity_no' at row 1?

1 Answers1