need advise on DELETE action

  • Hi friends

    i've a table that has 3 columns that refer to same parent table(foreign keys).am trying to following as part of action.

    ALTER TABLE dbo.u_childTable ADD CONSTRAINT

    FK_childTable_MASTER_BATCHTO FOREIGN KEY

    (

    FK_BATCHTO_MASTERID

    ) REFERENCES dbo.u_master

    (

    MASTERID

    ) ON UPDATE NO ACTION

    ON DELETE NO ACTION

    GO

    ALTER TABLE dbo.u_childTable ADD CONSTRAINT

    FK_childTable_MASTER_FEESCALER FOREIGN KEY

    (

    FK_FEESCALER_MASTERID

    ) REFERENCES dbo.u_master

    (

    MASTERID

    ) ON UPDATE set null

    ON DELETE set null

    GO

    ALTER TABLE dbo.u_childTable ADD CONSTRAINT

    FK_childTable_MASTER_SUBDEBT FOREIGN KEY

    (

    FK_SUBDEBT_MASTERID

    ) REFERENCES dbo.u_master

    (

    MASTERID

    ) ON UPDATE set null

    ON DELETE set null

    GO

    but it wont let me complaining with following error

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

    what am i missing here

  • This was removed by the editor as SPAM

  • what version of sql are you using does sql 2000 still do cascading on deletes/updates

  • sounds to me that your logic is flawed - try drawing a process flow diagram for your RFI to see if it makes sense.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • seems its bcoz of this
    "the tree of cascading referential actions must only have one path to a particular table on the cascading referential actions tree. "
     
    as explained by
     
     
  • seems only way achieve what i want here is thru "Triggers"

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply