Printable View
Just a question about foreign keys:
Can you have a foreign key consisting of only one column that points to a column that is apart of a composite primary key in another table?
Likewise, can you have only one column of a composite primary key be created as a foreign key pointing to another non-primary key column in another table?
no, u can't do that.. It gives error..
check this out...
create table rtest4
(
rollno number,
deptno number,
constraint pk_rktest4
primary key(rollno,deptno)
);
// cretes table rtest4
create table rtest5
(
deptno number,
dname varchar2(10)
,
constraint fk_rtest5
foreign key (deptno) references
rtest4(deptno)
);
//gives error..
*
ERROR at line 8:
ORA-02270: no matching unique or primary key for this column-list
Your foreign key will also be a composite foreign key in this case just like primary key.
This works ...
create table rtest5
(
rno number,
deptno number,
dname varchar2(10),
constraint fk_rtest5
foreign key (rno,deptno) references
rtest4(rollno,deptno)
)
/
hope this solves ur query and thanks to u coz it also helped me getting my concepts clear.
Rohit
Quote:
Originally posted by zoey
Just a question about foreign keys:
Can you have a foreign key consisting of only one column that points to a column that is apart of a composite primary key in another table?
Likewise, can you have only one column of a composite primary key be created as a foreign key pointing to another non-primary key column in another table?
It doesn't let you do it because it doesn't make sense. This is not how relational databases are built.
- Chris