Software

Ask something
Question Locked

SQL Syntax help

0 helpful
9 replies

by kentontator

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hey guys, does this line look correct? I am trying to create some tables and I have the .sql file but it gives me errors on this

id int NOT NULL auto_increment,

any problems? do I need to post more? I am not the best with sql, any help would be greatly appreciated!

9 replies

by kentontator

According to that site my syntax looks correct, here is what I have for a full table.
-------------------------------
CREATE TABLE mrbs_area
(
id int NOT NULL AUTO_INCREMENT,
area_name varchar(30),
area_admin_email text,
resolution int,
default_duration int,
morningstarts int,
morningstarts_minutes int,
eveningends int,
eveningends_minutes int,
private_enabled tinyint(1),
private_default tinyint(1),
private_mandatory tinyint(1),
private_override varchar(32),

PRIMARY KEY (id)
);

---------------------

If I execute that I get this error
-----
Msg 102, Level 15, State 1, Line 17
Incorrect syntax near 'AUTO_INCREMENT'.
-----

by mafergus

THis is exactly what on ran on my test box using the MYsql query browser and the command line. I removed the semi-colon since it doesn't need one for the gui. Try the sample and see if it works. Also check and make sure that table isn't out there

CREATE TABLE mrbs_area
(
id int NOT NULL AUTO_INCREMENT,
area_name varchar(30),
area_admin_email text,
resolution int,
default_duration int,
morningstarts int,
morningstarts_minutes int,
eveningends int,
eveningends_minutes int,
private_enabled tinyint(1),
private_default tinyint(1),
private_mandatory tinyint(1),
private_override varchar(32),
PRIMARY KEY (id)
)

by kentontator

I ran exactly that, still get the same error. I am just running the query through Microsoft SQL Server Management Studio. Am I not executing it properly?

by mafergus

You must be in a M$ SQL database. Try this

CREATE TABLE mrbs_area
(

Id int PRIMARY KEY IDENTITY,
area_name varchar(30),
area_admin_email text,
resolution int,
default_duration int,
morningstarts int,
morningstarts_minutes int,
eveningends int,
eveningends_minutes int,
private_enabled tinyint(1),
private_default tinyint(1),
private_mandatory tinyint(1),
private_override varchar(32),
);

by kentontator

I got further down! Only now its telling me this

---------
Msg 2716, Level 16, State 1, Line 1
Column, parameter, or variable #10: Cannot specify a column width on data type tinyint.
----------
should i take the values out?

by kentontator

changing the auto_increment syntax works great! how about this though

-------------
CREATE TABLE mrbs_entry
(
id int PRIMARY KEY IDENTITY,
start_time int DEFAULT '0' NOT NULL,
end_time int DEFAULT '0' NOT NULL,
entry_type int DEFAULT '0' NOT NULL,
repeat_id int DEFAULT '0' NOT NULL,
room_id int DEFAULT '1' NOT NULL,
timestamp timestamp,
create_by varchar(80) DEFAULT '' NOT NULL,
name varchar(80) DEFAULT '' NOT NULL,
type char DEFAULT 'E' NOT NULL,
description text,
private TINYINT(1) NOT NULL DEFAULT 0,

PRIMARY KEY (id),
KEY idxStartTime (start_time),
KEY idxEndTime (end_time)
);
---------------

error

------------
Msg 156, Level 15, State 1, Line 45
Incorrect syntax near the keyword 'KEY'.
-----------

This discussion is closed to new replies.