SQL Error: ORA-02256 Foreign Keys referencing composite primary keys
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: SQL Error: ORA-02256 Foreign Keys referencing composite primary keys

  1. #1
    Join Date
    Apr 2013
    Posts
    2

    SQL Error: ORA-02256 Foreign Keys referencing composite primary keys

    Hello All,

    I am struggling and frustrated with the error message that I am getting.

    I do not understand what I am doing wrong. The following are my created tables below. I am trying to reference a foreign key in the Registration Table with a Primary Composite Key in the Section Table.

    I am not sure what I am doing wrong. If anyone can help it is much appreciated. The error message I am receiving is below after the last created table.


    Create Table Student
    (
    StudentID number (6,0) NOT NULL,
    Student_Name varchar2 (20) NOT NULL,
    Constraint Student_PK Primary Key (StudentID)
    );



    Create Table Faculty
    (
    FacultyID number (5,0) NOT NULL,
    Faculty_Name varchar2 (20) NOT NULL,
    Constraint Faculty_PK Primary Key (FacultyID)
    );



    Create Table Course
    (
    CourseID varchar2 (10) NOT NULL,
    Course_Name varchar2 (20) NOT NULL,
    Constraint Course_PK Primary Key (CourseID)
    );




    Create Table Qualified
    (
    FacultyID number (5,0) NOT NULL,
    CourseID varchar2 (10) NOT NULL,
    Date_Qualified varchar2 (10) NOT NULL,
    Constraint Qualified_PK Primary Key (FacultyID, CourseID),
    Constraint Qualified_FK1 Foreign Key (FacultyID) references Faculty (FacultyID),
    Constraint Qualified_FK2 Foreign Key (CourseID) references Course (CourseID)
    );






    Create Table Section
    (
    Section_No number (5,0) NOT NULL,
    Semester varchar2 (7) NOT NULL,
    CourseID varchar2 (12) NOT NULL,
    Constraint Section_PK Primary Key (Section_No, Semester, CourseID),
    Constraint Section_FK Foreign Key (CourseID) references Course (CourseID)
    );




    Create Table Registration
    (
    StudentID number (6,0) NOT NULL,
    Section_No number (5,0) NOT NULL,
    Semester varchar2 (7) NOT NULL,
    Constraint Registration_PK Primary Key (StudentID, Section_No, Semester),
    Constraint Registration_FK1 Foreign Key (StudentID) references Student (StudentID),
    Constraint Registration_FK2 Foreign Key (Section_No) references Section (Section_No, Semester, CourseID),
    Constraint Registration_FK3 Foreign Key (Semester) references Section (Section_No, Semester, CourseID)
    );



    Error starting at line 1 in command:
    Create Table Registration
    (
    StudentID number (6,0) NOT NULL,
    Section_No number (5,0) NOT NULL,
    Semester varchar2 (7) NOT NULL,
    Constraint Registration_PK Primary Key (StudentID, Section_No, Semester),
    Constraint Registration_FK1 Foreign Key (StudentID) references Student (StudentID),
    Constraint Registration_FK2 Foreign Key (Section_No) references Section (Section_No, Semester, CourseID),
    Constraint Registration_FK3 Foreign Key (Semester) references Section (Section_No, Semester, CourseID)
    )
    Error at Command Line:8 Column:104
    Error report:
    SQL Error: ORA-02256: number of referencing columns must match referenced columns
    02256. 00000 - "number of referencing columns must match referenced columns"
    *Cause: The number of columns in the foreign-key referencing list is not
    equal to the number of columns in the referenced list.
    *Action: Make sure that the referencing columns match the referenced
    columns.

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,003
    You have three columns that need to all reference the same table and primary key.
    Also make sure that courseid is defined the same in every table.

    Code:
    Create Table Registration (
       StudentID  number(6,0) NOT NULL,
       Section_No number(5,0) NOT NULL,
       Semester   varchar2(7) NOT NULL,
       CourseID varchar2 (12) NOT NULL,
       Constraint Registration_PK  Primary Key (StudentID, Section_No, Semester),
       Constraint Registration_FK1 Foreign Key (StudentID) references Student (StudentID),
       Constraint Registration_FK2 Foreign Key (Section_No, Semester, CourseID) 
                            references Section (Section_No, Semester, CourseID));
    this space intentionally left blank

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by DBA Struggler View Post
    Hello All,

    I am struggling and frustrated with the error message that I am getting.

    I do not understand what I am doing wrong. The following are my created tables below. I am trying to reference a foreign key in the Registration Table with a Primary Composite Key in the Section Table.

    I am not sure what I am doing wrong. If anyone can help it is much appreciated. The error message I am receiving is below after the last created table.


    Create Table Student
    (
    StudentID number (6,0) NOT NULL,
    Student_Name varchar2 (20) NOT NULL,
    Constraint Student_PK Primary Key (StudentID)
    );



    Create Table Faculty
    (
    FacultyID number (5,0) NOT NULL,
    Faculty_Name varchar2 (20) NOT NULL,
    Constraint Faculty_PK Primary Key (FacultyID)
    );



    Create Table Course
    (
    CourseID varchar2 (10) NOT NULL,
    Course_Name varchar2 (20) NOT NULL,
    Constraint Course_PK Primary Key (CourseID)
    );




    Create Table Qualified
    (
    FacultyID number (5,0) NOT NULL,
    CourseID varchar2 (10) NOT NULL,
    Date_Qualified varchar2 (10) NOT NULL,
    Constraint Qualified_PK Primary Key (FacultyID, CourseID),
    Constraint Qualified_FK1 Foreign Key (FacultyID) references Faculty (FacultyID),
    Constraint Qualified_FK2 Foreign Key (CourseID) references Course (CourseID)
    );






    Create Table Section
    (
    Section_No number (5,0) NOT NULL,
    Semester varchar2 (7) NOT NULL,
    CourseID varchar2 (12) NOT NULL,
    Constraint Section_PK Primary Key (Section_No, Semester, CourseID),
    Constraint Section_FK Foreign Key (CourseID) references Course (CourseID)
    );




    Create Table Registration
    (
    StudentID number (6,0) NOT NULL,
    Section_No number (5,0) NOT NULL,
    Semester varchar2 (7) NOT NULL,
    Constraint Registration_PK Primary Key (StudentID, Section_No, Semester),
    Constraint Registration_FK1 Foreign Key (StudentID) references Student (StudentID),
    Constraint Registration_FK2 Foreign Key (Section_No) references Section (Section_No, Semester, CourseID),
    Constraint Registration_FK3 Foreign Key (Semester) references Section (Section_No, Semester, CourseID)
    );



    Error starting at line 1 in command:
    Create Table Registration
    (
    StudentID number (6,0) NOT NULL,
    Section_No number (5,0) NOT NULL,
    Semester varchar2 (7) NOT NULL,
    Constraint Registration_PK Primary Key (StudentID, Section_No, Semester),
    Constraint Registration_FK1 Foreign Key (StudentID) references Student (StudentID),
    Constraint Registration_FK2 Foreign Key (Section_No) references Section (Section_No, Semester, CourseID),
    Constraint Registration_FK3 Foreign Key (Semester) references Section (Section_No, Semester, CourseID)
    )
    Error at Command Line:8 Column:104
    Error report:
    SQL Error: ORA-02256: number of referencing columns must match referenced columns
    02256. 00000 - "number of referencing columns must match referenced columns"
    *Cause: The number of columns in the foreign-key referencing list is not
    equal to the number of columns in the referenced list.
    *Action: Make sure that the referencing columns match the referenced
    columns.
    Building up on Gandolf's comment, since there is no CourseID column on table Registration it is not possible to establish referential integrity constraint against Section table which has a composed, three columns PK that includes CourseID.

    If Registration table is supposed to track registrations at the course grain I would suggest to add CourseID to such table, include CourseID as part of Registration_FK2 and trash Registration_FK3.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Apr 2013
    Posts
    2
    I just want to let you know that I managed to solve the problem with referential integrity constraint. I put Section_No as the primary key for the Section relation, and I put Section_No and Semester as the primary key for the Registration relation. Everything works fine now.

    Thanks for your replies very much.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width