Скачать или смотреть Using Check Constraints in SQL: A Guide for Beginners

Discover how to effectively use `check constraints` in SQL when working with relationships between tables like `Buildings` and `Glasses`. Learn best practices for database integrity!
---
This video is based on the question https://stackoverflow.com/q/65847089/ asked by the user 'bca' ( https://stackoverflow.com/u/8067109/ ) and on the answer https://stackoverflow.com/a/65851063/ provided by the user 'The Impaler' ( https://stackoverflow.com/u/6436191/ ) 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: Using queries in check constraints

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.
---
Using Check Constraints in SQL: A Guide for Beginners

When you start working with SQL, particularly with databases that involve multiple related tables, you might encounter some challenges regarding data integrity—especially when it comes to enforcing certain rules on how data can relate to one another. One common issue that many beginners face is how to effectively use check constraints in such scenarios.

In this post, we will explore a practical example that demonstrates how to ensure that entries in one table correctly correlate with those in another. Specifically, we'll tackle the problem of moving a glass to the correct floor within a building while guaranteeing their relationships remain valid.

The Problem

Let's assume you've created a database structure with the following tables:

Buildings

Floors

Glasses

Floors_Glasses

Your goal is to enforce a rule that a glass can only be moved to a specific floor if both the floor and the glass belong to the same building.

To check if this condition holds true during an operation, a novice might consider using a query every time they make a database change. But as you'll soon see, there is a more efficient way to enforce these rules directly within your SQL database.

Solution: Using Foreign Keys and Constraints

1. Define Your Tables

First, let's start by defining the tables with appropriate foreign keys. Proper table design is crucial as it inherently supports data integrity:

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

2. Key Concept: Reference Sharing

The important part of this structure is the reference sharing. Notice how the floor_glasses table uses the building_id in both foreign key references. This means:

The foreign key (building_id, floor_id) link ensures that the specified floor belongs to the correct building.

The foreign key (building_id, glass_id) link guarantees that the glass also belongs to the same building.

This design inherently prevents moving a glass to a floor if they aren’t from the same building.

3. Adding Check Constraints via Triggers

While our table structure helps maintain data integrity, you may want additional checks in place. For that, you can consider SQL triggers. Here’s how you might implement a constraint trigger:

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

When an update occurs that violates our intended rule, the trigger can raise an exception:

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

This approach lets you ensure even more control at the database level, significantly reducing the risk of data corruption.

Conclusion

In summary, when dealing with relationships between tables in SQL, especially regarding check constraints, enforcing your rules at the database level through foreign keys and constraint triggers is a powerful practice. While it requires some upfront effort, it ultimately saves you time and helps protect the integrity of your data.

If you're starting with SQL, keep practicing these concepts, and you’ll build a solid understanding of how data should properly interact within your databases!