I'm getting this error message: `ORA-00933: SQL command not properly ended` and was hoping some-CodePudding

Here's the code that's causing the issue:

DROP TABLE customers;

--1 create tables
CREATE TABLE customers (
  customer_id NUMBER(10),
  last_name VARCHAR2(25),
  first_name VARCHAR2(25), 
  home_phone VARCHAR2(12),
  address VARCHAR2(100),
  city VARCHAR2(30),
  state VARCHAR2(2),
  email VARCHAR2(25),
  cell_phone VARCHAR2(12),
  CONSTRAINT pk_customer_customer_id PRIMARY KEY (customer_id),
  CONSTRAINT not_null_customer_last_name NOT NULL (last_name),
  CONSTRAINT not_null_customer_first_name NOT NULL (first_name),
  CONSTRAINT not_null_customer_home_phone NOT NULL (home_phone), 
  CONSTRAINT not_null_customer_address NOT NULL (address), 
  CONSTRAINT not_null_customer_city NOT NULL (city), 
  CONSTRAINT not_null_customer_state NOT NULL (state)
);

I've tried formatting the constraints at the column level and that didn't seem to help. I'm an absolute beginner and am doing this for a class so I'm sure it's something simple and silly but I couldn't figure it out for the life of me. After a few hours of frustration I figured I'd see if there was someone out there who could point me in the right direction.

CodePudding user response：

I think the problem is the way you are defining the NOT NULL constraints, they are CHECK constraints so you should use:

CREATE TABLE customers (
  customer_id NUMBER(10),
  last_name VARCHAR2(25),
  first_name VARCHAR2(25), 
  home_phone VARCHAR2(12),
  address VARCHAR2(100),
  city VARCHAR2(30),
  state VARCHAR2(2),
  email VARCHAR2(25),
  cell_phone VARCHAR2(12),
  CONSTRAINT pk_customer_customer_id PRIMARY KEY (customer_id),
  CONSTRAINT not_null_customer_last_name CHECK (last_name NOT NULL),
  CONSTRAINT not_null_customer_first_name CHECK (first_name NOT NULL),
  CONSTRAINT not_null_customer_home_phone CHECK (home_phone NOT NULL), 
  CONSTRAINT not_null_customer_address CHECK (address NOT NULL), 
  CONSTRAINT not_null_customer_city CHECK (city NOT NULL), 
  CONSTRAINT not_null_customer_state CHECK (state NOT NULL)
);

CodePudding user response：

NOT NULL constraints are defined in-line; there is no option to define them out-of-line (apart from using a CHECK constraint).

From the CREATE TABLE documentation:

inline_constraint::=

out_of_line_constraint::=

Therefore, if you want to use a NOT NULL constraint then you need to declare it inline:

CREATE TABLE customers (
  customer_id NUMBER(10),
  last_name   VARCHAR2(25)
              CONSTRAINT not_null_customer_last_name NOT NULL,
  first_name  VARCHAR2(25)
              CONSTRAINT not_null_customer_first_name NOT NULL, 
  home_phone  VARCHAR2(12)
              CONSTRAINT not_null_customer_home_phone NOT NULL,
  address     VARCHAR2(100)
              CONSTRAINT not_null_customer_address NOT NULL,
  city        VARCHAR2(30)
              CONSTRAINT not_null_customer_city NOT NULL,
  state       VARCHAR2(2)
              CONSTRAINT not_null_customer_state NOT NULL,
  email       VARCHAR2(25),
  cell_phone  VARCHAR2(12),
  CONSTRAINT pk_customer_customer_id PRIMARY KEY (customer_id)
);

`inline_constraint::=`

`out_of_line_constraint::=`