CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle10g
user2456216
user2456216

Reputation: 142

referential integrity in 2 tables in oracle

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

Upvotes: 1

Views: 193

Answers (2)

Sai
Sai

Reputation: 689

  • 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

Upvotes: 1

Srini V
Srini V

Reputation: 11355

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

 NOT NULL references serv (mno, ser,cp)

Upvotes: 0

Related Questions