Creating Conditional Rules for Column Values

Question

I am currently working on the following table in SQL Server

Table: order_status_logs
    log_id      INT NOT NULL IDENTITY(1,1)
    order_id    INT NOT NULL
    status_id   INT NOT NULL
    log_date    DATE NOT NULL
    expected_by DATE NULL

In this table for the status_id column there are the following values possible:

[1] - Pending
[2] - Ordered
[3] - Backordered
[4] - Received

I would like to set up my table so that if a new record is trying to be created, expected_by goes from NULL to NOT NULL if the status_id is not equal to 4. Essentially unless the order has been received, the table will always need data for the expected_by column.

Is it possible to do this with a CONSTRAINT? Or is this something I'd need to do via a Procedure where I return an error message or something if I don't provide that column data?

Answer 1

Yes, you can create a table check constraint without going to triggers or stored procedures. This is valid for Sql Server 2008 or higher:

ALTER TABLE dbo.order_status_logs
   ADD 
   CONSTRAINT CHK_EXPECTED_BY   
   CHECK ((status_id <> 4 AND expected_by IS NOT NULL) OR status_id = 4);  
GO  

Answer 2

I would probably wrap it in a stored procedure - you can't use a constraint to dynamically change the NULL/NOT NULL state of a column.

Something like:

CREATE PROCEDURE usp_OrderLogInsert
       @OrderID INT
       ,@StatusID INT
       ,@LogDate DATE 
       ,@ExpectedBy DATE
AS
BEGIN
    IF (@StatusID <> 4) AND (@ExpectedBy IS NULL)
       BEGIN
          RAISERROR ('Expected by must be used when status ID <> 4', 11, 1);
       END
    ELSE
       BEGIN
          --Insert Statement
       END
END