CODEWITHSUNDEEP
sqloracle-databaseforeign-keysprimary-key
dexterpointer
dexterpointer

Reputation: 1

sql trouble creating table (foreign/ref key)(oracle)

Cannot figure out these errors. been working on this far too long please help, I have been stuck on this for about 4 hours. 

CREATE TABLE Student
(
student_id varchar(25),
student_name varchar(25),
major varchar(10),
gpa number(3,2),
PRIMARY KEY(student_id)
);


CREATE TABLE Class
(
schedule_num varchar(25),
semester varchar(25),
course_name varchar(25),
course_number varchar(25),
credit varchar(4),
department varchar(25),
meeting_time varchar(25),
meeting_place varchar(25),
class_size_limit varchar (25),
PRIMARY KEY(schedule_num,semester)
);


CREATE TABLE Enrolls
(
semester varchar(25),
grade varchar(2),
PRIMARY KEY(student_id,schedule_num,semester),
student_id varchar(25) FOREIGN KEY REFERENCES Student(student_id),
schedule_num varchar(25) FOREIGN KEY REFERENCES Class(schedule_num),
);

2 errors---

1) ERROR -- missing right paren line 6
2) ERROR -- table or view does not exist line 1

Upvotes: 0

Views: 136

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

In Oracle, you should use varchar2() rather than varchar() (for very arcane and historical reasons).

When defining foreign and primary keys:

  • The foreign key needs to exactly match the primary key.
  • The references keyword does not use foreign key when inline with the column definition.
  • A composite foreign key cannot be declared in line.
  • A column needs to be defined before it can be used in a constraint.

Here is a SQL Fiddle that fixes these problems:

CREATE TABLE Student (
    student_id varchar2(25),
    student_name varchar2(25),
    major varchar2(10),
    gpa number(3,2),
    PRIMARY KEY(student_id)
);


CREATE TABLE Class (
    schedule_num varchar2(25),
    semester varchar2(25),
    course_name varchar2(25),
    course_number varchar2(25),
    credit varchar2(4),
    department varchar2(25),
    meeting_time varchar2(25),
    meeting_place varchar2(25),
    class_size_limit varchar2(25),
    PRIMARY KEY(schedule_num, semester)
);

CREATE TABLE Enrolls (
    semester varchar2(25),
    grade varchar2(2),
    student_id varchar2(25) REFERENCES Student(student_id),
    schedule_num varchar2(25),
    FOREIGN KEY (schedule_num, semester) REFERENCES Class(schedule_num, semester),
    PRIMARY KEY(student_id ,schedule_num, semester)
);

Upvotes: 1

S.KC
S.KC

Reputation: 71

You have added an extra comma at the end of your second last line. schedule_num varchar(25) FOREIGN KEY REFERENCES Class(schedule_num),

That comma is possibly causing both the errors.

Upvotes: 0

Related Questions