Unanswered: 2 tables referred to eac other twice by 2 to 1 primary key
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.
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 ?