SQL CREATE TABLE missing left parentheses error-CodePudding

CREATE TABLE residents
(
    R_ID NUMBER(4), CONSTRAINTS pk_residents_R_ID PRIMARY KEY,
    R_FN VARCHAR2(15), NOT NULL,
    R_LN VARCHAR2(15), NOT NULL,
    R_Contact NUMBER(10), NOT NULL,
    DoB DATE, NOT NULL
);

Tried a few changes but I'm unable to resolve this error. Any help would be appreciated!

CodePudding user response：

It works for Oracle in this way:

CREATE TABLE residents( 
        R_ID NUMBER(4), 
        R_FN VARCHAR2(15) NOT NULL, 
        R_LN VARCHAR2(15) NOT NULL, 
        R_Contact NUMBER(10) NOT NULL, 
        DoB DATE NOT NULL, 
        pk_residents_R_ID NUMBER(4) PRIMARY KEY 
        );

CodePudding user response：

Code which is the closest to your attempt is:

SQL> CREATE TABLE residents
  2  (
  3     r_id        NUMBER (4) CONSTRAINT pk_residents_r_id PRIMARY KEY,
  4     r_fn        VARCHAR2 (15) NOT NULL,
  5     r_ln        VARCHAR2 (15) NOT NULL,
  6     r_contact   NUMBER (10) NOT NULL,
  7     dob         DATE NOT NULL
  8  );

Table created.

SQL>

keyword is constraint, not constraintS
don't separate NOT NULL (or any other constraint) from its column with a comma

Also, using mixed letter case is irrelevant as Oracle - by default - stores object names in UPPERCASE. That's so unless you decide to enclose names into double quotes, but then you'll always have to use double quotes and exactly match letter case so - that's not what anyone should do, not in Oracle.