Oracle SQL script giving error. I try to create table

Question

I have script in Oracle SQL:

CREATE TABLE "CONNECTIONS_DB"
("USER" VARCHAR2(4) NOT NULL,
 "TIME" DATE NOT NULL,
 "STATUS" BOOLEAN NOT NULL,
 CONSTRAINT "CONNECTIONS_DB_PK" PRIMARY KEY ("USER", "TIME", "STATUS"),
);

and it returns next error:

Error starting at line : 616 in command -
CREATE TABLE "CONNECTIONS_DB"
("USER" VARCHAR2(4) NOT NULL,
 "TIME" DATE NOT NULL,
 "STATUS" BOOLEAN NOT NULL,
 CONSTRAINT "CONNECTIONS_DB_PK" PRIMARY KEY ("USER", "TIME", "STATUS"),
)
Error report -
ORA-00904: : недопустимый идентификатор
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

How to fix this?

GMB · Accepted Answer · 2019-09-27 21:45:35Z

1

Problems with your code:

1) there is a trailing comma at the end of the line that declares the CONSTRAINT

2) datatype BOOLEAN does not exists in Oracle; you can use NUMBER(1) instead

Consider:

CREATE TABLE "CONNECTIONS_DB" (
    "USER" VARCHAR2(4) NOT NULL,
    "TIME" DATE NOT NULL,
    "STATUS" NUMBER(1) NOT NULL,
    CONSTRAINT CONNECTIONS_DB_PK PRIMARY KEY ("USER", "TIME", "STATUS")
);

Side note: USER is a reserved word in Oracle. I would suggest using another identifier (like USERNAME), and removing the double quotes around the identifiers. This will save you the work of double quoting that column in all further queries:

CREATE TABLE CONNECTIONS_DB (
    USERNAME VARCHAR2(4) NOT NULL,
    TIME DATE NOT NULL,
    STATUS NUMBER(1) NOT NULL,
    CONSTRAINT CONNECTIONS_DB_PK PRIMARY KEY (USERNAME, TIME, STATUS)
);

Demo on DB Fiddle

edited Sep 27, 2019 at 21:45

answered Sep 27, 2019 at 21:40

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle SQL script giving error. I try to create table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related