SQL*Plus ORA-00904 invalid identifier & ORA-00905 missing keyword (foreign key)

Question

I've tried to debug this code to the best of my ability to eliminate the possibility of little mistakes being the reason these errors are occurring but I keep getting two different errors on three create table statements.

The CREATE TABLE SECTION statement gives an invalid identifier error on line 7 pointing to course#, here's my code:

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));

The CREATE TABLE TAKES statement gives an invalid identifier error on line 4 pointing to section#, here's my code:

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));

Full Context:

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]

Answer 1

Did you follow documentation that describes how you are supposed to do what you are doing? Because, it seems that you didn't pay much attention at classes, nor read documentation and tend to make up things, doing something that is either wrong or doesn't ever exist.

Consider deleting all that mess and starting over.

Here are some guidelines; try to fix those mistakes, come back if it still doesn't work.

In the COURSE table:

• don't use CHAR but VARCHAR2 data type
• you can't create constraints on non-existent columns (for example, a check constraint on CRS_CREDIT column, while you named the column as CREDIT)

In the SECTION table:

• don't use CHAR data type
• you can't use columns (in FOREIGN KEY constraints) that don't exist in the SECTION table (such as SEC_CRS_COURSE#), nor in the table you reference (such as CRS_COURSE# in the COURSE table)
• there are no ON UPDATE CASCADE nor ON DELETE RESTRICT in Oracle

The same goes for the TAKES table.

---

[EDIT, after you almost made it work]

Congratulations! You're now so close! SECTION and TAKES table need some adjustment (missing columns - have a look, I marked them with a comment) and then tables are successfully created.

Once again (as you won't listen): get rid of CHAR data type columns - use VARCHAR2 instead.

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>