Author |
Topic |
g_p
Starting Member
48 Posts |
Posted - 2006-11-30 : 06:38:06
|
Good morning!I have created into a file named creation.sql two tables likethat :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 departmentThe 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.[b]ORA-02291integrity constraint (...) violated - parent key not found[/b]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 tableI would be glad if you could help me find what i have done wrong!Thanks, in advance! |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-30 : 06:39:31
|
Aside using ORACLE? Peter LarssonHelsingborg, Sweden |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-11-30 : 06:41:18
|
Change the sequence of INSERTs:insert into department('Biology',100,12);insert into department('Chemistry',200,14);insert into teacher(12,'Nick','Smith',100);insert into teacher(14,'Jane','Owen',200);insert into teacher(16,'James','Cart',200); Harsh AthalyeIndia."Nothing is Impossible" |
|
|
g_p
Starting Member
48 Posts |
Posted - 2006-11-30 : 06:44:23
|
Well i work in a Solaris machine,i use Unix and i use the sqlplus command in order to work with sql.yes, i use oracle |
|
|
g_p
Starting Member
48 Posts |
Posted - 2006-11-30 : 06:54:29
|
quote: Originally posted by harsh_athalye Change the sequence of INSERTs:insert into department('Biology',100,12);insert into department('Chemistry',200,14);insert into teacher(12,'Nick','Smith',100);insert into teacher(14,'Jane','Owen',200);insert into teacher(16,'James','Cart',200); Harsh AthalyeIndia."Nothing is Impossible"
Well i did that but i get for every insert line the error ORA-02291.Inside the creation.sql i have created some others tables but inside the insertion.sql i first valued only the first two tables.Is that a problem? |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2006-11-30 : 07:02:18
|
In case you didn't notice this is a ms sql server forum and some here might know oracle but you'd be better off at www.dbforums.com or something. However, it seems like James departement is missing...--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
g_p
Starting Member
48 Posts |
Posted - 2006-11-30 : 07:10:02
|
In every department there must be one teacher who is going be in charge there.So,one teacher of the three is only teaching and he is not responsibe for a department.How can i show that in the tables without having these errors?Well i use SQL*Plus: Release 8.1.6.0.0 (c) Copyright 1999 Oracle Corporation. All rights reserved. |
|
|
g_p
Starting Member
48 Posts |
Posted - 2006-11-30 : 07:11:24
|
quote: Originally posted by g_p In every department there must be one teacher who is going be in charge there.So,one teacher of the three is only teaching and he is not responsibe for a department.How can i show that in the tables without having these errors?Well i use SQL*Plus: Release 8.1.6.0.0 (c) Copyright 1999 Oracle Corporation. All rights reserved.
I forgot,I have done that in the past with James and i get the same errors |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-30 : 07:13:42
|
It seems to me that you are trying to use some kind of circular reference?A teacher needs a department to be inserted, but a department can not be inserted because the department need a teacher id?Catch 22?Peter LarssonHelsingborg, Sweden |
|
|
g_p
Starting Member
48 Posts |
Posted - 2006-11-30 : 07:24:26
|
Well,every teacher has to have a department where he teaches.Every department has to have one teacher who is going to be in charge of it. e.g. if we have 10 teachers and 3 departments, the department table has to consist only of 3 lines where in every line is going to be 1 of the ten teachers.When you say "catch 22" what do you mean?Thanks, an advance :) |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-11-30 : 07:33:14
|
But you can't insert data in both table at a time. It's definitely circular reference...you can't insert record in either of the table with this constraint.Harsh AthalyeIndia."Nothing is Impossible" |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-30 : 07:39:00
|
I don't think MS SQL would even accept the constraints...Peter LarssonHelsingborg, Sweden |
|
|
SparkByte
Yak Posting Veteran
60 Posts |
Posted - 2006-11-30 : 16:42:10
|
Side note::" Catch 22 " -> "Damned if you do, Damned if you Don't"Could they remove the constraints from the tables and then have a trigger that checks the tables and after the inserts complete?Thank You,John Fuhrmanhttp://www.titangs.com |
|
|
g_p
Starting Member
48 Posts |
Posted - 2006-12-01 : 03:56:13
|
" Catch 22 " -> "Damned if you do, Damned if you Don't"Could they remove the constraints from the tables and then have a trigger that checks the tables and after the inserts complete?-----------------------------------------------------------------Well the design of the exercise must not be done like that.I dunno what's going wrong.Any different idea of inserting data into tables? |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-12-01 : 04:06:59
|
quote: Well the design of the exercise must not be done like that.
Homework problem, I guess! Harsh AthalyeIndia."Nothing is Impossible" |
|
|
g_p
Starting Member
48 Posts |
Posted - 2006-12-01 : 12:01:15
|
Yes, you are right |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-12-04 : 09:53:21
|
www.ORAFAQ.comMadhivananFailing to plan is Planning to fail |
|
|
|