Reputation: 329
This table I created in a SQLite database:
CREATE TABLE [tickets] (
[id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
[coupon_id] INTEGER NULL,
[size] FLOAT NULL,
[phone] VARCHAR(10) NULL,
[date] DATE DEFAULT CURRENT_DATE NULL,
[time] TIME DEFAULT CURRENT_TIME NULL,
[product] TEXT NULL
);
Now INSERT
operation is:
INSERT INTO "tickets" VALUES(429,9,18.16,'949-893-5032','2010-11-30','17:46:39','Kids’ Kups Berry Interesting™');
INSERT INTO "tickets" VALUES(430,9,12.04,'847-188-1359','2010-11-25','10:54:00','Raspberry Collider™');
INSERT INTO "tickets" VALUES(431,9,14.1,'204-682-5560','2010-12-08','15:34:07','Celestial Cherry High™');
Now the same table I created in MySQL:
CREATE TABLE tickets (
id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
coupon_id INTEGER NULL,
size FLOAT NULL,
phone VARCHAR(10) NULL,
date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NULL,
time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NULL,
product TEXT NULL
);
INSERT
operation for MySQL is:
INSERT INTO tickets VALUES(429,9,18.16,'949-893-5032','2010-11-30','17:46:39','Kids’ Kups Berry Interesting™');
INSERT INTO tickets VALUES(430,9,12.04,'847-188-1359','2010-11-25','10:54:00','Raspberry Collider™');
INSERT INTO tickets VALUES(431,9,14.1,'204-682-5560','2010-12-08','15:34:07','Celestial Cherry High™');
When i am inserting those values I got an error :-there can be only one TIMESTAMP column with current_timestamp in default of on update clause
…but I am not able to insert all those values into MySQL. Help me?
Upvotes: 1
Views: 1470
Reputation: 51
Your MySQL Schema appears to be incorrect for what you're trying to insert.
Excerpt from this post: Should I use field 'datetime' or 'timestamp'? ...Timestamps in MySQL generally used to track changes to records, and are updated every time the record is changed. If you want to store a specific value you should use a datetime field.
Change your MySQL schema to something closer to:
...
...
Upvotes: 0
Reputation: 44354
At first glace, your varchar
column is size 10, but you are inserting greater than length 10 data into it. Make sure your varchar
column is wide enough for your data.
Upvotes: 1
Reputation: 57603
In SQLite you have two columns
[date] DATE DEFAULT CURRENT_DATE NULL,
[time] TIME DEFAULT CURRENT_TIME NULL,
while on MySQL you have only one
date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NULL,
and you're trying to insert two values on it...
You should try
INSERT INTO tickets VALUES(..., '2010-11-30 17:46:39', ...)
Upvotes: 1