Enforcing Data Integrity with CHECK Constraints

Data validation is crucial for maintaining the accuracy and reliability of information stored in a database. CHECK constraints allow you to define rules that data must adhere to before being inserted or updated. This challenge asks you to design and implement a database schema with CHECK constraints to enforce specific data validation rules.

Problem Description

You are tasked with designing a database table to store information about products sold in an online store. The table should include columns for product ID, product name, price, and quantity in stock. You must implement CHECK constraints to ensure that the data entered into these columns adheres to the following rules:

• Price: Must be a positive number (greater than 0).
• Quantity in Stock: Must be a non-negative integer (greater than or equal to 0).
• Product Name: Must not be empty (cannot be NULL or an empty string).
• Product ID: Must be a positive integer.

The goal is to create a SQL schema that automatically validates data before it's stored, preventing invalid data from entering the database and ensuring data integrity. You are not required to create the table and populate it with data; only the schema definition with the appropriate CHECK constraints is needed.

Examples

Example 1:

Input: Table definition without CHECK constraints.
Output: Table definition with CHECK constraints as described in the problem description.
Explanation: The output should be a SQL `CREATE TABLE` statement that includes the necessary CHECK constraints on the `price`, `quantity_in_stock`, `product_name`, and `product_id` columns.

Example 2:

Input: A table definition with incorrect CHECK constraint syntax.
Output: A corrected table definition with valid CHECK constraint syntax.
Explanation: The output should demonstrate correct SQL syntax for defining CHECK constraints, ensuring they are properly formatted and functional.

Example 3: (Edge Case)

Input: A scenario where a column might contain NULL values.
Output: A table definition that explicitly handles NULL values within the CHECK constraints, ensuring that NULL values are not allowed for the `product_name` column.
Explanation: The CHECK constraint for `product_name` should explicitly disallow NULL values.

Constraints

• The database system is assumed to be a standard SQL implementation (e.g., PostgreSQL, MySQL, SQL Server). While syntax might vary slightly, the core concepts of CHECK constraints should be applicable.
• The table name should be products.
• The column names should be product_id, product_name, price, and quantity_in_stock.
• product_id should be an integer.
• product_name should be a string (VARCHAR or TEXT).
• price should be a numeric type (DECIMAL or FLOAT).
• quantity_in_stock should be an integer.
• The solution should be concise and readable.

Notes

• Consider how to handle NULL values within your CHECK constraints. Explicitly disallowing NULL values where appropriate is often a good practice.
• Think about the order in which you define the CHECK constraints. While the order generally doesn't affect functionality, it can impact readability.
• The focus is on the CHECK constraint implementation, not on the overall database design. You only need to define the table schema with the constraints.
• Pseudocode is not required. Provide a valid SQL CREATE TABLE statement.