Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Posts
    2

    Red face Unanswered: 2 tables referred to eac other twice by 2 to 1 primary key

    Guys,

    Lets say i have a table called Users(UserID(PK),....) and also table called ABC (User1,User2,....). I wanted to refer the UserID from the Users table to both of the fields in the ABC table, is this some kind of design problem, bcoz SQL Server is giving the error as stated below :

    ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Introducing FOREIGN KEY constraint 'FK_ABC_Users' on table 'ABC' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
    [Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See previous errors.

    The reason for me to design in such a way is that i wanted to treat all users with different tasks as in the same table instead of creating many tables for each.

    TQ

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    May try with TRIGGER which deals better with cascade deletes, refer to books online for more information.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    May 2003
    Posts
    2

    Wink

    Thanx for the reply, Sathya, it made me much more confident in my design. But if it's not much to ask, do you think there would be any problem in such a design as mentioned earlier, where 2 fields in a table is referring to a single primary key in another table. The reason for this is to treat all the users as same in 1 single table, later distinguished by their roles. Do you think there would be an unseen problem in my design in future ?

    TQ

Posting Permissions

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