By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,436 Members | 2,958 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,436 IT Pros & Developers. It's quick & easy.

Issue with Multiple Foreign Keys in MSSQL 2005

P: 1
Greetings everyone -

I am new to MSSQL 2005, and have started a database design project for my company. The issue I have is in a specific instance of foreign key creation.

Here is the whole situation:
I have a ContactInfo table that has several fields, including a CreatedByUserID, and a LastModifiedUserID, that are both INT fields.

I want to link both UserID fields to the UserInfo table by UserID. (Since the CreatedBy will be filled once, and should never be changed - unless a cascade affects from the UserInfo table), but the LastModifiedUserId will change every time someone edits this contact information.

I have tried setting these up with Foreign Key links, and if I create one with Update - Cascade, Delete - No Action (so the link is broken, but I can then find out what it was originally linked to and then track down why the user record was deleted) it works fine on the first one, but when I try to do the same foreign key setup on the 2nd one, I get the following error:

Expand|Select|Wrap|Line Numbers
  1. - Unable to create relationship 'FK_conContactInfo_optUserInfo_LastModifiedBy'.  
  2. Introducing FOREIGN KEY constraint 'FK_conContactInfo_optUserInfo_LastModifiedBy' on table 'conContactInfo' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
  3. Could not create constraint. See previous errors.
Any ideas, help or work arounds would be greatly appreciated.

Thanks in advance -

May 15 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.