  CREATE TABLE "OEHR_CUSTOMERS" 
   (	"CUSTOMER_ID" NUMBER(6,0), 
	"CUST_FIRST_NAME" VARCHAR2(20) CONSTRAINT "CUST_FNAME_NN" NOT NULL ENABLE, 
	"CUST_LAST_NAME" VARCHAR2(20) CONSTRAINT "CUST_LNAME_NN" NOT NULL ENABLE, 
	"STREET_ADDRESS" VARCHAR2(40), 
	"POSTAL_CODE" VARCHAR2(10), 
	"CITY" VARCHAR2(30), 
	"STATE_PROVINCE" VARCHAR2(10), 
	"COUNTRY_ID" CHAR(2), 
	"PHONE_NUMBER" VARCHAR2(25), 
	"NLS_LANGUAGE" VARCHAR2(3), 
	"NLS_TERRITORY" VARCHAR2(30), 
	"CREDIT_LIMIT" NUMBER(9,2), 
	"CUST_EMAIL" VARCHAR2(30), 
	"ACCOUNT_MGR_ID" NUMBER(6,0)
   ) ;
  ALTER TABLE "OEHR_CUSTOMERS" ADD CONSTRAINT "OEHR_CUST_CL_MAX" CHECK (credit_limit <= 5000) ENABLE;
  ALTER TABLE "OEHR_CUSTOMERS" ADD CONSTRAINT "OEHR_CUSTOMER_ID_MIN" CHECK (customer_id > 0) ENABLE;
  ALTER TABLE "OEHR_CUSTOMERS" ADD CONSTRAINT "OEHR_CUSTOMERS_PK" PRIMARY KEY ("CUSTOMER_ID") ENABLE;
  ALTER TABLE "OEHR_CUSTOMERS" ADD CONSTRAINT "OEHR_CUST_ACCT_MGR_FK" FOREIGN KEY ("ACCOUNT_MGR_ID")
	  REFERENCES "OEHR_EMPLOYEES" ("EMPLOYEE_ID") ON DELETE SET NULL ENABLE;
   COMMENT ON COLUMN "OEHR_CUSTOMERS"."CUSTOMER_ID" IS 'Primary key column.';
   COMMENT ON COLUMN "OEHR_CUSTOMERS"."CUST_FIRST_NAME" IS 'NOT NULL constraint.';
   COMMENT ON COLUMN "OEHR_CUSTOMERS"."CUST_LAST_NAME" IS 'NOT NULL constraint.';
   COMMENT ON COLUMN "OEHR_CUSTOMERS"."STREET_ADDRESS" IS 'Modified column to maintain customer street address.';
   COMMENT ON COLUMN "OEHR_CUSTOMERS"."POSTAL_CODE" IS 'Modified column to maintain customer postal code.';
   COMMENT ON COLUMN "OEHR_CUSTOMERS"."CITY" IS 'Modified column to maintain customer city.';
   COMMENT ON COLUMN "OEHR_CUSTOMERS"."STATE_PROVINCE" IS 'Modified column to maintain customer state or province.';
   COMMENT ON COLUMN "OEHR_CUSTOMERS"."COUNTRY_ID" IS 'Modified column to maintain customer country id.';
   COMMENT ON COLUMN "OEHR_CUSTOMERS"."PHONE_NUMBER" IS 'Modified column to maintain customer phone number.';
   COMMENT ON COLUMN "OEHR_CUSTOMERS"."CREDIT_LIMIT" IS 'Check constraint.';
   COMMENT ON COLUMN "OEHR_CUSTOMERS"."ACCOUNT_MGR_ID" IS 'References oehr_employees.employee_id.';
   COMMENT ON TABLE "OEHR_CUSTOMERS"  IS 'Contains customers data either entered by an employee or by the customer him/herself over the Web.';
  CREATE INDEX "OEHR_CUST_ACCOUNT_MANAGER_IX" ON "OEHR_CUSTOMERS" ("ACCOUNT_MGR_ID") 
  ;
  CREATE INDEX "OEHR_CUST_EMAIL_IX" ON "OEHR_CUSTOMERS" ("CUST_EMAIL") 
  ;
  CREATE INDEX "OEHR_CUST_LNAME_IX" ON "OEHR_CUSTOMERS" ("CUST_LAST_NAME") 
  ;
  CREATE INDEX "OEHR_CUST_UPPER_NAME_IX" ON "OEHR_CUSTOMERS" (UPPER("CUST_LAST_NAME"), UPPER("CUST_FIRST_NAME")) 
  ;
  CREATE OR REPLACE TRIGGER "OEHR_CUST_PK" 
BEFORE insert on oehr_CUSTOMERS
for each row
DECLARE cust_id number;
begin
for c1 in (SELECT oehr_CUSTOMERS_SEQ.nextval cust_id  FROM dual) loop
  if :new.customer_id is null then 
   :new.CUSTOMER_ID := c1.cust_id;
  end if;
end loop;
end;
/
ALTER TRIGGER "OEHR_CUST_PK" ENABLE;
