CompTIA DataSys+ (DS0-001)Database Management and MaintenanceEasy
A data engineer is designing a new database for an e-commerce platform. They need to ensure that the `product_price` column in the `products` table always contains a positive value and that the `order_quantity` column in the `order_items` table is always greater than zero. Which type of data integrity constraint should be implemented for these requirements?
- APrimary Key constraint.
- BCHECK constraint.
- CNOT NULL constraint.
- DForeign Key constraint.
Show answer & explanationAnswer & explanation
Correct answer: B. CHECK constraint.
A CHECK constraint is used to enforce domain integrity by ensuring that values in a column or set of columns satisfy a specified boolean expression. This is ideal for ensuring `product_price` is positive and `order_quantity` is greater than zero.
Why the other options are wrong
- A. A Primary Key constraint ensures uniqueness and non-nullability for a column or set of columns, identifying each row uniquely, but does not enforce value ranges.
- C. A NOT NULL constraint simply ensures that a column cannot contain NULL values, but it does not validate the range or specific values beyond that.
- D. A Foreign Key constraint enforces referential integrity by linking a column in one table to a primary key in another table, ensuring relationships are valid.
CHECK Constraint
A database constraint that enforces domain integrity by ensuring that all values in a column meet a specified condition or range.
- Evaluates a boolean expression for each new or updated row.
- Prevents invalid data from being inserted or updated into a column.
- Can be applied to a single column or multiple columns within a table.
Memory trick: Keys for identity/relations, Check for values, Not Null for presence.