SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Problem with insert into a table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

g_p
Starting Member

48 Posts

Posted - 11/30/2006 :  06:38:06  Show Profile  Reply with Quote
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
30116 Posts

Posted - 11/30/2006 :  06:39:31  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Aside using ORACLE?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 11/30/2006 :  06:41:18  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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 - 11/30/2006 :  06:44:23  Show Profile  Reply with Quote
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 - 11/30/2006 :  06:54:29  Show Profile  Reply with Quote
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

Norway
3271 Posts

Posted - 11/30/2006 :  07:02:18  Show Profile  Reply with Quote
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 - 11/30/2006 :  07:10:02  Show Profile  Reply with Quote
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 - 11/30/2006 :  07:11:24  Show Profile  Reply with Quote
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

Sweden
30116 Posts

Posted - 11/30/2006 :  07:13:42  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 11/30/2006 :  07:24:26  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 11/30/2006 :  07:33:14  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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

Sweden
30116 Posts

Posted - 11/30/2006 :  07:39:00  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I don't think MS SQL would even accept the constraints...


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SparkByte
Yak Posting Veteran

USA
60 Posts

Posted - 11/30/2006 :  16:42:10  Show Profile  Reply with Quote
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 - 12/01/2006 :  03:56:13  Show Profile  Reply with Quote

" 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
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 12/01/2006 :  04:06:59  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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 - 12/01/2006 :  12:01:15  Show Profile  Reply with Quote
Yes, you are right
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 12/04/2006 :  09:53:21  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
www.ORAFAQ.com

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000