Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Problem with insert into a table

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 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

30421 Posts

Posted - 2006-11-30 : 06:39:31
Aside using ORACLE?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

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 Athalye
India.
"Nothing is Impossible"
Go to Top of Page

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
Go to Top of Page

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 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?
Go to Top of Page

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"
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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 Larsson
Helsingborg, Sweden
Go to Top of Page

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 :)
Go to Top of Page

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 Athalye
India.
"Nothing is Impossible"
Go to Top of Page

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 Larsson
Helsingborg, Sweden
Go to Top of Page

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 Fuhrman
http://www.titangs.com
Go to Top of Page

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?
Go to Top of Page

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 Athalye
India.
"Nothing is Impossible"
Go to Top of Page

g_p
Starting Member

48 Posts

Posted - 2006-12-01 : 12:01:15
Yes, you are right
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-04 : 09:53:21
www.ORAFAQ.com

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -