Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2004
    Location
    Oklahoma City OK -
    Posts
    122

    Unanswered: Unable to create referential constraint

    I know this is simple, but I do not see my problem.

    ERROR :
    ORA-02270: no matching unique or primary key for this column-list



    I am unable to create this and other referential integrity constraints

    SQL> desc COURSE;
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    COURSE_SEQ NOT NULL NUMBER -- primary key
    SCHOOL_SEQ NOT NULL NUMBER
    DEPT_CODE NOT NULL VARCHAR2(6)
    GRADE_CODE VARCHAR2(6)
    COURSE_CODE VARCHAR2(6) -- unique
    COURSE_TITLE VARCHAR2(30)
    COURSE_UNITS NUMBER
    COURSE_TYPE VARCHAR2(4)
    MANDATORY_OPT VARCHAR2(4)

    SQL> desc ASSIGNMENT;
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    ASSIGNMENT_SEQ NOT NULL NUMBER -- primary key
    SCHOOL_SEQ NOT NULL NUMBER
    DEPT_CODE NOT NULL VARCHAR2(6)
    COURSE_CODE VARCHAR2(6)
    GRADE_CODE VARCHAR2(6)
    ASSIGNMENT_NBR NUMBER
    ASSIGNMENT_TITLE VARCHAR2(60)
    ASSIGNMENT_TYPE VARCHAR2(12)
    ASSIGNMENT_COMMENT VARCHAR2(300)



    SQL> ALTER TABLE ASSIGNMENT ADD (
    2 CONSTRAINT FK_COURSE_CODE_ASSGN FOREIGN KEY (COURSE_CODE)
    3 REFERENCES COURSE (COURSE_CODE));
    REFERENCES COURSE (COURSE_CODE))
    *
    ERROR at line 3:
    ORA-02270: no matching unique or primary key for this column-list

    Thank you ,

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    If you check your table def, you will see that course_seq is the primary key on course, NOT course_code. You should be storing course_seq, not course_code in your assignment table. When you need to reference the course_code, then join to the course table via the course_seq.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Did you create a UNIQUE index on COURSE(COURSE_CODE)?

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Mar 2004
    Location
    Warsaw / Poland
    Posts
    73
    Unique index won't be enough; You need unique constraint or primary key for that:

    SQL> create table parent(id number);

    Table created.

    SQL> create unique index idx_parent on parent(id);

    Index created.

    SQL> create table child(id number);

    Table created.

    SQL> alter table child add constraint fk_child_parent foreign key (id) references parent(id);
    alter table child add constraint fk_child_parent foreign key (id) references parent(id)
    *
    ERROR at line 1:
    ORA-02270: no matching unique or primary key for this column-list


    SQL> alter table parent add constraint unq_parent unique (id);

    Table altered.

    SQL> alter table child add constraint fk_child_parent foreign key (id) references parent(id);

    Table altered.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I believe LKBrwn_DBA meant to say CONSTRAINT; link he provided guides us to "Foreign Key Constraints" section which talks about it.

    Moreover, whenever a unique constraint is created, Oracle also creates a unique index.

  6. #6
    Join Date
    Oct 2004
    Location
    Oklahoma City OK -
    Posts
    122

    Thanks to everyone.

    SQL> ALTER TABLE COURSE ADD (
    CONSTRAINT COURSE_CODE_U UNIQUE (COURSE_CODE)
    );
    Table altered.



    SQL> ALTER TABLE ASSIGNMENT ADD (
    CONSTRAINT FK_COURSE_CODE_ASSGN FOREIGN KEY (COURSE_CODE)
    REFERENCES COURSE (COURSE_CODE));

    Table altered.


    This worked. Thanks to LKBrwn_DBA and everyone who helped my tunnel vision.
    I had previously created an index on course.course_code , not a constraint.
    I was just not thinking clearly on Friday.

Posting Permissions

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