roblem with insert into a table (ORA-02291)
I have created into a file named creation.sql two tables like
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:
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;
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!
Problem is not with insert into statement
Very Good Morning.
First of all have a look at your table Structure.
Table : Teacher Table : Department
IID pk dname
fname department_number pk
lname s_iid fk
Now Remember the fundamental of primary key- foreign key.
Without entering Primary key , can u enter foreign key?
In your case you are doing same thing dear.
Suppose you are entering record into "Teacher",
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?
Suppose you are entering record into "Department",
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.