469,355 Members | 2,603 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,355 developers. It's quick & easy.

Seems Unavoidable multiple cascade paths. How to avoid?

Hello,

There are three tables:

OS-GroupOFCompanies (Table1)
GoC_GroupOFCompaniesID (PK)

OS-Organization (Table 2)
Org_OrganizationID (PK)

OS-UnitAddress (Table 3)
Unit_UnitAddress (PK)
Scenario:

(1)GoC_GroupOfCompanies has -one to many- relationship with Org_OrganizationID.

(2)GoC_GroupOfCompanies has -one to many- relationship with Unit_UnitAddress.

(3)Org_OrganizationID has -one to many- relationship with Unit_UnitAddress.

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
3 8002
On Tue, 09 Nov 2004 23:11:11 GMT, SuryaPrakash Patel via SQLMonster.com
wrote:
Hello,

There are three tables:

OS-GroupOFCompanies (Table1)
GoC_GroupOFCompaniesID (PK)

OS-Organization (Table 2)
Org_OrganizationID (PK)

OS-UnitAddress (Table 3)
Unit_UnitAddress (PK)
Scenario:

(1)GoC_GroupOfCompanies has -one to many- relationship with Org_OrganizationID.

(2)GoC_GroupOfCompanies has -one to many- relationship with Unit_UnitAddress.

(3)Org_OrganizationID has -one to many- relationship with Unit_UnitAddress.

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

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by FreeToGolfAndSki | last post: by
4 posts views Thread by Blue Apricot | last post: by
12 posts views Thread by Tim | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.