CODEWITHSUNDEEP
sqloracle-databasecreate-table
Maxim
Maxim

Reputation: 115

Oracle SQL script giving error. I try to create table

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?

Upvotes: 1

Views: 131

Answers (1)

GMB
GMB

Reputation: 222402

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

Upvotes: 1

Related Questions