CODEWITHSUNDEEP
sqlms-access
nmahesh
nmahesh

Reputation: 43

Have two CREATE TABLE statements and the second one keeps giving me a syntax error not sure whats wrong

CREATE TABLE Customer
(
    customerID INT PRIMARY KEY,
    customerName VARCHAR(50),
    customerAddress VARCHAR(200) NOT NULL UNIQUE,
    customerCity VARCHAR(50),
    customerState CHAR(2),
    customerPostalCode CHAR(5)
);

CREATE TABLE Order
(
    orderID INT PRIMARY KEY
);

The first table runs fine but the second one keeps giving me a syntax error, I feel like its a simple error but I'm new to SQL and cant figure it out.

Upvotes: 0

Views: 68

Answers (4)

Gustav
Gustav

Reputation: 55806

The simple answer is, that Access SQL only supports creating of one table per call.

Thus, run this in two calls, first:

CREATE TABLE Customer
(
    customerID INT PRIMARY KEY,
    customerName VARCHAR(50),
    customerAddress VARCHAR(200) NOT NULL UNIQUE,
    customerCity VARCHAR(50),
    customerState CHAR(2),
    customerPostalCode CHAR(5)
);

and then:

CREATE TABLE Order
(
    orderID INT PRIMARY KEY
);

Upvotes: 0

Enzo Zordan
Enzo Zordan

Reputation: 1

If you "quote" the table name it should work. The default quotes in SQL server are square brackets: []

CREATE TABLE [dbo].[Order]([orderID] INT PRIMARY KEY);

Upvotes: 0

Lee Mac
Lee Mac

Reputation: 16015

Order is a reserved word in SQL (used for sorting) therefore, you'll need to surround it with backquotes to use it as a literal, e.g.:

CREATE TABLE `Order` (orderID INT PRIMARY KEY);

But it's not advisable to name tables (or any objects) using reserved words, as it will likely cause headaches later on.

Upvotes: 4

ishando
ishando

Reputation: 316

'order' is probably a reserved word so not a valid table name

Upvotes: 1

Related Questions