Presentation Summary: Constraints in SQL

Overview

This presentation focuses on solving example queries related to constraints in SQL, building on the understanding established in previous sessions.

Key Points

Types of Constraints in SQL

1. Not Null: Ensures that a column cannot have NULL values.
2. Unique: Guarantees all values in a column are distinct.
3. Check: Validates the value being entered into a column meets a specified condition.
4. Default: Specifies a default value for a column if no value is provided.
5. Primary Key: Combines the properties of Unique and Not Null, ensuring each row in the table can be uniquely identified.
6. Foreign Key: Enforces referential integrity between tables.

Focus of Current Presentation

• First Four Constraints: Not Null, Unique, Check, and Default.
• Examples with syntax demonstration.

Key Syntax for Creating Tables with Constraints

• General Syntax for Table Creation:

  CREATE TABLE table_name (
      column1 datatype constraint_name,
      column2 datatype constraint_name,
      ...
      column_n datatype constraint_name
  );
  

• Example:
  • Enforcing a Unique Constraint:

    CREATE TABLE employee (
        ID INT UNIQUE,
        Name VARCHAR(50) NOT NULL,
        DepartmentName VARCHAR(40),
        Salary INT
    );
    

  • Enforcing a Not Null Constraint:

    CREATE TABLE student (
        Name VARCHAR(10) NOT NULL
    );
    

Adding Constraints to Existing Tables

• Utilize the ALTER command to add or drop constraints in existing tables.

Examples with Explanations

1. Unique Constraint Example:

   • Enforces unique values in a column.
   • Null values are accepted if only UNIQUE without NOT NULL.

2. Not Null Constraint Example:

   • Prevents any null value entries in the specified column.

3. Default Constraint Example:

   • Automatically assigns a default value (e.g., DEFAULT 18) if no value is specified for a column during an insertion.

4. Check Constraint Example:

   • Ensures values meet specific criteria, e.g., age >= 18.
   • Example Check using IN clause:

     CREATE TABLE student (
         Gender CHAR CHECK (Gender IN ('M', 'F'))
     );
     

Primary Key and Foreign Key

• Primary Key Preview:

  • Uniquely identifies each record in a table.
  • No duplicates and no nulls allowed.
  • Provides the benefits of both UNIQUE and NOT NULL.

• Next Presentation Focus: More detailed exploration of Primary Key and Foreign Key with examples.

Conclusion

A thorough understanding of these constraints allows for robust database design, ensuring data integrity, and establishing clear relationships between database tables. Future sessions will delve deeper into more complex constraints like Primary Key and Foreign Key.