CODEWITHSUNDEEP
mysql
user1766104
user1766104

Reputation: 45

Data truncation error

For the following SQL I get the error:

Data truncation occurred on a write of column 0Data was 0 bytes long and 0 bytes were transferred.

Code:

CREATE TABLE faculty_members
(
fid        INTEGER AUTO_INCREMENT PRIMARY KEY,   
first_name VARCHAR(30) NOT NULL,
last_name  VARCHAR(30) NOT NULL,
rank       VARCHAR(25) NOT NULL,        
office     CHAR(8),                        
phone      CHAR(12),            
dob        DATE NOT NULL,
salary     DECIMAL(8,2),
CONSTRAINT faculty_members_pk PRIMARY KEY(fid),         
CONSTRAINT faculty_members_ck UNIQUE(first_name,last_name,dob),    
CONSTRAINT valid_salary CHECK(salary > 0)

);

And these are the INSERT statements

INSERT INTO faculty_members(first_name,last_name,rank,office,phone,dob,salary) VALUES
('Charles', 'Xavier', 'Professor', 'HSC 641', '563-555-6020', '11/09/1942', 125000),
('Bruce', 'Banner', 'Associate Professor', 'FO1 120', '563-555-8212', '06/20/1969', 87000),
('Hank', 'McCoy', 'Professor', 'FO1 120', '563-555-8212', '06/20/1972', 95000),
('Jeane', 'Grey', 'Assistant Professor', 'ECS 547', '563-555-8239', '03/19/1975', 95000),
('Erik', 'Lehnsherr', 'Professor', 'HSC 641', '563-555-6020', '11/09/1940', 115000),
('Diana', 'Prince', 'Associate Professor', 'HSC 400', '563-555-8212', '07/28/1967', 105100),
('Logan', 'Wolverine', 'Assistant Professor', 'ECS 540', '563-555-8100', '02/27/1964', 82000),
('Ororo', 'Storm', 'Assistant Professor', 'ECS 540', '563-555-8101', '05/01/1973', 82500);

Upvotes: 2

Views: 12584

Answers (2)

Harish
Harish

Reputation: 726

In our case, we were adding a value exceeding the column size.

Upvotes: 0

Marc B
Marc B

Reputation: 360572

Your dob values are incorrect. MySQL's date format is yyyy-mm-dd. Most like you're getting the default 0000-00-00 stored and the data truncation error from that.

Upvotes: 4

Related Questions