Скачать или смотреть How to Implement a CHECK Constraint for Conditional Nulls in SQL Tables

Learn how to define a `CHECK` constraint in SQL to ensure that only one of two columns can be populated while the other is null. This guide provides examples and clear explanations.
---
This video is based on the question https://stackoverflow.com/q/63850286/ asked by the user 'Alex' ( https://stackoverflow.com/u/12984234/ ) and on the answer https://stackoverflow.com/a/63850307/ provided by the user 'Gordon Linoff' ( https://stackoverflow.com/u/1144035/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Both columns can't have nulls at the same time, only one per time, how to define this constraint?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Implement a CHECK Constraint for Conditional Nulls in SQL Tables

Introduction

When designing a database table, it's essential to enforce rules that maintain data integrity. One common scenario is when you want to ensure that only one of two specific columns can be filled at any time while the other must remain empty (null). A classic example of this would be feedback columns for clients and barbers in a feedback system.

In this guide, we will explore how to define this constraint in an SQL table using a CHECK constraint.

The Problem

Suppose you have a table called tblFeedbacks that is designed to store feedback from both clients and barbers. The goal is to ensure that only one of the two columns—ClientFeedback or BarberFeedback—contains a value of 1 (to indicate positive feedback), while the other is null.

Here is the basic structure of the table:

[[See Video to Reveal this Text or Code Snippet]]

Goals:

Allow ClientFeedback to have a value of 1, implying the client provided feedback.

Allow BarberFeedback to have a value of 1, implying the barber provided feedback.

Ensure that when one feedback column is filled, the other remains null.

The Solution: Using a CHECK Constraint

To enforce the required relationship between the ClientFeedback and BarberFeedback columns, we will use a CHECK constraint that ensures that only one of these columns can contain a value of 1 at the same time.

Implementation Steps

Define your table with a CHECK constraint:
Here's how you can structure your CREATE TABLE statement:

[[See Video to Reveal this Text or Code Snippet]]

Another optional constraint for NULL values:
If you specifically require that at least one of the feedback columns must not be null, you can add a condition as follows:

[[See Video to Reveal this Text or Code Snippet]]

Breakdown of the CHECK Conditions

The first condition checks that if ClientFeedback is 1, then BarberFeedback must be 0, and vice versa.

The second optional condition ensures that at least one of the columns has a non-null value.

Conclusion

By applying a CHECK constraint, you can effectively control the data entered into your SQL table, ensuring only one feedback type is recorded at any given time. This simple yet powerful mechanism helps maintain the integrity and intended functioning of your database.

Feel free to incorporate these strategies into your own database designs, and always remember the importance of enforcing clear rules for your data.