Search code examples
sql-serverdata-integrity

Preventing inapropriate rows from being inserted into SQL Server database

I have a table for tent rents with the following columns: 

RentID, DateStarted, DateEnded, CustomerID, etc.

DateStarted is always entered at the beginning of the rental period, DateEnded is initally NULL and only gets filled when the rental period expires. CustomerID is the ID of the customer who rented the tent.

What I need to do is prevent users from inserting new rows for any given customer (denoted by CustomerID) if that customer has any rents with DateEnded=NULL.

I don't know how to do this. I was thinking of a Check constraint, but they don't allow me to see other rows. I was thinking of Unique constraints, but don't know if they could be used in this case. I was thinking of On-Insert trigger, but I'm not sure if I can cancel the insert if the condition is not met.

Thanks for any help.

Solution

  • If you are on SQL Server 2008.

    CREATE UNIQUE INDEX ix ON Rents(CustomerID) WHERE DateEnded IS NULL

    Earlier Versions you can use an indexed view (For this option various SET options need to be configured correctly for connections that update the base table but in SQL Server 2005 they probably will be already.)

    CREATE VIEW dbo.ConstrainRents
WITH SCHEMABINDING
AS
SELECT CustomerID
FROM dbo.Rents 
WHERE DateEnded IS NULL

GO

CREATE UNIQUE CLUSTERED INDEX ix ON dbo.ConstrainRents(CustomerID)