Missing right parenthesis after (or before) default 0, Oracle 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 &gt; 0) default 0 not null enable,
  pvisitante number constraint pvisitante_mayor_cero check (pvisitante &gt; 0) default 0 not null enable);


The Error Message


plocal number constraint plocal_mayor_cero check (plocal &gt; 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.
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 &gt; 0),
  pvisitante number default 0 not null enable constraint pvisitante_mayor_cero check (pvisitante &gt; 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 &gt; 0) not null enable,
  pvisitante number default 0 constraint pvisitante_mayor_cero check (pvisitante &gt; 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 &gt;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.