1

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?

5

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  
0

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
  • 1
    Actually, you can create for sure dynamic constraints based on the column values. Check my answer. – AMG Aug 20 '18 at 21:13
  • I said you can't have constraints altering column properties, not that you can't add logic to a constraint. Yours is more succinct, though. I prefer to keep business logic in a SP but YMMV – LowlyDBA - John McCall Aug 20 '18 at 23:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.