roblem with insert into a table (ORA-02291)

[g_p]

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!

[zalak]

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