0

I have two tables, Positions and Users:

Users

[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](100) NULL,
[DOB] [varchar](10) NULL,
[PositionId] [int] NULL

Positions

[Id] [int] IDENTITY(1,1) NOT NULL,
[PosName] [varchar](100) NULL,
[PosDesc] [varchar](10) NULL,
[UserId] [int] NULL

In these tables, Users.PositionId is a foreign key to Positions.Id. Positions.UserId is a foreign key to Users.Id.

In my database, a user can exist without having a position and a position does not need to be assigned to a user. HOWEVER, if a user is assigned to a position in the user's table, then I also want that position to be assigned to the user in the position's table. What I want to avoid is having User A assigned to Position B, when in turn Position B is assigned to User X.

Is there any way I can have SQL Server update the foreign key of Table B with the primary key of Table A whenever I update the foreign key on Table A (and vice-versa)?

I know that we can do cascades with foreign keys (ON DELETE, ON UPDATE, etc.). However, I don't think there is an application with this for what I want to do. The only other thing I could think of was using a database trigger, but is that really the most effective way to handle this??? I have seen a TON of topics on stack overflow about tables with foreign keys referencing each other, but I can't seem to quite find anything regarding my specific issue.

I appreciate any help on this matter!

nightmare637
  • 635
  • 5
  • 19

2 Answers2

3

I would suggest just storing the information once, in one of the tables. To avoid a position having multiple users, I would suggest:

Users:

[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](100) NULL,
[DOB] [varchar](10) NULL,

Positions

[Id] [int] IDENTITY(1,1) NOT NULL,
[PosName] [varchar](100) NULL,
[PosDesc] [varchar](10) NULL,
[UserId] [int] NULL REFERENCES users(userId);

And then an appropriate unique index:

create unique index unq_positions_userid
    on positions(userId)
    where userId is not null;

This guarantees that a user has only one position.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thank you so much for your response! Unfortunately, I need a means to access the user through just the position just as I need a means to access the position through just the user. I initially had a UsersPositions table set up the kept both of the foreign keys paired, but I felt that this added an unnecessary layer of complexity to my database. Would you happen to have any other suggestions? – nightmare637 Apr 02 '21 at 16:51
  • @nightmare637 . . . You just join between the tables to get both information. – Gordon Linoff Apr 02 '21 at 16:54
  • Yes, I wasn't suggesting it was difficult. From a programming perspective, though, it thought it might be easier and more maintainable in the long-term for me to set properties using Users.PositionId rather than going through a third table. However, I'm now starting to think what I had to begin with was the more efficient approach in the first place! – nightmare637 Apr 02 '21 at 17:07
  • 3
    @nightmare637 . . . Repeating data is less maintainable and generally not recommended unless there is a very specific reason. In this case, where you can `JOIN` on primary keys, performance would normally not be a consideration. – Gordon Linoff Apr 02 '21 at 17:09
  • Understood. Thanks again for your advice! – nightmare637 Apr 02 '21 at 17:16
  • @Gordon Linoff. nightmares comment is correct on the problem of bidirectional joins. with this design, joining from the user table to the position table on the userid would be a backwards join. you should always join from a foreign key to the primary key. not the other way around – kaladin_storm Apr 02 '21 at 18:02
1

It is considered bad practice to have two tables reference each other.

The right way to do this is to have the third table that will be a connection between the two. If one user can have multiple positions. For example:

UserPositions

    UserPositionsId int identity(1,1)
    UserId int NOT NULL REFERENCES users(id)
    PositionId int NOT NULL REFERENCES positions(id)

Here is good stack overflow post: In SQL, is it OK for two tables to refer to each other?

If the user can have only one position, and position can be assigned to only one user. this is not right answer. But I would not suggest having one position assigned to one user, it might seem that you want that right now, but in future, this is likely to change.

Filip
  • 94
  • 1
  • 8
  • 1
    I feel like an idiot. I did see that post earlier, but my foreign key fields were already nullable and the question in the post was a bit different than my issue. Your answer was what I initially had in place (only I made the primary key be a composite primary key of UserId and PositionId). I was reluctant to use this third table because I didn't want one user to have multiple positions, or one position to be assigned to multiple users. That, and some maintainability concerns on the programming side. However, after reconsidering, I do think this approach is the best after all. Thanks a bunch! – nightmare637 Apr 02 '21 at 17:14
  • 2
    Having a third table for a 1-1 relationship is not the best approach to solving this problem. – Gordon Linoff Apr 02 '21 at 17:26
  • @GordonLinoff - If the table is one to one then I agree, there is no need for the third table initially, however, this design to have one position to one user only will most likely change in the future, and redesigning the database will be harder. – Filip Apr 02 '21 at 17:31
  • @GordonLinoff - your answer should be an accepted answer and not mine. Since op is deciding that one user will always have one position, and one position will always be assigned to only one user, in that case, the third table is extra and will have to be maintained for no reason. – Filip Apr 02 '21 at 17:55
  • @Filip for this 1-1 table I would remove the userpositionsid column and then make the userid column the clustered primary key. That way both the user table and the user positions table would be in the same order on disk and so the joins would be cleaner and faster. – kaladin_storm Apr 02 '21 at 17:57
  • @kaladin_storm - this is correct in case where one user will only have one position, but for example, the user can have position Developer and Scrum master at the same time. It depends on the business domain that OP is having. But from question, Gordons answer is correct, User table should reference Positions only, and positions should have UserId as unique. – Filip Apr 02 '21 at 18:01
  • 1
    @Filip havin a unique userid in the positions table still enforces a user to only have a single position. for a user to have multiple positions you would definitely need a third table. for a user to only have a single position, but as a developer if you need to be able to join from a position to a user and a user to a position then you'd still need a third table to prevent a circular reference – kaladin_storm Apr 02 '21 at 18:09
  • @kaladin_storm - that is correct, but your previous comment suggested having userId as the primary key inside UserPositions table, which is a bad design, if a client decides to have multiple positions assigned to one user, developers will have a hard time making that happen if UserId is a primary key. – Filip Apr 02 '21 at 18:13
  • 1
    @Filip if they decided to enable a user having multiple positions then the easiest way to fix the userid being the primary key is to change the primary key to both the userid and the positionid. this would enforce a user can have multiple positions but only 1 of each position. and the data would still be stored on disk in order of userid so getting the list of positions for a user would still be fast. – kaladin_storm Apr 02 '21 at 18:16
  • @kaladin_storm - that is possible, but I am not a fan of composite primary keys. I still prefer to have a separate column just for a primary key. This is because if you want to reference this table for any reason in the future, you will have a hard time referencing both UserId and positionId. – Filip Apr 02 '21 at 19:19