Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2007
    Posts
    1

    Unanswered: urgent help please

    can anyonetell me why my database giving me the follwing error, when i try to dropthe relationship:
    ORA-02443: Cannot drop constraint - nonexistent constrain


    i have used this script to drop my realtionship -
    ALTER TABLE result DROP CONSTRAINT moduleid_FK;

    i looked on the net, it says make sure the constrain name is right, i did and its right... why my sql giving me this error please..

    CREATE TABLE STUDENT
    (StudID char(4) NOT NULL ,
    StudfName varchar(30),
    StudSname varchar(30),
    StudDOB varchar(30),
    StudHouseNo varchar(30),
    StudStreetName varchar(30),
    studcity varchar(30),
    StudPostcode varchar(30),
    StudPhoneNo varchar(30),
    StudNextofKind varchar(30),
    StudNextofKinContact varchar(30),
    PRIMARY KEY (StudID));


    CREATE TABLE COURSE
    (CourseID char(4) NOT NULL,
    StudID char(4) NOT NULL,
    CourseName varchar(30),
    Courselength varchar(30),
    CourseDegreeType varchar(15),
    PRIMARY KEY (CourseID),
    FOREIGN KEY (StudID) REFERENCES student (StudID));



    CREATE TABLE RESULT
    (ResultID char(4) NOT NULL,
    STUDID CHAR(4) NOT NULL,
    ModuleID char(4) NOT NULL,
    Module1Result varchar(15),
    Module2Result varchar(15),
    Module3Result varchar(15),
    PRIMARY KEY (ResultID),
    FOREIGN KEY (studID) REFERENCES student(studID));



    CREATE TABLE MODULE
    (ModuleID char(4) NOT NULL,
    ResultID char(4) NOT NULL,
    ATTENDANCEID char(4) NOT NULL,
    ModuleName1 varchar(45),
    ModuleName2 varchar(45),
    ModuleName3 varchar(45),
    ModuleCode varchar(6),
    PRIMARY KEY (ModuleID),
    FOREIGN KEY (ResultID) REFERENCES RESULT(ResultID));


    ALTER TABLE RESULT
    ADD FOREIGN KEY (moduleID) REFERENCES module(moduleID);

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by charles1309
    i looked on the net, it says make sure the constrain name is right, i did and its right...
    No it's not - otherwise Oracle wouldn't complain

    When you create a constraint like you did Oracle will generate unique name for the constraint. It is most probably something like SYS_....

    To find out the correct name of your constraint you need to query the view ALL_CONSTRAINTS:
    Code:
    select * from all_constraints
    where table_name = 'RESULT'
    If you want to get a proper name for your FK constraints you need to supply that name in the constraint definition
    Code:
    CREATE TABLE COURSE
    (CourseID char(4) NOT NULL,
    StudID char(4) NOT NULL,
    CourseName varchar(30),
    Courselength varchar(30),
    CourseDegreeType varchar(15),
    PRIMARY KEY (CourseID),
    CONSTRAINT fk_course_stud FOREIGN KEY (StudID) REFERENCES student (StudID));
    will create a FK constraing name FK_COURSE_STUD.

    When you use ALTER TABLE to define the constraint you need to do the same:
    Code:
    ALTER TABLE RESULT
    ADD CONSTRAINT fk_result_module
      FOREIGN KEY (moduleID) REFERENCES module(moduleID);

Posting Permissions

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