Oracle SQL Create Table Issue

Question

When I try to create a test table with some test rows, SQL Plus gives me the following error:

    SQL> CREATE TABLE test_table
  2  (
  3  test_data0 NUMBER(3) CONSTRAINT test_data0_pk PRIMARY KEY,
  4  test_data1 NUMBER(3) CONSTRAINT test_data1_fk REFERENCES test_table2, CONSTRAINT test_data1_condition NOT NULL,
  5  test_data2 DATE CONSTRAINT test_data2_condition NOT NULL,
  6  test_data3 NUMBER(2),
  7  test_data4 NUMBER(2) DEFAULT 0
  8  );
test_data1      NUMBER(3) CONSTRAINT test_data1_fk REFERENCES test_table2, C
ONSTRAINT test_data1_condition NOT NULL,

                               *
ERROR at line 4:
ORA-00904: : invalid identifier

If I only put one of the two constraints on line 4 I don't get any error. What is the problem?

Answer 1

You don't need the constraint key word for inline constraints. Of course, you can use it. But, in particular, not null is usually expressed without a constraint. So:

CREATE TABLE test_table (
  test_data0 NUMBER(3) CONSTRAINT test_data0_pk PRIMARY KEY,
  test_data1 NUMBER(3) NOT NULL CONSTRAINT test_data1_fk REFERENCES test_table2,
  test_data2 DATE NOT NULL,
  test_data3 NUMBER(2),
  test_data4 NUMBER(2) DEFAULT 0
);

If you do use constraint more than once, you need to leave out the comma. That is the error you are getting. So that line would be:

test_data1 NUMBER(3) CONSTRAINT test_data1_fk REFERENCES test_table2
                     CONSTRAINT test_data1_condition NOT NULL,

I often leave out the constraint itself for the inline constraints:

CREATE TABLE test_table (
  test_data0 NUMBER(3) PRIMARY KEY,
  test_data1 NUMBER(3) NOT NULL REFERENCES test_table2,
  test_data2 DATE NOT NULL,
  test_data3 NUMBER(2),
  test_data4 NUMBER(2) DEFAULT 0
);

Admittedly, you lose the name of the constraint when violated.