is this possible? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

is this possible?

Author

Topic

mavericky
Posting Yak Master

117 Posts

Posted - 2011-12-06 : 17:56:14

hi,

Table A
a int
b int
a and b together form primary key

Table B
c int (primary key)
d int

I want to create a foreign key such that,
A.b = B.d.

Is this possible, that d corresponds only to b even though the b is just part of the primary key?

Thanks,
Mavericky

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 23:40:27

you can try and see for yourself that its not possible. Reason is since b is only part of pk you cant guarantee uniqueness of values in b to consider it as candidate key. However, if you add a separate unique constraint on b to guarantee uniqueness, then you can add fk from d to b

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-12-07 : 06:43:38

Sql server will not allow you to create fk this way. To achieve this you need to separate it from pk and then add unique check constraint and then you can refer it

Java Database

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-07 : 06:44:59

quote:
Originally posted by jassi.singh

Sql server will not allow you to create fk this way. To achieve this you need to separate it from pk and then add unique check constraint and then you can refer it

Java Database

Why are you restating suggestions like this?
This is same as what i posted yesterday

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-12-07 : 08:04:51

quote:
Originally posted by visakh16

quote:
Originally posted by jassi.singh

Sql server will not allow you to create fk this way. To achieve this you need to separate it from pk and then add unique check constraint and then you can refer it

Java Database

Why are you restating suggestions like this?
This is same as what i posted yesterday

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Can't you just mark his answer as accepted and move on?

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources