TSQL - How to check a value against a value in a different column before inserting?

Question

Is there a way to implement a CHECK constraint that checks a value against another value in a different column in the same table? Specifically, I want to ensure that a "checkout" date value to be inserted is greater than the "checkin" date value in the table before inserting the row.

I may be missing some details, so please let me know if this is the case.

Answer 1

Yes, MSSQL Server (and others) allow CHECK CONSTRAINTS on the table - so that you can compare columns:

ALTER TABLE tbl ADD CONSTRAINT chkCheckout_GT_Checkin
    CHECK (Ckeckout > Checkin)

Answer 2

You could use a check like:

ALTER TABLE dbo.myTable ADD CONSTRAINT CK_myTable_CheckoutGreaterThanCheckin
CHECK (dbo.CheckDates()>1)

Then add a function like:

CREATE FUNCTION CheckDates()
RETURNS int
AS 
BEGIN
    DECLARE @retval int
    SET @retval = (SELECT COUNT(*) FROM myTable WHERE checkout > checkin)
    RETURN @retval
END;

Those may not work as written (sorry don't have access to an SQL engine at the moment.)

Or you may want to include the check in the query so that you don't have to use error handling in your code for when the check fails.

If you just want the insert to fail you could extend the where clause to be something like:

INSERT INTO myTable (checkout) 
VALUES(@checkout) 
WHERE @checkout > @checkin AND IDcolumn = @IDcolumn

If you want to return some type of indicator that there was a problem you could either use something like above but add SELECT @@ROWCOUNT or you could use just check the value in advance:

DECLARE @var int
SET @var = (SELECT count(*)
FROM myTable
WHERE @checkout > checkin AND IDcolumn = @IDcolumn)

@var would then be equal to 1 if the value passed.

Answer 3

You can try to use triggers.