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?