ORA-00907: missing right parenthesis, all parenthesis have been closed but still not working

Question

I want to create two tables Publisher and Book. Here are the SQL statements for creating the tables.

I want to create a foreign key constraint in the Book table. I'm not sure why it's throwing this error. I cross-checked all the parenthesis. The syntax seems to be fine but it's not creating the table.

CREATE TABLE Publisher
(
    pub_name varchar2(128) PRIMARY KEY, 
    phone integer, 
    address varchar2(20)
);


CREATE TABLE Book
(
    book_id integer NOT NULL PRIMARY KEY,
    title varchar2(256),
    pub_year number(4) UNSIGNED,
    pub_name REFERENCES Publisher(pub_name) ON DELETE CASCADE ON UPDATE CASCADE
);

Answer 1

In Oracle, there's no UNSIGNED nor ON UPDATE CASCADE.

SQL> CREATE TABLE Publisher(
  2  pub_name varchar2(128) PRIMARY KEY,
  3  phone    integer,
  4  address  varchar2(20));

Table created.

SQL> CREATE TABLE Book(
  2  book_id  integer NOT NULL PRIMARY KEY,
  3  title    varchar2(256),
  4  pub_year number(4),
  5  pub_name REFERENCES Publisher(pub_name) ON DELETE CASCADE);

Table created.

SQL>

Answer 2

One error is the on update cascade. However, this is a second error:

pub_year number(4) UNSIGNED,

Because unsigned isn't allowed here, you can use a constraint instead:

pub_year number(4) check (pub_year > 0),

That said, you probably want a more specific constraint, something like:

pub_year number(4) check (pub_year > 1950 and pub_year < 2100),

Note that this will represent the year as an integer (there is scale on the number definition so the default is 0). If you attempt to insert a number like 2020.20, then the value will be truncated -- rather than generating an error.