Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250

    Question Unanswered: Introducing FOREIGN KEY constraint

    "Introducing FOREIGN KEY constraint 'FK__APPLICANTMA__SEX__414EAC47' on table 'APPLICANTMASTER' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints."


    ive a foreign key reference on the table APPLICANT MASTER
    of the form
    FOREIGN KEY (SEXCODE) REFERENCES
    APPLICANTSEX(SEXCODE)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,


    can any one help me WHAT THE MESSAGE MEANS ?
    Cheers....

    baburajv

  2. #2
    Join Date
    Jan 2003
    Location
    India
    Posts
    523

    Re: Introducing FOREIGN KEY constraint

    Originally posted by baburajv
    "Introducing FOREIGN KEY constraint 'FK__APPLICANTMA__SEX__414EAC47' on table 'APPLICANTMASTER' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints."


    ive a foreign key reference on the table APPLICANT MASTER
    of the form
    FOREIGN KEY (SEXCODE) REFERENCES
    APPLICANTSEX(SEXCODE)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,


    can any one help me WHAT THE MESSAGE MEANS ?

    hi,
    this link below explains it all:
    http://lists.evolt.org/archive/Week-...21/139403.html
    harshal.

  3. #3
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250

    REF INTEGRITY CONSTRAINTS

    Hi,

    that was useful information, but i have doubts,

    my table is

    ApplicantSex
    (
    SexCode tinyint,
    Sex varchar(6),
    constraint pkApplicantSex primary key(SexCode)
    )

    Applicant
    (
    AppId bigint,
    Name varchar(30),
    SexCode tinyint,
    constraint pkApplicant PRIMARY KEY (AppId),
    constraint fkApplicant FOREIGN KEY (SexCode)
    references ApplicantSex(SexCode)
    on delete no action
    on update cascade
    )


    here, i can see no "cycles" or "multiple "cascade paths"

    then why sql server says

    "Introducing FOREIGN KEY constraint 'FK__APPLICANTMA__SEX__414EAC47' on table 'APPLICANTMASTER' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints."

    pl give some info
    Cheers....

    baburajv

  4. #4
    Join Date
    Jan 2003
    Location
    India
    Posts
    523

    Re: REF INTEGRITY CONSTRAINTS

    Originally posted by baburajv
    Hi,

    that was useful information, but i have doubts,

    my table is

    ApplicantSex
    (
    SexCode tinyint,
    Sex varchar(6),
    constraint pkApplicantSex primary key(SexCode)
    )

    Applicant
    (
    AppId bigint,
    Name varchar(30),
    SexCode tinyint,
    constraint pkApplicant PRIMARY KEY (AppId),
    constraint fkApplicant FOREIGN KEY (SexCode)
    references ApplicantSex(SexCode)
    on delete no action
    on update cascade
    )


    here, i can see no "cycles" or "multiple "cascade paths"

    then why sql server says

    "Introducing FOREIGN KEY constraint 'FK__APPLICANTMA__SEX__414EAC47' on table 'APPLICANTMASTER' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints."

    pl give some info
    when do u get this message while delete or while update??
    while updation there is a cascade from applicant master to applicant to applicant sex.

  5. #5
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250

    Question FK CONSTRAINTS

    I GET THIS MESSAGE WHILE RUNNING MY SCRIPT FILE (.SQL FILE) FROM THE QUERY ANALYZER.


    ONE MORE THING,

    "while updation there is a cascade from applicant master to applicant to applicant sex."

    i need to know one thing, the "applicantmaster" referes to sexcode in "applicantsex" and

    " ON UPDATE CASCADE" means any updation in applicant sex must be cascaded
    and not the reverse..(am i correct?)

    i hope i made my point clear

    thanks for the advice
    Cheers....

    baburajv

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    constraint fkApplicant FOREIGN KEY (SexCode)
    references ApplicantSex(SexCode)
    on delete no action
    on update cascade
    'on delete no action' means you will get an error when you try to delete a record from ApplicantSex that is already used (referenced) in Applicant. That is a desired behaviour.

    'on update cascade' means that if you alter the PK (SexCode) of ApplicantSex to another value, all the referencing Applicant.SexCode values will also be altered in the same way.
    ApplicantSex.SexCode is a tinyint why would you ever want to update that? What would be the great advantage of having "male" be for instance 14 instead of the former 2 or 'female' 23 instead of 1?

    I never use 'on update cascade'. If your PK is a number, there is no reason to change it over time. If you would use a character code CHAR(1) like 'B'oy or 'G'irl, then I can imagine someone will be pushing to display it as something else, like 'M'ale or 'F'emale. And then a cascaded update would be useful.

    Suppose some processes in your program use the gender to execute different code, like If gender == 'B' then ... else if gender == 'G' then ... else ... end if. That code too would need to be changed after the invisible-for-the-user 'on update cascade', yuk.

    That's why I keep the code that will be displayed in a separate column, away from the numeric PK.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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