0

I have multiple tables,

-Student(StudentID[pk],StudentName)

-Qualified(FID[pk],CourseID[pk],dateQ)

-Faculty(FID[pk],Fname)

-Course(CourseID[pk],CourseName)

And i need to create 2 more, which are Section, and Registration.

-Section(SectionNo[pk],Semester[pk],CourseID[pk])

-Registration(StudentID[pk],SectionNo[pk],Semester[pk])

I first create section without any issues:

create table section(
SectionNo number(28) not null,
Semester varchar(25) not null,
CourseID varchar(25) not null,
constraint sec_pk primary key(SectionNo,Semester,CourseID),
constraint sec_fk foreign key(CourseID) references Course(CourseID)
on delete cascade);

Then I try to make a table called registration, but it gives me the error in the title.

create table registration(
StudentID number(28) not null,
SectionNo number(28) not null,
Semester varchar(25) not null,
constraint reg_pk primary key(SectionNo,StudentID,Semester),
constraint reg_fk foreign key(StudentID) references Student(StudentID)
on delete cascade,
constraint reg_fk2 foreign key(SectionNo,Semester) references 
Section(SectionNo,Semester) on delete cascade);

Can someone please help me figure out what the issue is?

Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
Same Doe
  • 7
  • 3

1 Answers1

2

The ORA-2270 error is quite simple: it happens when the columns we reference in the foreign key do not match a primary key or unique constraint on the parent table.

Here, in your case, the primary key of section table is (SectionNo,Semester,CourseID) while you are referring only to Section(SectionNo,Semester)

To get rid of this please add "CourseID in your secondary key as well

A good read : Oracle (ORA-02270) : no matching unique or primary key for this column-list error

Community
  • 1
  • 1
Subodh Karwa
  • 2,495
  • 1
  • 15
  • 13
  • the issue is that "CourseID" is not found in section table, so i can't add that to my foreign key statement. – Same Doe Apr 11 '17 at 17:49
  • I am not sure what do you mean here. I can clearly see column : "CourseID varchar(25) not null " as well as contraint defining primary key onto the column : "constraint sec_pk primary key(SectionNo,Semester,CourseID)" – Subodh Karwa Apr 11 '17 at 17:52
  • is this what you want me to add to my code? constraint reg_fk2 foreign key(SectionNo,Semester) references Section(SectionNo,Semester,CourseID) on delete cascade); – Same Doe Apr 11 '17 at 17:54
  • Please add the following while creating second table: constraint reg_fk2 foreign key(SectionNo,Semester,CourseID) references Section(SectionNo,Semester,CourseID) on delete cascade) – Subodh Karwa Apr 12 '17 at 10:25
  • it didn't, i had to add an extra column called courseid to registration that holds nothing but null values in order for me to produce a foreign key constraint as you did above. – Same Doe Apr 13 '17 at 16:39