How to handle @@error=1785

I'm trying to add a FK constraint to a table and get error 1785
"
Introducing FOREIGN KEY constraint 'fk_blah'
on table my_table' may cause cycles or multiple cascade paths. Specify
ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN
KEY constraints.
"
I've tried put the alter table statement in a tran and roll it back if
@@error=1785 but I still get the error. Not sure if there is any way
to handle this. If there was it would be good because I've actually
got 100 similar alter table statements to be run in and my list stops
at the first occurence of error 1785.

Any ideas?
0
nzrdb6
3/24/2010 4:32:44 PM
sqlserver.server 1327 articles. 0 followers. Follow

1 Replies
848 Views

Similar Articles

[PageSpeed] 27

nzrdb6 wrote:
> I'm trying to add a FK constraint to a table and get error 1785
> "
> Introducing FOREIGN KEY constraint 'fk_blah'
> on table my_table' may cause cycles or multiple cascade paths. Specify
> ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN
> KEY constraints.
> "
> I've tried put the alter table statement in a tran and roll it back if
> @@error=1785 but I still get the error. Not sure if there is any way
> to handle this. If there was it would be good because I've actually
> got 100 similar alter table statements to be run in and my list stops
> at the first occurence of error 1785.

You are going to have to make sure your foreign key relationships have 
no cycles or multiple cascade paths or that you don't cascade deletions 
or updates. To illustrate what is meant by "cycles", suppose you have

CREATE TABLE A
(
	aID int NOT NULL PRIMARY KEY,
	bID int NOT NULL
)

CREATE TABLE B
(
	bID int NOT NULL PRIMARY KEY,
	aID int NOT NULL
)

ALTER TABLE A
ADD CONSTRAINT FK_A_B FOREIGN KEY (aID) REFERENCES B (aID)
ON DELETE CASCADE ON UPDATE CASCADE

ALTER TABLE B
ADD CONSTRAINT FK_B_A FOREIGN KEY (bID) REFERENCES A (bID)
ON DELETE CASCADE ON UPDATE CASCADE

INSERT INTO A (aID, bID) VALUES (1, 5)
INSERT INTO B (bID, aID) VALUES (5, 1)

Then what do you suppose happens if you execute

DELETE A WHERE aID = 1

? Deleting the row in A forces the row in B to be deleted first--but the 
row in B can't be deleted till the same row in A is deleted.

I don't know how to describe just how it causes a problem, but multiple 
cascade paths also need to be avoid. These are cases where table B has a 
foreign key into table A, and table C has a foreign key into both A and 
B. An update to the primary key in A or the deletion of a row in A then 
triggers updates to or deletion of row in C that are related to rows in 
A *and* rows in C that are related to rows in B that are related to rows 
in A.

So you have to make sure that your foreign key constraints that allow 
cascades don't create any cycles or multiple paths of execution like 
this. If you create a relationship diagram, it should have no closed loops.
0
Harlan
3/24/2010 5:58:00 PM
Reply:

Similar Artilces:

After modify isv.config, the CRM login error.
Just add the following code to isv.config, but when login CRM, it says "error...". I think I need to do some extra jobs? <MenuBar> <!-- Custom menu bar items that appear between --> <!-- the GoTo menu and the Help menu --> <CustomMenus> <Menu Title="ISV"> <MenuItem Title="New Window" Url="http://www.microsoft.com"/> <MenuSpacer/> <SubMenu Title="ISV Tests"> <M...

xp sp2
Hi, we have performed an ActiveX for Internet Explorer using Visual C++ ..net, and Windows 2000 Service Pack 4. We have tested it in different versions of IE and Windows 2000, always successfully. Then, we tried with Windows XP, and Windows XP Service Pack 1, with no problems. But when I tested it on Windows XP Service Pack 2, an error occurs and Internet Explorer is closed. It happens just when ie try to call a method of our OCX. Does anybody know what is happening? Is there any feature of XP SP2 that may make the OCX crash? Thank you in advance, and best regards, Manuel ...

Exchange 2K to 2K3 migration
I'd like to get some advice on the best approach to handle the STM file during an Inter-Org mailbox move. The Exchange migration wizard will handle the the mailbox but how do you handle the content in the STM file? Thanks. You don't need to worry about it. Mailbox data is stored in both the EDB and STM files. Typically, message content from the Internet will be in the STM file. I'd suspect that when you migrate the mailboxes, all data will be converted to native MAPI format (moved to EDB file) and will then be moved to the new mailbox. -- Ben Winzenz Exchange MVP Me...

Handle to an ActiveX control
Hi... What if I use GetModuleHandle(L"abc.ocx") in the InitInstance() of the of the App class derived from 'COleControlModule'...of the ActiveX control "abc.ocx" ? Will I get the Handle or it will return NULL....? I have encountered controls, in some of them..handle is found and in others its NULL... Wat is the reason behind this behavior..? Can any one explain? "Abby++" <asthana.abhinav@gmail.com> wrote in message news:1174308443.062622.51500@b75g2000hsg.googlegroups.com... > What if I use GetModuleHandle(L"abc.ocx") in the InitI...

Illegal Error Mess when i open spreadsheet
hello i get the following windows based error message when i open a office 2k spreadsheet. i get the message with the white screen. excel the program has performed an illegal operation, if this contines please contact your vendor EXCEL caused an invalid page fault in module MSO9.DLL at 0167:308d0b99. Registers: EAX=00629c74 CS=0167 EIP=308d0b99 EFLGS=00010212 EBX=00000000 SS=016f ESP=00628684 EBP=0062868c ECX=00000002 DS=016f ESI=00000000 FS=593f EDX=00000008 ES=016f EDI=00629c74 GS=0000 Bytes at CS:EIP: f3 a5 8b ca 83 e1 03 f3 a4 5f 5e 5d c3 8b 4d 10 Stack dump: ffffffd8 00000000 00629db...

Error when recovering a mailbox
Suddenly when attempting to recover deleted mailboxes the following error occurs: The specified directory service attribute or value does not exist Facility: LDAP Provider ID no: 8007200a Exchange System Manager This is when I click on reconnect on the mailbox with the red mark on it and after I put in the alias on the user I get the error message. This is happening for any mailbox I try and bring back. On Wed, 20 Sep 2006 08:15:02 -0700, WaltK <WaltK@discussions.microsoft.com> wrote: >Suddenly when attempting to recover deleted mailboxes the following error >occurs: > &...

Several errors on CRM Log
Hi, In my application log, i found out there are some errors as following: MSSQLSERVERADHELPER '0' is an invalid number of start up parameters. This service takes two start up parameters. SQLSERVERAGENT Unable to read local eventlog (reason: The event log file has changed between read operations). MSCRM Platform Error Report -------------------------------------------------------------------------------------------------------- Error: Access is denied. Error Message: Access is denied. Error Details: You do not have sufficient permissions or access rights to perform this opera...

Exchange
I have this problem , i would really need some help: When i`m creating a user account in Active Directory the mailbox that i setup there it doesn`t appear in Exchange, even if i don`t get any erros. Everything worked just fine until a few days ago, i don`t seems to find the cause and the soltuion. Thank you very much ! send mail to the mailbox. only on first request, the mailbox is created in the database, and you will see it in ESM. -- Omer Maydan MCSE,Security+ "Dragos Margoi" <dmargoi@memrb.ro> wrote in message news:4136B0CE.80108@memrb.ro... > I have this probl...

Error " The messaging interface has returned an unknown error "
This is a multi-part message in MIME format. ------=_NextPart_000_02DF_01C6070C.ECB58980 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I got the below err msg whenever I try to print the calendar. " The messaging interface has returned an unknown error. If the problems persists, restart Outlook." I have restarted my OL2003 prg but no joy.=20 Appreciate if anyone can enlighten me? Thanks=20 Jimmy ------=_NextPart_000_02DF_01C6070C.ECB58980 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding...

invalid handle
hi frds i m very new in vc++ and i m founding too much difficulty in this so plz can any one solve this problem .... actully i m inserting a image in list box using this code ........ everything is returning write thing still it is saying invalid handle after ImageList_Add(hList,m_hBmpNew,0); when i m going to dibug it plz help me BOOL Fun() { // Create 256 color image lists HIMAGELIST hList = ImageList_Create(32,32, ILC_COLOR8 , 8, 1); HBITMAP m_hBmpNew = (HBITMAP) LoadImage( AfxGetInstanceHandle(), // handle to instance "c:\\img.bmp", /...

Spooler error
We have Office 97 on our computer, but do not have the original discs anymore. When I try to open Outlook, I get the following error "MAPI Spooler could not be started". Any ideas? ...

Thunking a 32-bit HANDLE to a 64-bit HANDLE
Currently I am converting a 32-bit WDM driver to a 64-bit KMDF driver that will continue to work with our 32-bit DLL and our customer's 32-bit applications. The sample code for thunking 32-bit items shows the following Buffer->Handle = (HANDLE)Buffer32->Handle; see: http://msdn.microsoft.com/en-us/library/aa489604.aspx Buffer32->Handle is declared as UINT32 Handle The driver compiler issues error number C4312 for this cast. The code that I have adopted (to get it to compile) is: handlerInputs.hEvent = (HANDLE) (ULONG_PTR) p_handlerInputs3...

Access97 on XP
Using a db built in in Access97 with Outlook Express was fine until we moved from Windows2k to XP. Now a simple DoCmd.Send.object on a control form return Error 2287. All I want is for the headers on the email be filled by Email,- compose the object field showing some particulars filed on the record and a simple Hello whoever in the body. This has been working fine for years but now nada. There seems to be lot of exchange on the subject but I have not found one defnitive (and easy) answer Can any1 help Merci ...

Item allocation Error
We have run the Inventory-->Utility--Item Reconciliation for a particular Item. It has shown the result with previuos qty and adjusted qty. When we go to Item Inquiry for that item Qty hand 37, QtyAllocatted 2 and Qtyavailable show 35. But when you click allocation link It doesnt display any record containing Qty Allocated 2. We have check SOP10200, SVc..But couldnot find any record for that Item/site. What will be the area which is allocating the item. We are using manufacturing module along with SOP,POP and IVC module Let us know any other areas need to check. Regards Amit Hi, ...

Where is the error?
Hello: This evening I make a cpp file(it's a sample of c++ primer,chapter 3,string.cpp).The outline of it is below: #include <iostream> // line 1 class string; istream& operator>>(istream&,string&); // line 4 ostream& operator<<(ostream&,const string&); class string { public: string(); virtual ~string(); }; string::string() { //Do something } string::~string() { /...

Connection Error
HI all, I have 3 RMS systems running at my store. All the credit card transaction are processed through RMS.I didnt had any problems for a long time but now with only one register when i swipe a credit card it says "Connection error:network error" but internet connection is good so is the local area network.Database connection is fine as it gets the information for the items from the server. If any kind of solution is there please feel free to comment Thanks This is a multi-part message in MIME format. ------=_NextPart_000_0093_01C927B2.C5CF9F30 Content-Type: text/plain; c...

Error number: 0x8002802B
I keep getting the following error when I try to do a windows update. Error number: 0x8002802B "Dan" <Dan@discussions.microsoft.com> wrote in message news:684CF229-D392-4C2D-961E-9304386D3F3A@microsoft.com... > I keep getting the following error when I try to do a windows update. > Error > number: 0x8002802B Dan, for the benefit of those who will attempt to help you, try giving some more information, like, OS, & Service Pack#. -- "Don't pick a fight with an old man. If he is too old to fight, he'll just kill you." On...

Greatplains 7.50g3 run on Citrix error
Dear I have out the Greatplains 7.5g3 client on citrix run. When I run the GP is fine. But I try access to Inquiry / Sales module / trx by customer module, i cannot view the transaction details in "by customer", also i try select another customer , only show year 01-02 , cannot show year 04-05. after I try trx by document is working. I don't know what problems. Because in internal LAN access just view is no problems. Only crash citrix mode. Any GP expert can you help me. Thanks Jackie Wong ...

Error opening spreadsheets
Please help, I really am stuck! Spreadsheets on my computer don't always open when I click on them. Sometimes when I open spreadsheets across a network I get "not enough memory". Yet memory usage is only about 20%. Much older machines don't seem to have a problem. I am using Win2000 SP3 and Office XP Pro with all of the latest patches. Pentium 2.4 and 512 Mb memory ...

owa error from front end
I am getting this kind of display from my owa front end. I can access owa in the back end no problems. IE is displaying this http://picasaweb.google.com/tdubb123/Owa/photo#s4990970159568977938 Any idea what is going on? Hi, Have you seen and followed this: http://support.microsoft.com/?kbid=280823 Leif "tony" <none@none.com> wrote in message news:%23a8Jumq%23GHA.4376@TK2MSFTNGP03.phx.gbl... >I am getting this kind of display from my owa front end. I can access owa >in the back end no problems. IE is displaying this > > http://picasaweb.google.com/tdubb...

Daily Bank Sweep
New GP Client that is reconciling for the first time their operating account. We have entered the last reconciled balance and dates as of 12/31/08. Operating account gets swept every night and redeposited the next day. With each sweep interest is calculated. My question is how to handle the last sweep of the month, which is a sweep in transit. We have tried entering as a decrease adjustment (not posting to GL as it should not effect the GL balance). We have also tried entering as an adjustment to see what the effect is. We don't even come close to the bank ending balance. Pl...

Handling blank data points
I have a chart which is has "" in a formula to clear contents when not applicable to show error. This results in the chart treating the cell as 0 and therefore ugly result in data point. All other post responses to this type of question suggest using NA() and conditional formation to hide the error.. Unfortunately when this is used this screws up my SUM() and AVG() formulas.. Any other suggestions? Thanks Jo Hi Jo, I don't know if this is the best way but what I have done under similar circumstances is use the #N/A for the chart series data column and then I use a hel...

ConfigDSInteg error
Hi all, We have Exchange 5.5 and are now deploying Exchange 2003. We've used the ExDeploy tool to Exchange 2003 in coexistence with the old 5.5 server. We've completed ForestPrep, DomainPrep, ADC install and setup and finally completed the Exch2003 install. Both ADC and the new Ex2003 run on a WS2003-based server, named "ES-0". SP1 has not been applied yet. We're now running the integrity check tools and we're stuck at the results of the ConfigDSInteg tool. The results of the e2kdsinteg.log file are: --------> Config results from (objectClass=*) ... DN = CN=Conf...

Generate Mappings error
After making some customizations to the lead, contact, account and opportunity entities I used the generate mappings functionality to auto-generate mappings on the relationships. Since I used the same naming conventions, this appeared to work great. When I tried to convert a lead though, I received the following error: Duplicate Record - A duplicate record with these values already exists... Here's the stack trace: [COMException (0x80040237): Exception from HRESULT: 0x80040237.] Microsoft.Crm.Platform.ComProxy.CRMContactClass.Create(CUserAuth& Caller, String Contact) +0 Mi...

Error when saving
I'm using Excel 2002/XP and getting the following error when trying to save. "Excel could not save all the data and formatting you recently added to filename.xls" I have seen a Knowledge Base article that this can occur in 2000 when using a heavy amount of conditional formatting. But that post says when formatting is done to more than 2,050 rows. And I'm not formatting that many rows. Mine is approx. 550+ Anyone know what is going on here and what I can do about it? Thanks, David do you format lots of cells individually? XL: Error Message: Too Many Different Cel...