Is there a way to make a column's nullability depend on another column's nullability?

Question

I have two columns (among others) in a database table: ExitDate and ExitReason. Our business logic requires that ExitReason be specified if ExitDate is specified. The ExitDate column needs to allow nulls since the value is not always known at the time of insert. Is there a way to make the ExitReason column allow nulls only if the ExitDate value is null? I could accomplish the effect by splitting these two columns into a separate 'exit dates' table and making them both non-nullable, but it would be nice if I wouldn't have to.

Ideas? Thanks!

Answer 1

Assuming you are on SQL Server or something similar, you can do this with a CHECK constraint on your table. (Unfortunately, MySQL parses but ignores CHECK constraints, so you'd have to use a trigger for that platform.)

If the table already exists:

ALTER TABLE ADD CONSTRAINT CK_ExitDateReason
CHECK (
      (ExitDate IS NULL AND ExitReason IS NULL) 
   OR (ExitDate IS NOT NULL AND ExitReason IS NOT NULL) 
);

If you are creating the table yourself:

CREATE TABLE dbo.Exit (
     ...

   , CONSTRAINT CK_ExitDateReason CHECK ...
);

Using a check constraint is preferable to using a trigger because:

• check constraints are more visible than triggers
• the constraint is part of the table definition, as opposed to code that is run separately, so it's logically cleaner
• I am willing to bet it is faster than a trigger too

Answer 2

I could accomplish the effect by splitting these two columns into a separate 'exit dates' table and making them both non-nullable, but it would be nice if I wouldn't have to.

That sounds like a very good solution. And if you are using MySQL then it's probably the best solution since CHECK constraints aren't supported.

Answer 3

MS Access offers another method to accomplish your goal. With the table in Design View, open the property sheet. In contrast to a Validation Rule for a field, the table rule can reference other fields in the table.

Add this as a single line for the table's Validation Rule property.

([ExitDate] IS NULL AND [ExitReason] IS NULL) 
OR ([ExitDate] IS NOT NULL AND [ExitReason] IS NOT NULL)

It's similar to the CHECK CONSTRAINT @NickChammas supplied. I put square brackets around both ExitDate and ExitReason because without the brackets Access tends to interpret them as text literal values, so adds quotes like this ... which won't work:

("ExitDate" IS NULL AND "ExitReason" IS NULL) 
OR ("ExitDate" IS NOT NULL AND "ExitReason" IS NOT NULL)

You may find this method more convenient if you want to include a user-friendly message as the table's Validation Text property to display when the Validation Rule is violated:

"Provide values for both ExitDate and ExitReason, or leave both blank."

Edit: The suggestion by @AndriyM works as a MS Access table Validation Rule:

([ExitDate] Is Null) = ([ExitReason] Is Null)

Answer 4

It is possible to use checks with MS Access, but only through ADO.

sSQL = "ALTER TABLE customer ADD CONSTRAINT CK_ExitDateReason " _
& "CHECK ((ExitDate IS NULL) = (ExitReason IS NULL))"

CurrentProject.Connection.Execute sSQL

The constraint can only be removed via ADO. However, you are free to add and delete columns (fields) without affecting the check.

It is also possible to add a check that references another table.

If you are using the table with a form, the error returned will be 3317. You can either accept the default message or supply your own like so:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    If DataErr = 3317 And IsNull(Me.ExitReason) Then
        MsgBox "Please fill in a reason"
        Response = acDataErrContinue
    End If
End Sub

Further information: Intermediate Microsoft Jet SQL for Access 2000