| Author |
 Topic  |
|
|
g_p
Starting Member
48 Posts |
 Posted - 11/30/2006 : 06:38:06
|
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.[b]ORA-02291
integrity 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 table
I would be glad if you could help me find what i have done wrong!
Thanks, in advance!
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 11/30/2006 : 06:39:31
|
Aside using ORACLE?
Peter Larsson
Helsingborg, Sweden |
 |
|
|
harsh_athalye
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 11/30/2006 : 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 Athalye
India.
"Nothing is Impossible" |
|
|
|
g_p
Starting Member
48 Posts |
Posted - 11/30/2006 : 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 - 11/30/2006 : 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 Athalye
India.
"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
Norway
3241 Posts |
Posted - 11/30/2006 : 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 - 11/30/2006 : 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 - 11/30/2006 : 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
Sweden
29138 Posts |
Posted - 11/30/2006 : 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 Larsson
Helsingborg, Sweden |
|
|
|
g_p
Starting Member
48 Posts |
Posted - 11/30/2006 : 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
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 11/30/2006 : 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 Athalye
India.
"Nothing is Impossible" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 11/30/2006 : 07:39:00
|
I don't think MS SQL would even accept the constraints...
Peter Larsson
Helsingborg, Sweden |
|
|
|
SparkByte
Yak Posting Veteran
USA
60 Posts |
Posted - 11/30/2006 : 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 Fuhrman
http://www.titangs.com |
|
|
|
g_p
Starting Member
48 Posts |
Posted - 12/01/2006 : 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
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 12/01/2006 : 04:06:59
|
quote:
Well the design of the exercise must not be done like that.
Homework problem, I guess! 
Harsh Athalye
India.
"Nothing is Impossible" |
|
|
|
g_p
Starting Member
48 Posts |
Posted - 12/01/2006 : 12:01:15
|
| Yes, you are right |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 12/04/2006 : 09:53:21
|
www.ORAFAQ.com
Madhivanan
Failing to plan is Planning to fail |
|
|
| |
Topic |
|
Problem with insert into a table
