add a foreign key constraint, failed

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 Administration

add a foreign key constraint, failed

Author

Topic

java148
Yak Posting Veteran

63 Posts

Posted - 2011-10-26 : 20:38:02

I tried to add a foreign key constraint to a table, but failed. Please help . thanks

create table shippingAddress (name varchar(20), zip varchar(5));
create table person (name varchar(20), age int);

alter table person add constraint fk_name Foreign Key(name) REFERENCES shippingAddress(name)

error is


There are no primary or candidate keys in the referenced table 'shippingAddress' that match the referencing column list in the foreign key 'fk_name'.

java148
Yak Posting Veteran

63 Posts

Posted - 2011-10-26 : 22:13:08

I think I found the reason, table is missing primary key.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-27 : 02:47:54

yep..thats a reason. also if table person is already having data you need to make sure the data contained in name has only values which are present inside name field of shipping address if not not allow you to create fk (unless you specify with no check opition)

Also I feel like fk should be from shipping address -> person rather than other way around which is what you're having now
Ideally person should be master table and shipping address would be having just reference of a person in it

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources