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)
