CODEWITHSUNDEEP
oracle-database
Maria
Maria

Reputation: 25

Missing parentheses in CREATE TABLE instruction

CREATE TABLE MAJEST_PROD_2015(
  PRODUCT_ID CHAR(10) NOT NULL,
  DESCRIPTION_PROD CHAR(30),
  SEW_DATE DATE,
  HARVEST_DATE DATE,
  QUANTITY INT,
  PROD_RATING INT(1),
  PRIMARY KEY (PRODUCT_ID)
);

Error report -

SQL Error: ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis" *Cause:
*Action:

Upvotes: 1

Views: 184

Answers (2)

MT0
MT0

Reputation: 168041

As already noted INT does not take a length constraint in Oracle - instead you could use NUMBER(1,0) which would restrict it to a single digit (-9 .. +9) and then you can further restrict it using a CHECK constraint.

CHAR(n) will also right pad the value with space (CHR(32)) characters so that it always contains the maximum number of characters. If you did not intend this then you should be using VARCHAR2(n) instead.

You also do not need a NOT NULL constraint on a column that is the PRIMARY KEY.

CREATE TABLE MAJEST_PROD_2015(
  PRODUCT_ID       VARCHAR2(10)  CONSTRAINT MAJEST_PROD_2015__PROD_ID__PK PRIMARY KEY,
  DESCRIPTION_PROD VARCHAR2(30),
  SEW_DATE         DATE,
  HARVEST_DATE     DATE,
  QUANTITY         INT,
  PROD_RATING      NUMBER(1,0)   CONSTRAINT MAJEST_PROD_2015__PROD_RAT__CK CHECK ( PROD_RATING BETWEEN 1 AND 5 )
);

(also, should it be SEW_DATE or SOW_DATE? Since the next line talks about harvest then I would have thought "sow" was more apt.)

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175756

Oracle is not MySQL so there is no INT(1) type:

CREATE TABLE MAJEST_PROD_2015(
   PRODUCT_ID CHAR(10) NOT NULL,
   DESCRIPTION_PROD CHAR(30),
   SEW_DATE DATE,
   HARVEST_DATE DATE,
   QUANTITY INT,
   PROD_RATING INT,            -- here
   PRIMARY KEY (PRODUCT_ID) 
);

SqlFiddleDemo

If you don't need constant size of string, consider using VARCHAR2(30).

EDIT:

but i need those values to be between 1-5

So add check constraint:

CONSTRAINT chk_PROD_RATING CHECK (PROD_RATING BETWEEN 1 AND 5),

SqlFiddleDemo2

Upvotes: 1

Related Questions