Overview
This lecture demonstrates how to add check constraints to the OrderTransactions table in Oracle to ensure data authenticity and integrity.
Introduction to Check Constraints
- Check constraints verify that data entered into a table meets specific rules.
- They help prevent invalid or nonsensical data from being imported or entered.
- Constraints can be enabled or disabled as needed.
Viewing Existing Constraints
- The OrderTransactions table may already have system-generated constraints (e.g., NOT NULL checks and a primary key).
- These built-in constraints enforce required fields and unique identification.
Steps to Add Check Constraints
- Access the SQL Workshop and select the OrderTransactions table.
- Go to the Constraints tab and click on "Create" to add new constraints.
- Name each constraint following a consistent pattern (e.g., ordertransactions_con1, con2, etc.).
Examples of Check Constraints Added
- Share Price: Must be greater than 0 (
share_price > 0).
- Quantity Shares: Must be greater than 0 (
quantity_shares > 0).
- Price Type: Must be either 'L' (limit) or 'M' (market). Use single quotes for character values.
- Order Type: Must be either 'B' (buy) or 'S' (sell), specified with uppercase letters and single quotes.
Common Mistakes and Tips
- Use single quotes for string values in SQL, not double quotes.
- Constraint names must be unique within each table.
- Case sensitivity matters for character constraints (use consistent letter casing).
Importance of Constraints
- Enforce data integrity, catch input errors early, and keep the database consistent.
- Prevents importing or entering invalid values (like negative share prices or unrecognized order types).
Key Terms & Definitions
- Constraint — A rule that restricts permissible data values in a database table.
- Check Constraint — A type of constraint that limits the values in a column to a specific condition.
- Primary Key — A unique identifier for each record in a table.
Action Items / Next Steps
- Practice adding check constraints to your own tables.
- Prepare to learn about adding foreign key constraints to link tables in upcoming videos.