Missing right parenthesis after (or before) default 0, Oracle

Question

I am trying to create a table with Oracle SQL where two columns of the table should have a default value and should not be NULL.

I am getting the error you can see below, but i am not able to understand it, because the are no parenthesis to be closed there!

The Script

 CREATE TABLE ENCUENTROS (
  elocal constraint clave_extrana_equipos references equipos(code) not null enable,
  evisitante constraint clave_extrana_equipos references equipos(code) not null enable,
  fecha date,
  plocal number constraint plocal_mayor_cero check (plocal > 0) default 0 not null enable,
  pvisitante number constraint pvisitante_mayor_cero check (pvisitante > 0) default 0 not null enable);

The Error Message

plocal number constraint plocal_mayor_cero check (plocal > 0) default 0 not null enable,
                                                              *
ERROR at line 5:
ORA-00907: missing right parenthesis

^{* is where Oracle detected the error}

I'm at loss as to why there should be an error at that position.

Answer 1

The problem is with the ordering of the clauses. If you move the CHECK constraint after the DEFAULT 0 clause, then the table creates:

CREATE TABLE ENCUENTROS (
  elocal constraint clave_extrana_equipos_1 references equipos(code) not null enable,
  evisitante constraint clave_extrana_equipos_2 references equipos(code) not null enable,
  fecha date,
  plocal number default 0 not null enable constraint plocal_mayor_cero check (plocal > 0),
  pvisitante number default 0 not null enable constraint pvisitante_mayor_cero check (pvisitante > 0)
);

or

CREATE TABLE ENCUENTROS (
  elocal constraint clave_extrana_equipos_1 references equipos(code) not null enable,
  evisitante constraint clave_extrana_equipos_2 references equipos(code) not null enable,
  fecha date,
  plocal number default 0 constraint plocal_mayor_cero check (plocal > 0) not null enable,
  pvisitante number default 0 constraint pvisitante_mayor_cero check (pvisitante > 0) not null enable 
);

Note that I had to give different names to the two foreign key constraints.

BTW, there is a bit of a oddity in your definition: you require a value for local and pvisitante and if none is passed, then you use 0. But the check contraint requires the value to be >0. Meaning that any insert that does not provide a value will fail ... The error will be something like constraint pvisitante_mayor_cero failed validation or similar.

Maybe a simpler approach would be to remove the default clause. This will then make an insert that does not include pvisitante fail with a different error: cannot insert null into ENCUENTROS (PVISITANTE). That may be clearer to application developers.