owners --> cars" would not accomplish that even if were NOT NULL. The ideal answer, if I understand your goals correctly is to use "ON DELETE CASCADE" for all foreign key constraints as you seem to have tried. Unfortunately, SQL Server gets confused when you try to do this kind of thing (IBM, Oracle, and even MS Access handle it just fine). Hopefully they'll get it right in the next version. For now you can accomplish this by dropping the cascade action on in favor of a delete trigger on like this: CREATE TABLE countries ( country_cd CHAR(2) NOT NULL PRIMARY KEY, country_name VARCHAR(50) NOT NULL UNIQUE); CREATE TABLE owners ( owner_id INT NOT NULL PRIMARY KEY, owner_name VARCHAR(50) NOT NULL, country_cd CHAR(2) NOT NULL REFERENCES countries(country_cd) ON DELETE CASCADE); CREATE TABLE cars ( vin CHAR(17) NOT NULL PRIMARY KEY, country_cd CHAR(2) NOT NULL REFERENCES countries(country_cd) ON DELETE NO ACTION, -- action replaced by trigger owner_id INT NULL REFERENCES owners(owner_id) ON DELETE CASCADE); CREATE TRIGGER countries_delete_trigger ON countries INSTEAD OF DELETE AS -- Note to programmer: this action ought to be handled by defining the foreign key -- constraint on with ON DELETE CASCADE. If support is -- added in a future version, this trigger should be dropped and the referential constraint -- should be correctly defined. DELETE FROM cars WHERE country_cd IN (SELECT country_cd FROM deleted); DELETE FROM countries WHERE country_cd IN (SELECT country_cd FROM deleted); Hope that helps, Rich "Terry" wrote in message news:google.com...[ref] > Hi, > I'm kinda new to this SQL Server world so please bear with me. I'm > using SQL Server 2000. I'd like to get some help with the following > example. > > Consider the following tables: > > > CARS > ---- > CAR_ID (pk) > CAR_NAME > OWNER_ID (fk) > COUNTRY_ID (fk) (manufacturing country) > > > OWNERS > ------ > OWNER_ID (pk) > OWNER_NAME > COUNTRY_ID (fk) (where he was born) > > > COUNTRY > ------- > COUNTRY_ID (pk) > COUNTRY_NAME > > > A) As you can see, the Country table is used by the 2 others. > B) I want to set it so that if I delete a Country, all Cars and Owners > of that country will be deleted (Cascade). > C) I also want that, if an Owner is deleted, all his cars get deleted > too (Cascade). > > The problem is that, in Cars the OWNER_ID (fk) is nullable. Not all > Cars will have an Owner.So I cannot rely on the cascade > Country->Owner->Car. I'm stuck there. If I create the 2 relationships: > (from Cars to Country and from Owners to Country) first, then I cannot > create the relationship between Cars and Owners (I get the 'may cause > cycles or multiple cascade paths' message) > > Any suggestions will be greatly appreciated. Thanks![/ref] [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => <7a3cf1b5.0308081730.ce24899@posting.google.com> [htmlstate] => on_nl2br [postusername] => Rich [ip] => richdillon@mind [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 2 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> owners --> cars" would not > accomplish that even if were NOT NULL. > > The ideal answer, if I understand your goals correctly is to use "ON DELETE > CASCADE" for all foreign key constraints as you seem to have tried. > Unfortunately, SQL Server gets confused when you try to do this kind of > thing (IBM, Oracle, and even MS Access handle it just fine). Hopefully > they'll get it right in the next version. For now you can accomplish this > by dropping the cascade action on in favor of a delete > trigger on like this: > > CREATE TABLE countries ( > country_cd CHAR(2) NOT NULL PRIMARY KEY, > country_name VARCHAR(50) NOT NULL UNIQUE); > > CREATE TABLE owners ( > owner_id INT NOT NULL PRIMARY KEY, > owner_name VARCHAR(50) NOT NULL, > country_cd CHAR(2) NOT NULL > REFERENCES countries(country_cd) > ON DELETE CASCADE); > > CREATE TABLE cars ( > vin CHAR(17) NOT NULL PRIMARY KEY, > country_cd CHAR(2) NOT NULL > REFERENCES countries(country_cd) > ON DELETE NO ACTION, -- action replaced by trigger > owner_id INT NULL > REFERENCES owners(owner_id) > ON DELETE CASCADE); > > CREATE TRIGGER countries_delete_trigger > ON countries INSTEAD OF DELETE > AS > -- Note to programmer: this action ought to be handled by defining the > foreign key > -- constraint on with ON DELETE CASCADE. If support is > -- added in a future version, this trigger should be dropped and the > referential constraint > -- should be correctly defined. > > DELETE > FROM cars > WHERE country_cd IN (SELECT country_cd FROM deleted); > > DELETE > FROM countries > WHERE country_cd IN (SELECT country_cd FROM deleted); > > > Hope that helps, > Rich > > > "Terry" wrote in message > news:google.com...[ref] > > Hi, > > I'm kinda new to this SQL Server world so please bear with me. I'm > > using SQL Server 2000. I'd like to get some help with the following > > example. > > > > Consider the following tables: > > > > > > CARS > > ---- > > CAR_ID (pk) > > CAR_NAME > > OWNER_ID (fk) > > COUNTRY_ID (fk) (manufacturing country) > > > > > > OWNERS > > ------ > > OWNER_ID (pk) > > OWNER_NAME > > COUNTRY_ID (fk) (where he was born) > > > > > > COUNTRY > > ------- > > COUNTRY_ID (pk) > > COUNTRY_NAME > > > > > > A) As you can see, the Country table is used by the 2 others. > > B) I want to set it so that if I delete a Country, all Cars and Owners > > of that country will be deleted (Cascade). > > C) I also want that, if an Owner is deleted, all his cars get deleted > > too (Cascade). > > > > The problem is that, in Cars the OWNER_ID (fk) is nullable. Not all > > Cars will have an Owner.So I cannot rely on the cascade > > Country->Owner->Car. I'm stuck there. If I create the 2 relationships: > > (from Cars to Country and from Owners to Country) first, then I cannot > > create the relationship between Cars and Owners (I get the 'may cause > > cycles or multiple cascade paths' message) > > > > Any suggestions will be greatly appreciated. Thanks![/ref][/ref] [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => <7a3cf1b5.0308091316.2c422aa6@posting.google.com> [ref] => <7a3cf1b5.0308081730.ce24899@posting.google.com> [htmlstate] => on_nl2br [postusername] => Terry [ip] => a165287@yahoo.c [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 3 [islastshown] => 1 [isfirstshown] => [attachments] => [allattachments] => ) --> Same parent table prohibits creating relationship - Microsoft SQL / MS SQL Server

Same parent table prohibits creating relationship - Microsoft SQL / MS SQL Server

Hi, I'm kinda new to this SQL Server world so please bear with me. I'm using SQL Server 2000. I'd like to get some help with the following example. Consider the following tables: CARS ---- CAR_ID (pk) CAR_NAME OWNER_ID (fk) COUNTRY_ID (fk) (manufacturing country) OWNERS ------ OWNER_ID (pk) OWNER_NAME COUNTRY_ID (fk) (where he was born) COUNTRY ------- COUNTRY_ID (pk) COUNTRY_NAME A) As you can see, the Country table is used by the 2 others. B) I want to set it so that if I delete a Country, all Cars and Owners of that country will be deleted (Cascade). C) I ...

  1. #1

    Default Same parent table prohibits creating relationship

    Hi,
    I'm kinda new to this SQL Server world so please bear with me. I'm
    using SQL Server 2000. I'd like to get some help with the following
    example.

    Consider the following tables:


    CARS
    ----
    CAR_ID (pk)
    CAR_NAME
    OWNER_ID (fk)
    COUNTRY_ID (fk) (manufacturing country)


    OWNERS
    ------
    OWNER_ID (pk)
    OWNER_NAME
    COUNTRY_ID (fk) (where he was born)


    COUNTRY
    -------
    COUNTRY_ID (pk)
    COUNTRY_NAME


    A) As you can see, the Country table is used by the 2 others.
    B) I want to set it so that if I delete a Country, all Cars and Owners
    of that country will be deleted (Cascade).
    C) I also want that, if an Owner is deleted, all his cars get deleted
    too (Cascade).

    The problem is that, in Cars the OWNER_ID (fk) is nullable. Not all
    Cars will have an Owner.So I cannot rely on the cascade
    Country->Owner->Car. I'm stuck there. If I create the 2 relationships:
    (from Cars to Country and from Owners to Country) first, then I cannot
    create the relationship between Cars and Owners (I get the 'may cause
    cycles or multiple cascade paths' message)

    Any suggestions will be greatly appreciated. Thanks!
    Terry Guest

  2. #2

    Default Re: Same parent table prohibits creating relationship

    Terry,

    It doesn't look like the NULLability of <cars.owner_id> is an issue here.
    For example, a japanese-born owner might have a german car, or vice-versa.
    It seems that in respose to your removing Germany from the countries table,
    for example, you'd like the DBMS to delete german-born owners from <owners>
    AND their cars (regardless of country) from <cars> AND all cars made in
    Germany from <cars>. The cascade "countries --> owners --> cars" would not
    accomplish that even if <cars.owner_id> were NOT NULL.

    The ideal answer, if I understand your goals correctly is to use "ON DELETE
    CASCADE" for all foreign key constraints as you seem to have tried.
    Unfortunately, SQL Server gets confused when you try to do this kind of
    thing (IBM, Oracle, and even MS Access handle it just fine). Hopefully
    they'll get it right in the next version. For now you can accomplish this
    by dropping the cascade action on <cars.country_id> in favor of a delete
    trigger on <countries> like this:

    CREATE TABLE countries (
    country_cd CHAR(2) NOT NULL PRIMARY KEY,
    country_name VARCHAR(50) NOT NULL UNIQUE);

    CREATE TABLE owners (
    owner_id INT NOT NULL PRIMARY KEY,
    owner_name VARCHAR(50) NOT NULL,
    country_cd CHAR(2) NOT NULL
    REFERENCES countries(country_cd)
    ON DELETE CASCADE);

    CREATE TABLE cars (
    vin CHAR(17) NOT NULL PRIMARY KEY,
    country_cd CHAR(2) NOT NULL
    REFERENCES countries(country_cd)
    ON DELETE NO ACTION, -- action replaced by trigger
    owner_id INT NULL
    REFERENCES owners(owner_id)
    ON DELETE CASCADE);

    CREATE TRIGGER countries_delete_trigger
    ON countries INSTEAD OF DELETE
    AS
    -- Note to programmer: this action ought to be handled by defining the
    foreign key
    -- constraint on <cars.country_cd> with ON DELETE CASCADE. If support is
    -- added in a future version, this trigger should be dropped and the
    referential constraint
    -- should be correctly defined.

    DELETE
    FROM cars
    WHERE country_cd IN (SELECT country_cd FROM deleted);

    DELETE
    FROM countries
    WHERE country_cd IN (SELECT country_cd FROM deleted);


    Hope that helps,
    Rich


    "Terry" <com> wrote in message
    news:google.com... 


    Rich Guest

  3. #3

    Default Re: Same parent table prohibits creating relationship

    Hey Rich,
    Thanks for introducing me to the wonderful world of triggers :) Your
    solution works great, thanks for taking the time to write it all. I
    really appreciate it.


    Terry

    "Rich Dillon" <com> wrote in message news:<phx.gbl>... [/ref]
    Terry Guest

Similar Threads

  1. Datatgrid - Parent Child Relationship
    By steroche in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: August 8th, 10:19 PM
  2. Check Parent-child relationship in datagrid
    By Grey in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: March 18th, 07:43 PM
  3. Parent/Child Relationship
    By Bob Day in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: November 6th, 01:13 AM
  4. OO parent/child relationship
    By Gerard in forum PHP Development
    Replies: 20
    Last Post: October 7th, 05:52 PM
  5. Replies: 2
    Last Post: August 12th, 04:38 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •