CODEWITHSUNDEEP
mysql
rahul1205
rahul1205

Reputation: 884

Using tinyint as datatype in MySQL 5.5.57

I am having trouble with the following SQL query. The error arises in status field when I specify tinyint as its data-type. Am I using the wrong syntax?

Query

create table device_master( 
id INT,
serial_number VARCHAR, 
status TINYINT, 
created_at DATETIME, 
updated_at TIMESTAMP ON UPDATE CURRENT TIMESTAMP, 
created_by INT, 
updated_by INT, primary key(id));

Error

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' status TINYINT, created_at DATETIME, updated_at TIMESTAMP ON UPDATE CURRENT TIM' at line 1

Upvotes: 0

Views: 392

Answers (3)

simon.ro
simon.ro

Reputation: 3302

There are actually two syntax errors

  1. You have to specify the max length of the varchar e.g. VARCHAR(255)
  2. Its ON UPDATE CURRENT_TIMESTAMP (note the _)

Try running:

create table device_master( 
  id INT,
  serial_number VARCHAR(255), 
  status TINYINT, 
  created_at DATETIME, 
  updated_at TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
  created_by INT, 
  updated_by INT, 
  primary key(id)
);

Upvotes: 2

user8527410
user8527410

Reputation: 493

I think not tinyint is your problem. You have to declare the length of varchar for serial_number:

create table device_master( 
id INT,
serial_number VARCHAR(100), 
status TINYINT, 
created_at DATETIME, 
updated_at TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
created_by INT, 
updated_by INT, primary key(id));

Upvotes: 2

Shadow
Shadow

Reputation: 34232

No, the issue is that you did not specify length for serial_number field. It should be defined as serial_number VARCHAR(N),, where N is the maximum length in characters.

Note, that the code excerpt in the syntax error message start before the word status, indicating the error occurred way before the tinyint data type were specified.

Upvotes: 2

Related Questions