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.
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 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:
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.
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;
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.