asked on

FK reference sql server 2005

expert,

I run one sproc script, it gave me error like
There are no primary or candidate keys in the referenced table 'MUSERS' that match the referencing column list in the foreign key 'FK_US_DUSERS'.

I don't understand what is the meaning for this error? how can I know FK_US_DUSERS in which table? or FK_US_DUSERS in table MUSERS which column as FK in other table?

Please help!
Lynn

HainKurt

you should add parent record first...

for example you add a city to Ontario but you did not define Ontario yet...

so first insert ontario into distinct, then insert Toronto into city table

HainKurt

post your statement, also structure of MUSERS table...

check the definition of 'FK_US_DUSERS' to understand what you should insert first...

ASKER CERTIFIED SOLUTION

sammySeltzer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

yrcdba7

ASKER

I cried to create unique index but it tell me that the index for the FK FK_US_DUSERS in table MUSERS already created.

Msg 1913, Level 16, State 1, Line 1
The operation failed because an index or statistics with name 'IX_DOCUSERS_NUM' already exists on table 'MUSERS'.

any help?

yrcdba7

ASKER

the error is:

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

I already create unique index on MUSERS on FK FK_US_DUSERS. why above errror message came out again.

Please help!!!!!!!!

LYnn

HainKurt

please post the table structure
also find the table where 'FK_US_DUSERS' is defined and post the script/definion of that table

it should be like

CONSTRAINT [FK_US_DUSERS]
FOREIGN KEY ([SOME_COLUMN])
REFERENCES [dbo].[SOME_OTHER_TABLE] ( [SOME_OTHER_COLUMN] )

HainKurt

basically you are trying to insert/delete/update a record in a table where there is FK saying the some columns in that record should exists in some other table

to make this work, the other table should have a index on referenced columns so the lookup can be done... you are missing this index on other table...

ASta

Show structure of MUsers

or
- try to drop index IX_DOCUSERS_NUM
- add primary key to table MUsers
- try add constraint again
- and do it only if you work with development server :)

yrcdba7

ASKER

Thank you, you released my stress.
Thank you so much.

Lynn