Range Check

What is a Range Check?

A range check is a validation method for databases. A range check ensures that data is between an upper and lower acceptable value, within a certain range. Range checks are useful for dates, numbers, and even strings.

Types of Range Check?

There are many different types of Range Checks:

Check digit: The check digit is used to ensure that code numbers that are originally produced by a computer are re-entered into another computer correctly.
Format check: This checks that the input data is in the right format.
Length check: this is used to make sure that the correct number of characters are entered into the field. It confirms that the character string entered is neither too short nor too long
Lookup table: this helps to lessen errors in a field with a limited list of values.
Presence check: this kind of check makes sure that an essential or required field cannot be left blank: it must be filled in. If someone attempts to leave the field blank, then an error message will be displayed, and they won’t be able to proc=eed to the next step, nor will they be able to save any other data which they have entered.
Range check: this is generally used when working with data which contains numbers, currency, or date and time values.
Spell check: This makes sure that field name and values are spelled correctly.

Range Check in SQL

Range check in SQL refers to validating that a value falls within a specified range or set of values before allowing it to be stored in a database or used in a query. This is often used to ensure data consistency and maintain data integrity. Here are some examples of range checks in SQL:

1. Using a CHECK constraint while creating a table:

Suppose you have a table named Products and you want to ensure that the price column contains values greater than or equal to 0. You can add a CHECK constraint during table creation:

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(255),
    Price DECIMAL(10, 2) CHECK (Price >= 0)
);

Now, any attempt to insert a product with a negative price will result in an error.

2. Using BETWEEN in a query to filter results:

You can use the BETWEEN keyword to filter results within a specific range. For example, if you want to select products with a price between 10 and 100, you can use the following query:

SELECT ProductID, ProductName, Price
FROM Products
WHERE Price BETWEEN 10 AND 100;

3. Using a CASE statement to enforce a range check:

In some cases, you might want to set a default value if the input value is out of range. You can use a CASE statement to achieve this. Suppose you want to update the price of a product, but you want to ensure that the price remains within the range of 0 to 1000. If the new price is outside this range, set it to the nearest boundary:

UPDATE Products
SET Price = CASE
    WHEN NewPrice < 0 THEN 0
    WHEN NewPrice > 1000 THEN 1000
    ELSE NewPrice
END
WHERE ProductID = 1;

In this example, the NewPrice represents the updated price you want to set, which could be a variable or a result from another operation. The CASE statement ensures that the updated price falls within the specified range.

These are some examples of range checks in SQL that help ensure data consistency and maintain data integrity.