Creating Conditional Rules for Column Values

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?

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

StackExchange Database Administrators Q#215422, answer score: 7