一点点的

• 语法无效（主键关键字的位置），
• 多余的外键关键字（您应该 * 行外 * 使用它们，而不是 * 行内 *），
• birth_date列的检查约束条件错误（日期怎么会大于18？），
• Oracle建议我们使用varchar2而不是varchar，
• number(50)的精度太高（也许您宁愿跳过它）。

修复后（使用虚拟主表）：

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

Table created.

员工姓名：

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>

作为检查员工年龄的触发器，以下是方法：

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>