I have been getting error "ORA-00907: missing right parenthesis" when I run this create table statement:

create table employee(
  primary key(emp_id number(20)),
  emp_name varchar(30),
  birth_date date CHECK(birth_date>18),
  gender varchar(10),
  dept_no number(20)
    CONSTRAINT fk FOREIGN KEY(dept_no)
    REFERENCES department(dept_no),
  address varchar(50),
  designation varchar(20)
    CHECK(designation IN('manager', 'clerk', 'leader', 'analyst', 'designer', 'coder','tester')),
  salary number(50)
    CHECK(salary>0),
  experience number(2),
  email_id varchar(30)
    CONSTRAINT chk_email
    CHECK (REGEXP_LIKE(email_id,'^[A-Za-z0-9_.] @[A-Za-z] \.[A-Za-z]{2,4}$'))
);

I have looked up the exact syntax and after checking many times, everything seems to be just perfect but the error still exists. What is wrong?

CodePudding user response：

A little bit of

• invalid syntax (position of the primary key keywords),
• superfluous foreign key keywords (you'd use them out of line, not inline),
• check constraint for the birth_date column is wrong (how can date be larger than 18?),
• Oracle suggests us to use varchar2 instead of varchar,
• number(50) has too large precision (perhaps you'd rather just skip it).

Once fixed (with a dummy master table):

SQL> CREATE TABLE department
  2  (
  3     dept_no   NUMBER PRIMARY KEY
  4  );

Table created.

Employee:

SQL> CREATE TABLE employee
  2  (
  3     emp_id        NUMBER (20) PRIMARY KEY,
  4     emp_name      VARCHAR2 (30),
  5     birth_date    DATE,
  6     gender        VARCHAR2 (10),
  7     dept_no       NUMBER CONSTRAINT fk_emp_dept REFERENCES department (dept_no),
  8     address       VARCHAR2 (50),
  9     designation   VARCHAR2 (20)
 10                     CHECK
 11                        (designation IN ('manager',
 12                                         'clerk',
 13                                         'leader',
 14                                         'analyst',
 15                                         'designer',
 16                                         'coder',
 17                                         'tester')),
 18     salary        NUMBER CHECK (salary > 0),
 19     experience    NUMBER (2),
 20     email_id      VARCHAR2 (30)
 21                     CONSTRAINT chk_email CHECK
 22                        (REGEXP_LIKE (
 23                            email_id,
 24                            '^[A-Za-z0-9_.] @[A-Za-z] \.[A-Za-z]{2,4}$'))
 25  );

Table created.

SQL>

As of a trigger that checks employee's age, here's how:

SQL> CREATE OR REPLACE TRIGGER trg_bi_emp
  2     BEFORE INSERT
  3     ON employee
  4     FOR EACH ROW
  5  BEGIN
  6     IF MONTHS_BETWEEN (SYSDATE, :new.birth_date) < 18 * 12
  7     THEN
  8        raise_application_error (-20000,
  9                                 'Too young; must be at least 18 years of age');
 10     END IF;
 11  END;
 12  /

Trigger created.

SQL> INSERT INTO employee (emp_id, birth_date) VALUES (1, DATE '2020-07-25');
INSERT INTO employee (emp_id, birth_date) VALUES (1, DATE '2020-07-25')
            *
ERROR at line 1:
ORA-20000: Too young; must be at least 18 years of age
ORA-06512: at "SCOTT.TRG_BI_EMP", line 4
ORA-04088: error during execution of trigger 'SCOTT.TRG_BI_EMP'


SQL> INSERT INTO employee (emp_id, birth_date) VALUES (1, DATE '1997-07-25');

1 row created.

SQL>