CODEWITHSUNDEEP
mysqlsql
Danestyles
Danestyles

Reputation: 13

data truncation error and int data types

CREATE TABLE Customer (
    customerid varchar(10) NOT NULL, 
    FirstName varchar(20) NOT NULL, 
    LastName varchar(20) NOT NULL, 
    StreetAddress varchar (30) NOT NULL, 
    City varchar (20)NOT NULL, 
    State varchar (20) NOT NULL, 
    Zip int(5) NOT NULL, 
    Hphone int (10) NOT NULL, 
    Mphone int (10) NOT NULL, 
    Ophone int (10) NOT NULL, 
    PRIMARY KEY (customerid));

CREATE TABLE OrderTable (
    donutorderid varchar(10) NOT NULL, 
    customerid varchar (10) NOT NULL,
    oderdate datetime (6) NOT NULL,
    PRIMARY KEY (donutorderid)
);

CREATE TABLE Donut (
    donutid varchar(10),
    donutname varchar(20),
    description varchar(30),
    unitprice numeric,
    PRIMARY KEY (donutid)
);

CREATE TABLE OrderLine (
    donutorderid varchar (30),
    donutid varchar (30),
    qty int (10),
    PRIMARY KEY (donutorderid,donutid)
);

ALTER TABLE OrderTable ADD INDEX checks (customerid), 
ADD CONSTRAINT checks FOREIGN KEY (customerid) REFERENCES Customer (customerid);
ALTER TABLE OrderLine ADD INDEX has (donutorderid), 
ADD CONSTRAINT has FOREIGN KEY (donutorderid) REFERENCES OrderTable (donutorderid);
ALTER TABLE OrderLine ADD INDEX available_in (donutid), 
ADD CONSTRAINT available_in FOREIGN KEY (donutid) REFERENCES Donut (donutid);

create view CustInfo
    AS Select customerid, concat(FirstName,LastName) as FullName from customer;

insert into customer
    (customerid, FirstName, LastName, StreetAddress, 
    City, State, Zip, Hphone, Mphone, Ophone) 
values ('123','John','Doe', 'one hoover lane', 'las vegas', 
        'nevada', 89104, 702555122, 702441111, 702332222);

Data truncation: Out of range value for column 'Hphone' at row 1

When I have 9 digits in my telephone e.g. 702551212 vs 7025551212 it works and Hphone, Mphone, Ophone all have int (10).

Upvotes: 0

Views: 444

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You have declared your phone numbers to be integers. That is a very, very bad idea. Declare them to be strings:

CREATE TABLE Customer (
    customerid varchar(10) NOT NULL, 
    FirstName varchar(20) NOT NULL, 
    LastName varchar(20) NOT NULL, 
    StreetAddress varchar (30) NOT NULL, 
    City varchar (20)NOT NULL, 
    State varchar (20) NOT NULL, 
    Zip char(5) NOT NULL, 
    Hphone varchar(32) NOT NULL, 
    Mphone varchar(32) NOT NULL, 
    Ophone varchar(32) NOT NULL, 
    PRIMARY KEY (customerid)
);

It is unclear to me why the phone numbers -- and other columns -- are declared NOT NULL. Not everyone has three phone numbers. In fact, some people have fewer and some more, suggesting that the phone numbers should really be in a separate table.

I also made the zip code a string. After all, leading zeros are very important for zip codes.

But, for your immediate problem, then use single quotes to put the numbers in as strings:

insert into customer(customerid, FirstName, LastName, StreetAddress, 
    City, State, Zip, Hphone, Mphone, Ophone) 
values ('123','John','Doe', 'one hoover lane', 'las vegas', 
        'nevada', '89104', '702555122', '702441111', '702332222');

Upvotes: 1

Related Questions