Hi

I have a background in access and am relatively new to SQL.

I have migrated a database from access 2000 to SQL 2000 and experienced a
problem in the relationships i didn't previously have.

I have four tables A B C D each of these tables has a one field primary key.
Also each table contains two foreign keys.

When i link table A to B, B to C, C to D using the first foreign key and
selected cascade update this works perfectly and saves.

Then i introduce another table called Countries, each of the other tables
has a foreign key to the field CO_Id.

When i attempt to link these four tables to the Countries table using the
foreign key and save i get this foreign key error.

I don't understand why it thinks that it may cause cycles and i'm not
technically experienced to see how to work round it.

Any help would be greatly appreciated.

Thanks

Gillian

gdonald20 wrote:
Please post DDL so we can see exactly what your relationships are.

What error?

Chris

Hi

Sorry i wrote the error in the subject line and then forgot about it.

The error is "Foreign key may cause multiple cycles and cascades"

Gillian

Show quoteHide quote

gdonald20 wrote:
And your DDL? (see http://www.aspfaq.com/etiquette.asp?id=5006 if you
don't know what that means)

Chris

Hi

Is there anyway you can help me with out seeing the ddl?

It's just that i can't disclose who my client is and if i display that code
i will disclose who the client is.

Thanks

Gillian

Show quoteHide quote

gdonald20 wrote:
Just post some simplified DDL, enough to illustrate the relationships
between the tables. Rename the tables/columns if you have to.

Chris

CONSTRAINT [PK_W_People] PRIMARY KEY  CLUSTERED
    (
        [PE_Id]
    )  ON [PRIMARY] ,
    CONSTRAINT [FK_W_People_W_Conferences] FOREIGN KEY
    (
        [PE_CF_Id]
    ) REFERENCES [W_Conferences] (
        [CF_Id]
    ) ON UPDATE CASCADE
) ON [PRIMARY]
GO

CONSTRAINT [PK_W_Conferences] PRIMARY KEY  CLUSTERED
    (
        [CF_Id]
    )  ON [PRIMARY] ,
    CONSTRAINT [FK_W_Conferences_W_Diocese] FOREIGN KEY
    (
        [CF_DI_Id]
    ) REFERENCES [W_Diocese] (
        [DI_Id]
    ) ON UPDATE CASCADE
) ON [PRIMARY]
GO

CONSTRAINT [PK_W_Diocese] PRIMARY KEY  CLUSTERED
    (
        [DI_Id]
    )  ON [PRIMARY] ,
    CONSTRAINT [FK_W_Diocese_W_Regions1] FOREIGN KEY
    (
        [DI_RE_Id]
    ) REFERENCES [W_Regions] (
        [RE_Id]
    ) ON UPDATE CASCADE
) ON [PRIMARY]
GO

CONSTRAINT [PK_W_Regions] PRIMARY KEY  CLUSTERED
    (
        [RE_Id]
    )  ON [PRIMARY]
) ON [PRIMARY]
GO

As things are these relationships work perfectly but when i try to join
these four tables to the table called Countries i get this horrible foreign
key error.

Thanks

Gillian



Show quoteHide quote

gdonald20 wrote:
Thanks for that. I guess the first question is, why do you need to link
all of them to the Countries table? Surely you link a region to a
country, and everything that is related (directly or indirectly) to a
region is then linked to that country?

Chris

umI thought about that but i still think i have a problem.

For example if i input a persons details in the people table and fill in
their address etc and the country they stay in. If the people table isn't
linked to the countries table and for some reason i change the UK from being
country number 1 to number 3 the record in the people table won't update.

Thanks

Gillian

Show quoteHide quote

gdonald20 wrote:
What I meant was, you wouldn't need to store CountryID in the People
table because the Conference you select is indirectly related to the
country.

However you may still have a valid case to store a CountryID in those
tables (e.g. if you wanted to store their birth country). I don't see
how a FK constraint between People and Countries would conflict with
your other constraints.

But let's see the constraints that you are attempting to create on the
Countries table first.

Chris

I think i've maybe mis-understood.

I have an access front end for the users, connected to the SQL back end.

On the people form the user picks the country the person lives in from a
combo box and then the country id is stored, that's why i need to have
cascade updates.

The problem is that not all of the people will be a part of a conference
because the system has to take non-members into consideration too.

The same thing happens on the regions and and conferences etc.

Have i incorrectly designed this?

Thanks

Gillian

Show quoteHide quote

gdonald20 wrote:
No from what you've described that sounds okay. I managed to reproduce
your problem with the following:

CREATE TABLE Country(country_id INT NOT NULL PRIMARY KEY)
CREATE TABLE D( d_id INT NOT NULL PRIMARY KEY,
                country_id INT NULL FOREIGN KEY REFERENCES
country(country_id) ON UPDATE CASCADE)
CREATE TABLE C( c_id INT NOT NULL PRIMARY KEY,
                country_id INT NULL FOREIGN KEY REFERENCES
country(country_id) ON UPDATE CASCADE,
                d_id INT NOT NULL REFERENCES D(d_id) ON UPDATE CASCADE)
CREATE TABLE B( b_id INT NOT NULL PRIMARY KEY,
                country_id INT NULL FOREIGN KEY REFERENCES
country(country_id) ON UPDATE CASCADE,
                c_id INT NOT NULL REFERENCES C(c_id) ON UPDATE CASCADE)
CREATE TABLE A( a_id INT NOT NULL PRIMARY KEY,
                country_id INT NULL FOREIGN KEY REFERENCES
country(country_id) ON UPDATE CASCADE,
                b_id INT NOT NULL REFERENCES B(b_id) ON UPDATE CASCADE)

(This is what is meant by posting sample DDL btw, so that anyone can
reproduce your problem quickly).

It does appear that SQL Server just isn't as smart as it should be in
this case. There doesn't appear to be any circular references yet it
won't let you create the above.

Do you really need the cascading updates? Why would a user need to
change a country_id?

Chris

Rows are not records and you should be using the ISO country codes (you
did no research, did you?).

SQL Server is very strict and a bit dumb about finding cycles in DDL;
other products are smarter and more forgiving.  If you hae a small
number of countries, you can use a CHECK(country_code IN ('UK', 'US',
'CA', ..)) in the tables. Otherwise, you can get some ugly code with
triggers, etc.

Hi

I am actually using the ISO country codes but still wanted a id number for
each row.

In reply to Chris's post to be honest i can't think of a situation that
would require the id number of the country number to be changed.

But i am smary enough to realise that just becuase i can't think of one
right now it doesn't mean there isn't one, which would make the cascade
updates necessary.

I just don't want to not have it and then once the web application and the
access front end are deployed realise that i did need the updates.

At least not i know it's not something i have done wrong it's just SQL
doesn't like it.

Will have to think about what to do about the problem, thanks for all of the
help.

Thanks

Gillian

Show quoteHide quote

Bulk Insert from Excel
Sort Numbers in Nvarchar As Numbers
How do I filter out unwanted rows by only one different column?
Orders Per Hour Count
extracting data