roblem with insert into a table (ORA-02291)
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: roblem with insert into a table (ORA-02291)

Hybrid View

  1. #1
    Join Date
    Nov 2006
    Posts
    1

    roblem with insert into a table (ORA-02291)

    Good morning!

    I have created into a file named creation.sql two tables like
    that :

    create table teacher (iid integer,fname varchar(20),lname varchar(20),department_number integer,primary key(iid));

    create table department (dname varchar(20),department_number integer,s_iid integer,primary key (department_number),foreign key (s_iid) references teacher);

    alter table teacher add foreign key (department_number) references department;

    1.s_iid is the id of the teacher who is in charge in every department

    The tables are created but when i try to insert into the tables i get errors like:

    e.g.

    insert into teacher(12,'Nick','Smith',100);
    insert into teacher(14,'Jane','Owen',200);
    insert into teacher(16,'James','Cart',200);

    insert into department('Biology',100,12);
    insert into department('Chemistry',200,14);

    alter table teacher add foreign key(department_number) references department;

    Errors:

    1.ORA-02291
    integrity constraint (...) violated - parent key not found

    I searched in the web to see what is going wrong with this error and it says that:a foreign key value has no matching primary key value.
    As you can see every foreign key in teacher has a matching primary key in department.

    2.ORA-02275: such a referential constraint already exists in the table

    I would be glad if you could help me find what i have done wrong!

    Thanks, in advance!

  2. #2
    Join Date
    Nov 2006
    Posts
    3

    Problem is not with insert into statement

    Hi

    Very Good Morning.

    First of all have a look at your table Structure.
    Table : Teacher Table : Department

    [B]IID pk [/B] dname
    fname [I]department_number pk[/I]
    lname [B]s_iid fk[/B]
    [I]department_number fk[/I]



    Now Remember the fundamental of primary key- foreign key.

    Without entering Primary key , can u enter foreign key?
    NO
    In your case you are doing same thing dear.
    [B]Suppose you are entering record into "Teacher",[/B]
    You get an error, becouse nothing is there into "department_number" field of table "Department", so if primary key is null, then can u enter into foreign one?

    [B]Suppose you are entering record into "Department",[/B]
    You get an error, becouse nothing is there into "S_iid" field of table "Teacher", so if primary key is null, then can u enter into foreign one?



    Now got it?
    What happen with it?
    so remove s_iid of tabel of "department"
    I dont know ur requirement but it is useless.
    As u have "department_no" into "Teacher" its useless.


    So,
    Take Care

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