Prevent a user logging in twice in a database table

Question

I have a table with these columns:

user ID, login time, logout time

When a user logs in if that user already has a row with a login time but no logout time then he relogs in using that row, otherwise it creates a new. The problem is sometimes the stored procedure runs twice and I get two entries.

Is there a constraint I can use for this situation to prevent it from being possible?

Possible duplicate of [Solutions for INSERT OR UPDATE on SQL Server](https://stackoverflow.com/questions/108403/solutions-for-insert-or-update-on-sql-server) — Anthony L, Feb 22 '18 at 04:53
Punch in punch out is a much harder problem than it seems at first glance. basing your in / out on the order of the insertion of the rows is quite error prone in many different ways. It's best to have an explicit indication from the software using the database if the punch is in or out. — Zohar Peled, Feb 22 '18 at 05:23

score 2 · Accepted Answer · answered Feb 22 '18 at 05:03

Try the following

CREATE TABLE UserLog(
  ID int NOT NULL IDENTITY PRIMARY KEY,
  UserID int NOT NULL,
  LoginTime datetime NOT NULL DEFAULT SYSDATETIME(),
  LogountTime datetime
)

-- unique index with where clause
CREATE UNIQUE INDEX UK_UserLog_UserID ON UserLog(UserID) WHERE LogountTime IS NULL
GO


INSERT UserLog(UserID)
VALUES(1)

-- Cannot insert duplicate key row in object 'dbo.UserLog' with unique index 'UK_UserLog_UserID'. The duplicate key value is (1).
INSERT UserLog(UserID)
VALUES(1)


UPDATE UserLog
SET
  LogountTime=SYSDATETIME()
WHERE UserID=1
  AND LogountTime IS NULL


INSERT UserLog(UserID)
VALUES(1)


SELECT *
FROM UserLog

@Jack Reilly - I'm very glad I could help you ) – Sergey Menshov Feb 22 '18 at 05:43 — Sergey Menshov, Feb 22 '18 at 05:43

Prevent a user logging in twice in a database table

1 Answers1