Informix - Cant find syntax error

Question

This may be a stupid mistake but I'm new to Informix and I can't seem to figure out why none of my CREATE TABLE statements won't run. I keep getting a syntax error on all of my CREATE TABLE statements.

CREATE TABLE customer(
store_num INTEGER NOT NULL,
store_name VARCHAR(20) NOT NULL,
addr VARCHAR(20),
addr2 VARCHAR(20),
city VARCHAR(15),
state VARCHAR(2),
zip-code VARCHAR(5),
contact_name VARCHAR(30),
phone VARCHAR(18),
CONSTRAINT cust_pk PRIMARY KEY(store_num)
);

create table orders(
order_num INTEGER NOT NULL,
order_date DATE NOT NULL,
store_num INTEGER NOT NULL,
fac_code CHAR(3),
ship_instr CHAR(10),
promo CHAR(1) NOT NULL,
CONSTRAINT orders_pk PRIMARY KEY(order_num)
); 

create table factory(
fac_code CHAR(3) NOT NULL,
fac_name CHAR(15) NOT NULL,
CONSTRAINT fac_pk PRIMARY KEY(fac_code)
);

create table stock(
stock_num INTEGER NOT NULL,
fac_code CHAR(3) NOT NULL,
description CHAR(15) NOT NULL,
reg_price DECIMAL(8,2) NOT NULL,
promo_price DECIMAL(8,2),
price_updated DATE,
unit CHAR(4) NOT NULL,
CONSTRAINT stock_pk PRIMARY KEY(stock_num)
);

create table items(
order_num INTEGER NOT NULL,
stock_num INTEGER NOT NULL,
quantity SMALLINT NOT NULL,
price DECIMAL(8,2) NOT NULL,
CONSTRAINT items_pk PRIMARY KEY(order_num, stock_num)
);

create table state(
state_code CHAR(2) NOT NULL,
state_name CHAR(15) NOT NULL,
CONSTRAINT state_pk PRIMARY KEY(state_code)
);

Any help will be appreciated.

I think the error on table `customer` `zip-code` instead of `zip_code` — D-Shih, Feb 12 '18 at 08:10

D-Shih · Answer 1 · 2018-02-12T08:14:05.137

2

You can try customer's field zip_code instead of zip-code

edited Feb 12 '18 at 08:14

answered Feb 12 '18 at 08:12

D-Shih

44,943
6
31
51

That didn't fix it – ScottyDooZA Feb 12 '18 at 08:16
@ScottyDooZA: Fixing the spelling `zip-code` to `zip_code` should have changed the error message for the first table. See my answer for the rest of the trouble. – Jonathan Leffler Feb 12 '18 at 08:21
@Jonathan Leffler & Daniel That did fix one of the issues, I just had an unnecessary comma that I added so I didn't see that the fix worked. I also had an issue with my constraint which gave me a syntax error – ScottyDooZA Feb 12 '18 at 08:25

score 2 · Accepted Answer · answered Feb 12 '18 at 08:20

For reasons I don't understand, Informix requires the constraint name after the constraint, whereas standard SQL requires the constraint name before the constraint.

Thus, in addition to changing zip-code to zip_code (as pointed out by daniel.shih in an answer), you need:

CREATE TABLE customer(
    store_num     INTEGER NOT NULL,
    store_name    VARCHAR(20) NOT NULL,
    addr          VARCHAR(20),
    addr2         VARCHAR(20),
    city          VARCHAR(15),
    state         VARCHAR(2),
    zip_code      VARCHAR(5),
    contact_name  VARCHAR(30),
    phone         VARCHAR(18),
    PRIMARY KEY(store_num) CONSTRAINT cust_pk
);

Informix - Cant find syntax error

2 Answers2