CODEWITHSUNDEEP
mysql
user2975266
user2975266

Reputation: 67

MySQL creating table syntax error

I have a homework assignment and am trying to find my error. I am creating a table in MySQL and am a beginner to this. Please give suggestions!! 

Create table computer_inventory 
( 
   assetnumber int(10) not null default “0”,
   manufacturer varchar(15) not null default ‘ ‘, 
   originalcost decimal(12,2) not null default “0”, 
   currentvalue decimal(12,2) not null default ‘0’, 
   boughtfrom varchar(20) not null default ‘ ‘, 
   instock tinyint(1) not null default ‘ ‘, 
   currentuser varchar(20) not null default ‘ ‘, 
   userphonenum varchar(13) not null default ‘ ‘, 
   boughtdate datatime not null default ‘ ‘ 
);

Again, I am new so there may be many errors. 

**EDIT:** 

Create table computer_inventory ( 
      assetnumber int(10) not null default 0,
     manufacturer varchar(15) not null default ‘ ‘, 
     originalcost decimal(12,2) not null default 0, 
     currentvalue decimal(12,2) not null default 0, 
     boughtfrom varchar(20) not null default ‘ ‘, 
     instock tinyint(1) not null default 0, 
     currentuser varchar(20) not null default ‘ ‘,
     userphonenum varchar(13) not null default ‘ ‘,
     boughtdate datetime not null default ‘0000-00=00’ 
 );

I am using MySQL 5.6. The error says "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 ‘ ‘ ‘, originalcost decimal(12,2) not null default 0, currentvalue decimal(1’ at line 2 "

Upvotes: 3

Views: 4717

Answers (1)

gloomy.penguin
gloomy.penguin

Reputation: 5911

There were a few things wrong... datetime was misspelled. Some of the default values were the wrong datatype for the column, too. 

Create table computer_inventory 
( 
   assetnumber int(10) not null default 0,
   manufacturer varchar(15) not null default '', 
   originalcost decimal(12,2) not null default 0, 
   currentvalue decimal(12,2) not null default 0, 
   boughtfrom varchar(20) not null default '', 
   instock tinyint(1) not null default 0, 
   currentuser varchar(20) not null default '', 
   userphonenum varchar(13) not null default '', 
   boughtdate datetime not null  default '0000-00-00'
);

Next time you have this kind of problem, here is what you can do: try making the table with just one or two columns first, if those lines don't have errors, drop the table and re-create it with a few more columns. If you get errors, this will make it easier to isolate exactly what part is incorrect. When I was testing this, I got errors about the wrong default values and stuff like that.... such as instock tinyint(1) not null default ' ', which doesn't work because you're trying to set an empty string to a integer column. It doesn't work. For the datetime default value, I had to look it up on google to see what the appropriate empty date value was. Some of it was trial and error, too, like removing the backticks and replacing them with normal quotes.

EDIT: This works for me without errors... I am testing it with sql fiddle and it is set for MySQL 5.5.31. Tell me the exact error you're getting (we can't help you unless you tell us what the error says, it's impossible for me to guess) and what DB you're using and its version.

This is based on your edit above. You typed 0000-00-00' as 0000-00=00' and you need to use a normal single quote instead of a backtick (I think?). 

Create table computer_inventory ( 
     assetnumber int(10) not null default 0,
     manufacturer varchar(15) not null default ' ', 
     originalcost decimal(12,2) not null default 0, 
     currentvalue decimal(12,2) not null default 0, 
     boughtfrom varchar(20) not null default ' ', 
     instock tinyint(1) not null default 0, 
     currentuser varchar(20) not null default ' ',
     userphonenum varchar(13) not null default ' ',
     boughtdate datetime not null default '0000-00-00'
 );

Upvotes: 1

Related Questions