foreign key syntax

[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?

[rohit1346]

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

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?

[chrisrlong]

It doesn't let you do it because it doesn't make sense. This is not how relational databases are built.

- Chris