SQL Data Integrity: Enforcing Business Rules with CHECK Constraints

Databases are the backbone of many applications, and ensuring data accuracy and consistency is paramount. Often, simple data type restrictions aren't enough; we need to enforce specific business rules. This challenge focuses on implementing data validation directly within the database schema using SQL's CHECK constraint.

Problem Description

Your task is to create a database table for storing product information and then implement a CHECK constraint to enforce a specific business rule regarding the product's price.

What needs to be achieved: You will define a table named Products and add a CHECK constraint to ensure that the price column always holds a non-negative value.

Key requirements:

1. Create a table named Products.
2. The Products table must have at least the following columns:
   • product_id (integer, primary key)
   • product_name (text/varchar, cannot be null)
   • price (numeric/decimal, cannot be null)
3. Implement a CHECK constraint on the price column to ensure that its value is greater than or equal to zero (>= 0).
4. Demonstrate that the CHECK constraint prevents the insertion of invalid data.

Expected behavior:

• INSERT statements with a price value of 0 or greater should succeed.
• INSERT statements with a price value less than 0 should fail, raising an error indicating a constraint violation.
• UPDATE statements that attempt to change the price to a value less than 0 should also fail.

Important edge cases to consider:

• What happens if you try to insert NULL into the price column? (The NOT NULL constraint should handle this, but it's good to be aware of interactions.)
• Consider the data type used for price – ensure it can handle decimal values.

Examples

Example 1: Valid Data Insertion

SQL:
CREATE TABLE Products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

ALTER TABLE Products
ADD CONSTRAINT chk_price_non_negative
CHECK (price >= 0);

INSERT INTO Products (product_id, product_name, price) VALUES (1, 'Laptop', 1200.50);
INSERT INTO Products (product_id, product_name, price) VALUES (2, 'Mouse', 25.00);
INSERT INTO Products (product_id, product_name, price) VALUES (3, 'Keyboard', 0.00);

Output:
(Successful execution of all INSERT statements)

Explanation: The CHECK constraint chk_price_non_negative is successfully applied. The INSERT statements for 'Laptop', 'Mouse', and 'Keyboard' provide valid prices (1200.50, 25.00, and 0.00 respectively), which are all greater than or equal to zero, so they are accepted.

Example 2: Invalid Data Insertion

SQL:
-- Assuming the Products table and chk_price_non_negative constraint are already created as in Example 1.

INSERT INTO Products (product_id, product_name, price) VALUES (4, 'Defective Widget', -10.75);

Output:
ERROR:  insert or update on table "Products" violates check constraint "chk_price_non_negative"

Explanation: The INSERT statement attempts to add a product with a negative price (-10.75). The CHECK constraint chk_price_non_negative detects this violation and prevents the row from being inserted, returning an error.

Example 3: Invalid Data Update

SQL:
-- Assuming the Products table and chk_price_non_negative constraint are already created, and a product with ID 2 exists.
UPDATE Products
SET price = -5.00
WHERE product_id = 2;

Output:
ERROR:  update or insert on table "Products" violates check constraint "chk_price_non_negative"

Explanation: An attempt is made to update the price of the product with product_id 2 to a negative value (-5.00). The CHECK constraint prevents this update, as it would violate the business rule that prices must be non-negative.

Constraints

• The product_id should be a unique integer.
• The product_name should be a string.
• The price should be a numeric type capable of storing decimal values up to two places.
• The CHECK constraint must be named chk_price_non_negative.
• Solutions should focus on standard SQL syntax that is widely supported.

Notes

• This challenge tests your understanding of defining table structures and enforcing data integrity rules directly within the database.
• CHECK constraints are a powerful tool for ensuring data quality and enforcing business logic at the most fundamental level.
• Pay close attention to the syntax for creating tables and adding constraints.
• Consider how different database systems might present error messages, but the core concept of constraint violation remains the same.