Search code examples
sqloracle-databasecreate-table

"ORA-00984: column not allowed here" when attempting to create table using SQL

I am attempting to run this script to create a table, however I get a column not allowed error. After doing some research it seems that it could be that it could be a syntax error concerning values, however I am not inserting any values. 

CREATE TABLE SALESPERSON (
 sales_id     VARCHAR2(10) PRIMARY KEY,
 sales_fname  VARCHAR2(35) NOT NULL,
 sales_lname  VARCHAR2(35) NOT NULL,
 sales_email  VARCHAR2(35) NOT NULL,
 sales_region VARCHAR2(35) NOT NULL CHECK(sales_region IN ('NORTH','SOUTH','EAST','WEST')),
 sales_phone  CHAR(10)  NOT NULL,
 hire_date    DATE DEFAULT 01-JAN-2001 NOT NULL);

What am I overlooking?

Solution

  • I am getting a different error message, but probably due to the same mistake. You can't set the default to 01-JAN-2001. Perhaps just putting it in single quotes will fix it; better, to_date('01-JAN-2001', 'DD-MON-YYYY').