can foreign key take role of primary key?

Categories >> Software >> Databases >> SQL Server
Suggest New Category

Question Posted / ganesh prasad sial

5 Answers
8280 Views
CarrizalSoft Technologies, TCS, Villa Marie, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

can foreign key take role of primary key? ..

Answer / gaurav

Yes. This situation occurs in one-to-one relationships.

For example:

create table Table1
( T1_Id integer not null primary key
, T1_Data varchar(9) not null
)
create table Table2
( T2_Id integer not null primary key
, T2_Data varchar(37) not null
, foreign key (T2_Id) references Table1 (T1_Id)
)

Why would someone design two tables like this? If it's a
one-to-one relationship, couldn't you just include T2_Data
in Table1? Yes, you could, although then you would also need
to allow T2_Data to be NULL, if not every T1_Id has a
matching T2_Id—in this case it's actually a
one-to-zero-or-one relationship.

If it's a true one-to-one relationship, where every T1_Id
has a matching T2_Id, then you could combine them without
NULLs. Then the only reason why you might want separate
tables would be if T2_Data were (a) infrequently queried,
and (b) so large that it would result in many fewer physical
rows on a physical database page, which would imply poorer
performance for queries that used only T1_Data.

Is This Answer Correct ?

10 Yes

0 No

can foreign key take role of primary key? ..

Answer / @panwar

yes but for same table and if you use foriegn key as a
primary key for same table then there will be no benifit of
master-detail concept for it relation.
and if you follow the Master_detail concept then you can't
use as primary key for another table.

Is This Answer Correct ?

9 Yes

2 No

can foreign key take role of primary key? ..

Answer / kkk

No foreign key cant take role of primary key

Is This Answer Correct ?

7 Yes

4 No

can foreign key take role of primary key? ..

Answer / radhakrishnan vaithilingam

no,foreign key is possible for null value in column.
this rules not matching with PK.

Is This Answer Correct ?

2 Yes

1 No

can foreign key take role of primary key? ..

Answer / suresh murugadass

Yes. It is possible in this scenario. Here table has a
self join. Since emp_id represents both emp_id and mgr_id
this is possible a primary key to behave as foriegn key
create table emp(
emp_id int not null primarykey,
emp_name varchar2(40) not null,
mgr_id int not null foriegn key(mgr_id) references emp(emp_id)
}

Is This Answer Correct ?

2 Yes

2 No

Post New Answer