SQL Oracle 表创建错误 ORA-00904 无效标识符

Oracle table create error ORA-00904 invalid identifier

提问by Zbarcea Christian

It's very interesting I don't know why I'm getting ORA-00904 invalid identifier when I'm trying to create a table with oracle.

很有趣，我不知道为什么在尝试使用 oracle 创建表时会收到 ORA-00904 无效标识符。

CREATE TABLE animals
(
   CONSTRAINT animal_id NUMBER(6) PRIMARY_KEY,
   name VARCHAR2(25),
   CONSTRAINT license_tag_number NUMBER(10) UNIQUE,
   admit_date DATE NOT NULL,
   adoption_id NUMBER(5),
   vaccination_date DATE NOT NULL
);

回答by Yasir Arsanukaev

When creating tables with CREATE TABLEin Oracle, you have at least four ways to specify constraints.

CREATE TABLE在 Oracle 中创建表时，您至少有四种方法可以指定约束。

In-line specification

在线规格

CREATE TABLE animals
(
   animal_id NUMBER(6) PRIMARY KEY,
   name VARCHAR2(25),
   license_tag_number NUMBER(10) UNIQUE,
   admit_date DATE NOT NULL,
   adoption_id NUMBER(5),
   vaccination_date DATE NOT NULL
);

In-line specification with explicit constraints' names

具有显式约束名称的内嵌规范

CREATE TABLE animals
(
   animal_id NUMBER(6) CONSTRAINT animal_id_pk PRIMARY KEY,
   name VARCHAR2(25),
   license_tag_number NUMBER(10) CONSTRAINT animal_tag_no_uq UNIQUE,
   admit_date DATE NOT NULL,
   adoption_id NUMBER(5),
   vaccination_date DATE NOT NULL
);

Out-line specification

外形规格

CREATE TABLE animals
(
   animal_id NUMBER(6) ,
   name VARCHAR2(25),
   license_tag_number NUMBER(10),
   admit_date DATE NOT NULL,
   adoption_id NUMBER(5),
   vaccination_date DATE NOT NULL,
   PRIMARY KEY (animal_id),
   UNIQUE (license_tag_number)
);

Out-line specification with explicit constraints' names

带有显式约束名称的大纲规范

CREATE TABLE animals
(
   animal_id NUMBER(6) ,
   name VARCHAR2(25),
   license_tag_number NUMBER(10),
   admit_date DATE NOT NULL,
   adoption_id NUMBER(5),
   vaccination_date DATE NOT NULL,
   CONSTRAINT animal_id_pk PRIMARY KEY (animal_id),
   CONSTRAINT animal_tag_no_uq UNIQUE (license_tag_number)
);

If you don't explicitly specify constraints names, they are generated automatically by the system, and read something like SYS_C0013321. I find the last way the most readable, because you see which constraints are created, and can manage them using user-friendly names (e. g. using view user_constraints).

如果您没有明确指定约束名称，它们将由系统自动生成，并读取类似SYS_C0013321. 我发现最后一种方式最易读，因为您可以看到创建了哪些约束，并且可以使用用户友好的名称（例如使用 view user_constraints）管理它们。

By the way, there's a typo in your code: you should use PRIMARY KEYinstead of PRIMARY_KEY.

顺便说一句，您的代码中有一个错字：您应该使用PRIMARY KEY而不是PRIMARY_KEY.

回答by Robert

This is correct code, you must remove CONSTRAINTword:

这是正确的代码，您必须删除CONSTRAINT单词：

CREATE TABLE animals
(
   animal_id NUMBER(6) PRIMARY KEY,
   name VARCHAR2(25),
   license_tag_number NUMBER(10) UNIQUE,
   admit_date DATE NOT NULL,
   adoption_id NUMBER(5),
   vaccination_date DATE NOT NULL
);

but you can also use constraints for PK and UNIQUE as below:

但您也可以对 PK 和 UNIQUE 使用约束，如下所示：

CREATE TABLE animals
(
   animal_id NUMBER(6) not null,
   name VARCHAR2(25),
   license_tag_number NUMBER(10) UNIQUE,
   admit_date DATE NOT NULL,
   adoption_id NUMBER(5),
   vaccination_date DATE NOT NULL,
   CONSTRAINT animals_PK PRIMARY KEY (animal_id) ,
   CONSTRAINT l_tag_number_uq UNIQUE (license_tag_number) 
);

It is good practice to use constraints because they give you a friendly name/short description.

使用约束是一种很好的做法，因为它们为您提供了一个友好的名称/简短描述。

SQL Fiddle DEMO

SQL小提琴演示

回答by Robert

I think there some mistake here :

我认为这里有一些错误：

Catch this example :

抓住这个例子：

CREATE TABLE "name_of_table"

    ("column_1" "data_type",

    "column_2" "data_type",

    "column_3" "data_type",

    CONSTRAINT column_name PRIMARY KEY (column_1, column_2)

    ... );

Your code suppose :

你的代码假设：

CREATE TABLE animals(

创建餐桌动物（

CREATE TABLE animals
(
   animal_id NUMBER(6) PRIMARY_KEY,
   name VARCHAR2(25),
   license_tag_number NUMBER(10) UNIQUE,
   admit_date DATE NOT NULL,
   adoption_id NUMBER(5),
   vaccination_date DATE NOT NULL
);

You may check the example here

您可以在此处查看示例