CODEWITHSUNDEEP
sqlsql-server
nightmare637
nightmare637

Reputation: 635

How can I have two tables update each other's foreign key in SQL Server?

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!

Upvotes: 0

Views: 652

Answers (2)

Filip
Filip

Reputation: 94

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269603

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.

Upvotes: 3

Related Questions