Seems Unavoidable multiple cascade paths. How to avoid?

SuryaPrakash Patel via SQLMonster.com

Hello,

There are three tables:

OS-GroupOFCompanie s (Table1)
GoC_GroupOFComp aniesID (PK)

OS-Organization (Table 2)
Org_Organizatio nID (PK)

OS-UnitAddress (Table 3)
Unit_UnitAddres s (PK)
Scenario:

(1)GoC_GroupOfC ompanies has -one to many- relationship with Org_Organizatio nID.

(2)GoC_GroupOfC ompanies has -one to many- relationship with Unit_UnitAddres s.

(3)Org_Organiza tionID has -one to many- relationship with Unit_UnitAddres s.

Following Error message appeared after trying to save the relationship (3) described above.

'OS-Unit-UnitAddress' table saved successfully
'OS-Organization' table
- Unable to create relationship 'FK_OS-Organization_OS-Unit-UnitAddress'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Introducing FOREIGN KEY constraint 'FK_OS-Organization_OS-Unit-UnitAddress' on table 'OS-Organization' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See previous errors.

NB: ON UPDATE is much needed.

How to manage the situation?

Please guide.

Thanks

SuryaPrakash

*************** *************** ***********
* This message was posted via http://www.sqlmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse....935efcabb55ee9
*************** *************** ***********

Jul 20 '05 #1

Subscribe Reply

8229

Hugo Kornelis

On Tue, 09 Nov 2004 23:11:11 GMT, SuryaPrakash Patel via SQLMonster.com
wrote:

Hello,

There are three tables:

OS-GroupOFCompanie s (Table1)
GoC_GroupOFComp aniesID (PK)

OS-Organization (Table 2)
Org_Organizatio nID (PK)

OS-UnitAddress (Table 3)
Unit_UnitAddres s (PK)
Scenario:

(1)GoC_GroupOf Companies has -one to many- relationship with Org_Organizatio nID.

(2)GoC_GroupOf Companies has -one to many- relationship with Unit_UnitAddres s.

(3)Org_Organiz ationID has -one to many- relationship with Unit_UnitAddres s.

Following Error message appeared after trying to save the relationship (3) described above.

'OS-Unit-UnitAddress' table saved successfully
'OS-Organization' table
- Unable to create relationship 'FK_OS-Organization_OS-Unit-UnitAddress'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Introducing FOREIGN KEY constraint 'FK_OS-Organization_OS-Unit-UnitAddress' on table 'OS-Organization' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See previous errors.

NB: ON UPDATE is much needed.

How to manage the situation?

Please guide.

Thanks

SuryaPrakash

Hi SuryaPrakash,

Depends on the reasons for these relationships. Let me give a simplified
example, using different tables: Person, City, Country.

Case 1:

There's a one to many relationship between Person and City: "Person lives
in City".
There's a one to many relationship between City and Country: "City is
located in Country".
There's a one to many relationship between Person and Country: "Person
lives in Country".

In this case, the third relationship is implied by the first and second
relationships; it should not be stored seperately, as that would introduce
redundancy in your model.
Case 2:

There's a one to many relationship between Person and City: "Person lives
in City".
There's a one to many relationship between City and Country: "City is
located in Country".
There's a one to many relationship between Person and Country: "Person has
planned a holiday to Country".

In this case, the third relationship is not related to the either of the
other relationships. You can't just omit it, as you would lose data that
you need for your application. So you'll have to store it.

In the design phase, you should just add the ON UPDATE / DELETE action
that you want. There are three common on update/delete action: no action,
nullify or cascade. Design what you need, regardless of which option is
available in your choisen DBMS.

If you have to implement on SQL Server, you'll have to workaround some
limitation. Nullify is not available at all; cascade is available but has
some limitations. That means that you might have to use triggers to
implement the on update/delete effects that SQL Server can't offer. You
should of course use the builtin options whereever you can, but if SQL
Server doesn't offer an option, you'll have to roll your own.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Jul 20 '05 #2

SuryaPrakash Patel via SQLMonster.com

Dear Hugo

Thank you very much....

Your Suggestions are good..

Scenario is :
User will enter data of an address in following manner

1 Address Line1**/
2 Address Line2**/
3 Address Line3**/

4 Area**/
5 ZIP**/
6 Village/Town**/
7 County**/
8 District
9 State/Province**/
10 Country*
** optional
*Compulsory
Fields 1 to 3 are in principle is one field.

Fields 10 to 4 are having many to one relationships.
either 5 & 6 is compulsory.
Feature required is whenever a user types area automatically remaining fields should appear if it is stored previously in the database....
Please Guide
SuryaPrakash Patel

--
Message posted via http://www.sqlmonster.com

Jul 20 '05 #3

Hugo Kornelis

On Mon, 29 Nov 2004 03:35:02 GMT, SuryaPrakash Patel via SQLMonster.com
wrote:

(snip)

Feature required is whenever a user types area automatically remaining fields should appear if it is stored previously in the database....

Hi SuryaPrakash,

Things like that can only be accomplished in the front-end. E.g. if you're
using Access, you create VBA code to fill the input fields you want to
fill and use one of the events of the area field (OnChange?) to execute
that code when needed.

However, you might first want to look into your design. It looks as if
you're storing redundant data. If the county is functionally dependent on
the area, you should only store the area in the addresses table, not the
county as well. You can always have a view that joins address, area and
county together.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Jul 20 '05 #4

Similar topics

2777

Multiple Deletions From Different Tables in SQL Server Trigger

by: bmccollum | last post by:

I have written a trigger that's supposed to go out and delete corresponding records from multiple tables once I delete a specific record from a table called tblAdmissions. This does not work and I'm not sure why... Here's the code that's supposed to run, let's say, if a user (via a VB 6.0 interface) decides to delete a record. If the record in the tblAdmissions table has the primary key (AdmissionID) of "123", then the code below is...

Microsoft SQL Server

2857

Problems debugging multiple projects in IDE when projects are GAC DLLs

by: Johann Blake | last post by:

I can hardly believe I'm the first one to report this, but having gone through the newsgroup, it appears that way. I would like to open a solution in the VS.NET IDE that consists of multiple DLLs and a test application (an .EXE). The .EXE is my startup application. All the DLLs are shared components. This means that they contain a key and are stored in the GAC. When I go to run the test application in the IDE, it will not execute...

C# / C Sharp

10143

Multiple Foreign Keys on Same Table

by: FreeToGolfAndSki | last post by:

Hi, I have an Orders Table that has Employee1 and Employee2 (one is the sales rep the other is the telemarketing rep) Both of these fields need to cascade update against the Employees table. I can't seem to create the desired relationship in a Diagram and I'm not sure how best to set this up. Any ideas? Thanks in advance...

Microsoft SQL Server

6402

Nexted Tables, Preventing Cascade

by: Blue Apricot | last post by:

How can I prevent certain Class properties from cascading from a parent table to its children? Specifically, I have a table that acts as a container (I call it class="page"), with a certain border, background color, padding, etc. I am finding that the tables that it contains "inherit" certain attributes, like padding, which I do not want them to do. Can I prevent this "cascade"?

HTML / CSS

1417

Issue with Multiple Foreign Keys in MSSQL 2005

by: ckiraly | last post by:

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...

Microsoft SQL Server

3926

Multiple applications/multiple web.configs: how to structure a big collection of (seemingly) nested web applications?

by: | last post by:

I'm seeking (probably basic) guidance on the right way to split a large site that's supposed to represent one domain(mydomain.org) into many small VS.NET projects, and how to avoid issues with multiple web.config files leading to the error: "It is an error to use a section registered as allowDefinition = 'MachineToApplication'"... I'm fairly new to VS.NET and my sloppy first solution was to make one huge solution/project with just one...

ASP.NET

1810

Dependency Loop seems unavoidable

by: Tim | last post by:

Dear All, Dependency should be not looped. However, I think it unavoidable. For example, I have two classes: Bank and People as follows class Bank { private: Set<people*m_clients; //all clients };

C / C++

1655

Multiple "Cascade Delete" rules on 1 table allowed?

by: Cirene | last post by:

Can you assist me with this database problem? I have 4 tables in my db.... Table1 (key: Table1Id) Table2 (key: Table2Id) Table3 (key: Table3Id) Table 4 has these fields: key: Table4Id Table1Id (ties it to Table 1)

ASP.NET

1225

cyclic cascade paths

by: akdemirc | last post by:

Hi, i have tree tables in my db, one for menu, one for menu items and the other authorization.. They have the following columns and relationships: Menu ----------------- id (PK), name, position MenuItem --------------- id (PK), name, parent (FK to Menu->id), position

Microsoft SQL Server

8752

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

9401

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

9257

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

9176

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

8097

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

6702

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

3221

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

2635

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

2157

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General