SQL错误:ORA-00907:缺少右括号挣扎 [英] SQL Error: ORA-00907: missing right parenthesis struggling

问题描述

CREATE TABLE ADMIN (
    A_EMP_ID                   CHAR 5 BYTE                            NOT NULL,
    ADMIN_START_DATE           DATE                 DEFAULT SYSDATE       NOT NULL,
    ADMIN_END_DATE             DATE                                       NULL,
    DIVERSITY_TRAINING_CERT      CHAR(1 BYTE)       DEFAULT 'N'           NOT NULL,
    ADMIN_TITLE                  CHAR(40 BYTE)                            NULL,
    CONSTRAINT                   ADMIN_PK           PRIMARY KEY(A_EMP_ID),
  CONSTRAINT        ADMIN_FK1          
  FOREIGN KEY(A_EMP_ID)
  REFERENCES ADMIN(A_EMP_ID),
  CONSTRAINT                 ADMIN_DIVERSITY_CERT
      CHECK (DIVERSITY_TRAINING_CERT = 'N','Y'),
  CONSTRAINT                 ADMIN_END_DATE
      CHECK (<= 'ADMIN_START_DATE'),
    );



Error starting at line : 1 in command -
CREATE TABLE ADMIN (
    A_EMP_ID                   CHAR 5 BYTE                            NOT NULL,
    ADMIN_START_DATE           DATE                 DEFAULT SYSDATE       NOT NULL,
    ADMIN_END_DATE             DATE                                       NULL,
    DIVERSITY_TRAINING_CERT      CHAR(1 BYTE)       DEFAULT 'N'           NOT NULL,
    ADMIN_TITLE                  CHAR(40 BYTE)                            NULL,
    CONSTRAINT                   ADMIN_PK           PRIMARY KEY(A_EMP_ID),
  CONSTRAINT        ADMIN_FK1          
  FOREIGN KEY(A_EMP_ID)
  REFERENCES ADMIN(A_EMP_ID),
  CONSTRAINT                 ADMIN_DIVERSITY_CERT
      CHECK (DIVERSITY_TRAINING_CERT = 'N','Y'),
  CONSTRAINT                 ADMIN_END_DATE
      CHECK (<= 'ADMIN_START_DATE'),
    )
Error report -
SQL Error: ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"
*Cause:    
*Action:

顶部是我的代码，当我运行它时，我从下半部分收到消息.我想这与我的外键有关，但是我不知道确切的解决方案是什么.任何帮助将不胜感激.

The top part is my code and when I run it, I get the message from the bottom half. I'm thinking it's something to do with my foreign key, but I don't know what the exact solution is. Any help is greatly appreciated.

推荐答案

您的陈述有很多错误.

• A_EMP_ID CHAR 5 BYTE在长度限制附近缺少(..)
• 您绝对不希望将CHAR用于admin_title.请使用VARCHAR2.
• DIVERSITY_TRAINING_CERT = 'N','Y'是无效的表达式.您可能想要diversity_training_cert IN ('N','Y')
• 虽然FOREIGN KEY (a_emp_id) REFERENCES admin(a_emp_id)在语法上是正确的，但这没有任何意义.我猜你想要一个manager_id或类似的东西.然后像FOREIGN KEY (manager_id) REFERENCES admin(a_emp_id)这样的东西.
  或者，您可能打算引用employee表.在这种情况下，a_emp_id数据类型必须与该表的PK列的类型匹配.

• CONSTRAINT ADMIN_END_DATE CHECK (<= 'ADMIN_START_DATE'),出现三个错误:

• A_EMP_ID CHAR 5 BYTE is missing the (..) around the length constraint
• You most definitely do not want to use CHAR for the admin_title. UseVARCHAR2 instead.
• DIVERSITY_TRAINING_CERT = 'N','Y' is not a valid expression. You probably want diversity_training_cert IN ('N','Y')
• While FOREIGN KEY (a_emp_id) REFERENCES admin(a_emp_id) is syntactically correct, it doesn't makes sense. I guess you want a manager_id or something similar. And then something like FOREIGN KEY (manager_id) REFERENCES admin(a_emp_id).
  Alternatively you maybe intended to reference an employee table. In that case the a_emp_id data type must match the type of PK column that table.

• CONSTRAINT ADMIN_END_DATE CHECK (<= 'ADMIN_START_DATE'), has three errors:

• 一栏不能用单引号引起来.所以它必须是admin_start_date而不是'admin_start_date'
检查约束要求适当的条件. <= admin_start_date不是条件，您需要将其与某些内容进行比较.可推测的admin_end_date
• 在该表达式后有一个逗号,，这也是错误的.

• a column must not be enclosed in single quotes. So it has to be admin_start_date not 'admin_start_date'
• a check constraint requires a proper condition. <= admin_start_date is not a condition, you need to compare the column it with something. Presumable admin_end_date
• you have a comma , after that expression which is wrong as well.

将所有内容放在一起:

CREATE TABLE admin 
(
    a_emp_id                  CHAR(5 BYTE) NOT NULL, 
    admin_start_date          DATE DEFAULT SYSDATE NOT NULL,
    admin_end_date            DATE NULL,
    diversity_training_cert   CHAR(1 BYTE)  DEFAULT 'N' NOT NULL,
    admin_title               VARCHAR2(40 BYTE) NULL,
    CONSTRAINT admin_pk 
        PRIMARY KEY(a_emp_id),
    CONSTRAINT admin_fk1 
        FOREIGN KEY (a_emp_id) REFERENCES admin(a_emp_id),
    CONSTRAINT admin_diversity_cert 
        CHECK (diversity_training_cert IN ('N','Y')), 
    CONSTRAINT admin_end_date 
        CHECK ( admin_end_date <= admin_start_date) 
);

---

无关，但是:绝对也不需要大写所有内容.

---

Unrelated, but: there is also absolutely no need to write everything in upper case.

这篇关于SQL错误:ORA-00907:缺少右括号挣扎的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！