can foreign key take role of primary key?
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
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 |
What is similarity and difference between truncate and delete in sql?
what types of replication are supported in sql server? : Sql server database administration
Can you explain the types of Joins that we can have with Sql Server?
How can windows applications connect to sql servers via odbc?
What command would you use to create an index?
what is Equity join?
How does a profiler work?
How to convert numeric expression data types using the cast() function?
your distribution database is full what will u do
Does sql server 2016 have ssms?
When would you use it?
What is clustered primary key?