Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2009

    Unanswered: foreing keys referencing same primay key

    I have table
    A(id int identity(1,1) PRIMARY KEY)
    and table
    B(id1 int, id2 int, primary key(id1, id2))
    I want to set id1 and id2 as foreign keys that would both reference id in table A.
    When i do this i cannot enforce Cascade on Delete and Update actions on one of the Foreing Key constraints. I receive the following message:
    Unable to create relationship 'FK_B_A1'.
    Introducing FOREIGN KEY constraint 'FK_B_A1' on table 'B' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
    Could not create constraint.

    I need to preserve these tables with their structures and, obviously, when i delete a record from A with id = 1, for instance, all records in B that have either id1 = 1 or id2 = 1 need to be deleted.

    Any ideas on how to proceed?

    Thank you

  2. #2
    Join Date
    Feb 2004
    One Flump in One Place
    That is correct - you can create triggers to enforce cascading deletes for this sort of thing. Other people prefer to manage this in stored procs and only allow data manipulation via these, but I prefer the idiot-proofing of triggers myself.
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2009
    thank you, i solved the problem using triggers

Posting Permissions

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