Lecture Notes: Default Constraint in SQL

Introduction to Default Constraint

• Default Constraint: Automatically assigns a specified value to a column if no value is provided during insertion.
• Example Use Case: Fast-food restaurant products table with free items (e.g., napkins, straws).

Demonstration of Default Constraint

Initial Setup without Default Constraint

• Products Table Example:
  • Items: Hamburgers, Fries, Soda, Ice Cream.
  • Free Items: Napkins, Straws, Forks, Spoons (manual entry of $0.00).
• SQL Command Example:

  INSERT INTO products VALUES (104, 'Straw', 0.00);
  INSERT INTO products VALUES (105, 'Napkin', 0.00);
  

Using Default Constraint

• Objective: Automate entry of default $0.00 for free items.
• Creating Table with Default Constraint:

  CREATE TABLE products (
      product_id INT,
      product_name VARCHAR(25),
      price DECIMAL(4,2) DEFAULT 0.00
  );
  

• Alter Existing Table to Add Default Constraint:

  ALTER TABLE products ALTER COLUMN price SET DEFAULT 0.00;
  

Inserting Rows with Default Constraint

• Simplifies insertion by not requiring explicit price entry.
• Example:

  INSERT INTO products (product_id, product_name) VALUES (104, 'Straw');
  

  • Automatically sets price to $0.00.

Example: Transactions Table with Timestamp

Explanation

• Transactions Table: Demonstrates default timestamp for transaction records.
• Columns:
  • TransactionId - integer.
  • Amount - decimal(5,2).
  • TransactionDate - datetime (defaulting to current time).

Implementation

• Create Table with Default Timestamp:

  CREATE TABLE transactions (
      transaction_id INT,
      amount DECIMAL(5,2),
      transaction_date DATETIME DEFAULT NOW()
  );
  

• Insert Transactions:

  INSERT INTO transactions (transaction_id, amount) VALUES (1, 4.99);
  

  • Automatically adds current timestamp.

Conclusion

• Benefits of Default Constraint:
  • Efficient data entry by automating default values.
  • Facilitates insertion without needing to specify common default values.
• Applications: Useful for consistent default values like zero pricing or timestamps.

Wrap-up

• Demonstrated how to set and use default constraints in MySQL.
• Practical in scenarios requiring repetitive default values.
• Helps streamline database operations by reducing manual data entry.