referential integrity in 2 tables in oracle

Question

I have a table in Oracle, the table is something like:

create table serv ( ser number(7), mno number(4), cp number(4), primary key (ser, mno, cp) );

And other table, and is like:

create table ftp ( mno number(4), cp number(4), ftp varchar2(9), ser number(7), constraint fk_prueba foreign key (mno, ser,cp) references serv (mno, ser,cp) );

And I am trying to insert into the serv table the following values:

insert into serv values(1,2,3);

Now I am inserting into ftp table the values:

insert into ftp values (1,2,'hola',null); insert into ftp values (1,5,'hola',null); insert into ftp values (1,9,'hola',null);

And the problem is that I can insert the values in FTP, and the values can not be inserted, because in serv I have the mno 2, and I am inserting a mno into ftp that does not exist in serv.

I need insert values in ftp, that exist in serv.

Who can help?

Thanks

That's a very good point in fact. In Oracle, this is limitation or property, not sure. I don't know the exact reason but a composite foreign key is never checked for parent key in master table if one or more of the column of key in the child table is inserted as null. — San
– San, Commented Jan 10, 2014 at 16:46

Sai · Accepted Answer · 2014-01-10 18:41:54Z

you are creating composite primary key and referencing it by a child table. So you are creating relation between two table based on combination ser, mno, cp . Only that combination is referred from patent table..it wont treat each value seperately
so, relate two table by creating referential integrity on column ser and composite ref.int on mno & cp

Srini V · Accepted Answer · 2014-01-10 16:49:51Z

This is how Foreign Key works in Oracle. Use this to overcome the issue

 NOT NULL references serv (mno, ser,cp)

Collectives™ on Stack Overflow

referential integrity in 2 tables in oracle

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related