Best ms-access questions in November 2011

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

Asked on Fri, 04 Nov 2011 by ralbatross sql ms-access column nullable
8 votes

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!

Answered by Nick Chammas

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