Trigger to Only Allow a Single Null Value Per Item ID

I am working on a database for equipment management, where users can sign in and out equipment. I have a table called TBL_Usage which keeps track of this. When an item is signed out, a new record is created in this table with the sign out date, and when it is signed back in, a sign in date is entered in the same record.

For the purposes of this question, the table looks like this:
TBL_Usage(Date_Out, Date_In, Equipment_ID)

  • SQL Server: How do I maintain data integrity using aggregate functions with group by?
  • Entity Framework: Reusing foreign key
  • Showing the full list IDs when OUTER JOIN two tables
  • Is there a way to get a boolean without casting in SQL Server?
  • MSSQL: How do you script Stored Procedure creation with code?
  • Why select Top clause could lead to long time cost
  • Now an item can’t be signed out twice at the same time, therefore for each Equipment_ID, there can only be a maximum of one record where Date_In is null. I want to create a trigger/constraint to deal with this and am not quite sure how to set it up.

    One Solution collect form web for “Trigger to Only Allow a Single Null Value Per Item ID”

    You can do it with a filtered unique index.

    create unique index IX_TBL_Usage_Equipment_ID 
      on TBL_Usage(Equipment_ID) 
      where Date_In is null
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.