I am working with PostgreSQL for the first time. I have this statement:
CREATE TABLE IF NOT EXISTS sequences (
SEQU_NK SERIAL PRIMARY KEY NOT NULL UNIQUE,
NOM_SEQU varchar(30) NOT NULL,
PROCHAIN bigint NOT NULL,
PRIMARY KEY (SEQU_NK),
UNIQUE KEY IDX_SEQU_NOM (NOM_SEQU)
)
When I run it I get:
ERROR: syntax error at or near "KEY" LINE 6: UNIQUE KEY IDX_SEQU_NOM (NOM_SEQU) ^ ********** Error ********** ERROR: syntax error at or near "KEY"
Try like this:
CREATE TABLE IF NOT EXISTS sequences (
SEQU_NK SERIAL PRIMARY KEY NOT NULL UNIQUE,
NOM_SEQU varchar(30) NOT NULL,
PROCHAIN bigint NOT NULL,
CONSTRAINT "IDX_SEQU_NOM" UNIQUE (NOM_SEQU)
)
PRIMARY KEY (SEQU_NK) is not required) 2) The syntax to create the UNIQUE constraint was not correct. Hope this helps :)
CREATE TABLE sequences (
sequ_nk serial PRIMARY KEY,
nom_sequ varchar(30) NOT NULL UNIQUE,
prochain bigint NOT NULL
);
It's UNIQUE , not UNIQUE KEY
There can only be one PRIMARY KEY.
A PRIMARY KEY column is UNIQUE and NOT NULL automatically.
Read the manual.
Or, better yet, read the manual for your actual Postgres version 8.4 - which tells you that IF NOT EXISTS
Or, better still, upgrade to a current version of Postgres. Version 8.4 has reached EOL last year.
Aside: Don't use quoted upper-case identifiers, or you have to keep quoting them, always.
