代码之家 › 专栏 › 技术社区 › TehBrackShiip

SQL*Plus ORA-00904标识符无效&ORA-00905缺少关键字(外键)

ora-00905 ora-00904 create-table ddl oracle

-1

TehBrackShiip · 技术社区 · 8 年前

我已经尽力调试了这段代码,以消除出现这些错误的原因可能是一些小错误,但我在三条create table语句中不断遇到两个不同的错误。

CREATE TABLE SECTION语句在指向课程#的第7行出现无效标识符错误,下面是我的代码:

CREATE TABLE SECTION
(SECTION#   VARCHAR2(8) constraint pk_section# primary key,
TIME   CHAR(5),
MAXST   NUMBER(2),
ROOM   VARCHAR2(14),
constraint chk_maxst check(maxst<=35),
constraint fk_crs foreign key(course#)
REFERENCES course(course#),
constraint fk_pro foreign key(empid)
REFERENCES professor(empid));

CREATE TABLE TAKES语句在指向第#节的第4行出现无效标识符错误,下面是我的代码:

CREATE TABLE TAKES
(GRADE   CHAR(5) constraint nn_grade not null,
constraint chk_grade check(grade IN ('A','B','C')),
constraint fk_sec foreign key(section#)
REFERENCES section (section#),
constraint fk_stu foreign key(sid)
REFERENCES student(sid));

完整上下文:

drop table professor cascade constraints;
drop table course cascade constraints;
drop table student cascade constraints;
drop table section cascade constraints;
drop table takes cascade constraints;

CREATE TABLE PROFESSOR
(NAME  CHAR(15) constraint nn_name not null,
EMPID VARCHAR2(8) constraint pk_empid primary key,
PHONE NUMBER(10),
DATEHIRED DATE,
SALARY NUMBER);

CREATE TABLE COURSE
(NAME   CHAR(24) constraint nn_names not null,
COURSE# CHAR(10) constraint pk_course# primary key,
CREDIT  CHAR(6) constraint nn_credit not null,
COLLEGE CHAR(20),
HRS   NUMBER(1),
constraint chk_credit check(credit IN('U','G')),
constraint chk_college check(college IN ('Arts and Sciences','Education','Engineering','Business')),
constraint chk_course check((credit='U' AND hrs<=4) OR (credit = 'G' AND hrs=3)),
constraint unq_course unique(name, college));

CREATE TABLE STUDENT
(SID   VARCHAR2(7) constraint pk_sid primary key,
NAME    CHAR(14),
ADDRESS  CHAR(22),
BIRTHDATE DATE,
GRADELEVEL CHAR(2) constraint nn_glvl not null);

CREATE TABLE SECTION
(SECTION#   VARCHAR2(8) constraint pk_section# primary key,
TIME   CHAR(5),
MAXST   NUMBER(2),
ROOM   VARCHAR2(14),
constraint chk_maxst check(maxst<=35),
constraint fk_crs foreign key(course#)
REFERENCES course(course#),
constraint fk_pro foreign key(empid)
REFERENCES professor(empid));

CREATE TABLE TAKES
(GRADE   CHAR(5) constraint nn_grade not null,
constraint chk_grade check(grade IN ('A','B','C')),
constraint fk_sec foreign key(section#)
REFERENCES section (section#),
constraint fk_stu foreign key(sid)
REFERENCES student(sid));

Textbook references:
[https://drive.google.com/open?id=1eDdBShzgnSjISqxByJ7FKgbkLCEwXzpd][1]
[https://drive.google.com/open?id=1WhDsgQy2xSwjxVMqDzaGOcBh7zSokneT][2]
[https://drive.google.com/open?id=12N51OCEucRn_unagqHYsqufEGK3tKJH_][3]

1 回复 | 直到 8 年前

Littlefoot 8 年前

你是否遵循了描述你应该如何做你正在做的事情的文档?因为,你似乎在课堂上没有太多注意力,也没有阅读文档组成做一些错误的或根本不存在的事情。

考虑删除所有这些混乱,重新开始。

以下是一些指导原则;试着纠正那些错误,如果仍然不起作用,请回来。

在课程表格:

不使用CHAR,但使用VARCHAR2数据类型
不能在不存在的列上创建约束(例如,CRS\U CREDIT列上的检查约束,而将该列命名为CREDIT)

在 截面图 表格:

不使用CHAR数据类型
不能使用SECTION表中不存在的列(在外键约束中)(例如SEC\u CRS\u COURSE),也不能使用引用的表中不存在的列(例如COURSE表中的CRS\u COURSE)
Oracle中没有更新级联和删除限制

这同样适用于拿桌子

[编辑,在您之后几乎使其工作]

祝贺你现在离得太近了!节和TAKES表需要进行一些调整(缺少列-请看,我用注释标记了它们),然后成功创建了表。

再一次(因为您不会听):去掉CHAR数据类型列-改用VARCHAR2。

SQL> CREATE TABLE PROFESSOR
  2  (
  3     NAME        CHAR (15) CONSTRAINT nn_name NOT NULL,
  4     EMPID       VARCHAR2 (8) CONSTRAINT pk_empid PRIMARY KEY,
  5     PHONE       NUMBER (10),
  6     DATEHIRED   DATE,
  7     SALARY      NUMBER
  8  );

Table created.

SQL>
SQL> CREATE TABLE COURSE
  2  (
  3     NAME      CHAR (24) CONSTRAINT nn_names NOT NULL,
  4     COURSE#   CHAR (10) CONSTRAINT pk_course# PRIMARY KEY,
  5     CREDIT    CHAR (6) CONSTRAINT nn_credit NOT NULL,
  6     COLLEGE   CHAR (20),
  7     HRS       NUMBER (1),
  8     CONSTRAINT chk_credit CHECK (credit IN ('U', 'G')),
  9     CONSTRAINT chk_college CHECK
 10        (college IN ('Arts and Sciences',
 11                     'Education',
 12                     'Engineering',
 13                     'Business')),
 14     CONSTRAINT chk_course CHECK
 15        ( (credit = 'U' AND hrs <= 4) OR (credit = 'G' AND hrs = 3)),
 16     CONSTRAINT unq_course UNIQUE (name, college)
 17  );

Table created.

SQL>
SQL> CREATE TABLE STUDENT
  2  (
  3     SID          VARCHAR2 (7) CONSTRAINT pk_sid PRIMARY KEY,
  4     NAME         CHAR (14),
  5     ADDRESS      CHAR (22),
  6     BIRTHDATE    DATE,
  7     GRADELEVEL   CHAR (2) CONSTRAINT nn_glvl NOT NULL
  8  );

Table created.

SQL>
SQL> CREATE TABLE SECTION
  2  (
  3     SECTION#   VARCHAR2 (8) CONSTRAINT pk_section# PRIMARY KEY,
  4     TIME       CHAR (5),
  5     MAXST      NUMBER (2),
  6     ROOM       VARCHAR2 (14),
  7     course#    CHAR (10),                                        -- added by LF
  8     empid      VARCHAR2 (8),                                     -- added by LF
  9     CONSTRAINT chk_maxst CHECK (maxst <= 35),
 10     CONSTRAINT fk_crs FOREIGN KEY (course#) REFERENCES course (course#),
 11     CONSTRAINT fk_pro FOREIGN KEY (empid) REFERENCES professor (empid)
 12  );

Table created.

SQL>
SQL> CREATE TABLE TAKES
  2  (
  3     GRADE      CHAR (5) CONSTRAINT nn_grade NOT NULL,
  4     section#   VARCHAR2 (8),                                     -- added by LF
  5     sid        VARCHAR2 (7),                                     -- added by LF
  6     CONSTRAINT chk_grade CHECK (grade IN ('A', 'B', 'C')),
  7     CONSTRAINT fk_sec FOREIGN KEY (section#) REFERENCES section (section#),
  8     CONSTRAINT fk_stu FOREIGN KEY (sid) REFERENCES student (sid)
  9  );

Table created.

SQL>