Key Points from Presentation on Primary and Foreign Key Constraints

Introduction

• Focus on primary and foreign key constraints, two of the six SQL constraints.
• Brief overview of SQL constraints: NOT NULL, UNIQUE, CHECK, DEFAULT, PRIMARY KEY, and FOREIGN KEY.

Primary Key

• Purpose: Ensures accuracy, integrity, and reliability in the database.
• Syntax: CREATE TABLE using constraints.
  • General syntax to enforce a primary key:

    CREATE TABLE Employee (
        ID INT PRIMARY KEY,
        Name VARCHAR(255),
        DepartmentName VARCHAR(40),
        Salary INT
    );
    

• Characteristics:
  • Uniqueness and no duplicate values.
  • No null values are allowed.
  • Column-level constraint example.

Comparison: Primary Key vs. Unique and Not Null

• Unique: No duplicates allowed. Null values are accepted.
• Not Null: Null values not allowed. Duplicates are accepted.
• Primary Key: More strict, denies duplicates and null values.

Foreign Key

• Purpose: Establishes a relationship between two tables; ensures referential integrity.
• Example of a foreign key constraint:
  • Table Employee:

    CREATE TABLE Employee (
        ID INT PRIMARY KEY,
        Name VARCHAR(255) NOT NULL,
        DepartmentCode INT,
        Salary INT,
        CONSTRAINT FK_Department FOREIGN KEY (DepartmentCode) REFERENCES Department(DepartmentCode)
    );
    

• Characteristics:
  • A column in one table referencing another table’s primary key.
  • Refers back to another table (database table integrity).

Referential Integrity

• Ensures a valid relationship between two relational database tables.
• A foreign key should always reference a corresponding value in another table.
• Prevents orphan records, ensuring data remains consistent.

Summary

• Detailed understanding of primary and foreign key constraints.
• Differentiation between primary key, unique, and not null constraints.
• Explanation of referential integrity in ensuring database relationships remain valid.

This presentation enhances the understanding of key constraints in SQL used to maintain database integrity and relationships.