DBM lecture

Introduction to Foreign Keys

  • Definition: A foreign key is a primary key from a different table that is included in the current table to create a link between the two tables.

  • Purpose: To ensure that values in the foreign key column correlate with values in the referenced primary key column of another table.

Importance of Foreign Key Constraints

  • Referential Integrity: A foreign key constraint ensures that every foreign key value must exist as a primary key value in the referenced table.

  • Lifetime Preservation: Foreign key constraints are maintained throughout the life cycle of the table to uphold data integrity.

  • Enforcement of Non-Null Values: When defining a foreign key, you can specify that this column must not accept null values directly by using the NOT NULL constraint.

Defining Foreign Keys in SQL

  • Syntax: To create a foreign key, you specify the keyword FOREIGN KEY followed by the column(s) in parentheses, and then the field it references in another table:

    FOREIGN KEY (column_name)
REFERENCES referenced_table (referenced_column)

  • Example Usage: For a department table:

    • If department_name is a foreign key in the instructor table referencing the primary key department_name of the department table.

Constraints Associated with Foreign Keys

  • Constraint for Non-Existence: If the referenced table or key doesn’t exist, the foreign key constraint will not allow the creation of the new table with that foreign key.

  • Uniqueness Requirement: The referenced column in the other table must be a primary key or must have a unique constraint to ensure referential integrity.

Nullable and Non-Nullable Columns in Foreign Key

  • Not Null Constraint: If a column is designated NOT NULL, each record must have a value for that column when inserting data into the table.

  • Implications: Columns marked NOT NULL do not allow for null entries, thus guiding the design decision during table creation.

Inserting Data into a Table

  • Syntax: There are two primary syntaxes for inserting data:

    1. Full Column Specification:
      sql INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

    2. Order-Based Insertion:
      sql INSERT INTO table_name VALUES (value1, value2, ...);

  • Constraints on Insertions: If a column is marked as NOT NULL, a value must be provided during the insertion process.

Handling Duplicate Rows in Insertions

  • Primary Key Uniqueness: Inserting two identical rows into a table with a primary key will result in an error due to uniqueness constraints.

  • Example Attempt:

    • Attempts to insert a record with a primary key that already exists will fail.

Primary Key Definition and Candidate Keys

  • Candidate Keys: A table may have multiple candidate keys, but only one is chosen as the primary key.

  • Multi-Column Primary Keys: A primary key can consist of multiple columns, known as a composite primary key.

Handling Tables Without Primary Keys

  • Importance of Keys: Creating tables without identifying keys is discouraged as it makes data retrieval ineffective, although it is technically possible in MySQL.

  • Log Tables: For logging purposes, a table without a primary key may sometimes be acceptable without identifying records.

Deletion of Records from Tables

  • Syntax for Deletion: To delete records from a table, you use:
    sql DELETE FROM table_name WHERE condition;

  • Cascading Effects: If foreign keys reference another table, you cannot delete records that are referred to until those references are addressed.

  • Safe Deletion Practices: Always specify conditions when deleting to avoid unintentionally removing all records.

Updating Records in Tables

  • Update Syntax: To change record values, the syntax is:
    sql UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

  • Setting Multiple Columns: You can update several columns in one statement.

Altering Table Structure

  • Adding Columns: You can add new columns using:
    sql ALTER TABLE table_name ADD column_name datatype;

  • Setting Default Values on New Columns: If adding a new column, specifying a default value aids in maintaining historical records without violating constraints.

Select Statement Overview

  • Basic Structure: The basic structure of a select statement includes:
    sql SELECT column1, column2 FROM table_name WHERE condition;

  • Set vs Bag Semantics: SQL uses bag semantics which allows duplicate rows unless specified otherwise with DISTINCT to mimic set semantics.

Using Select to Perform Calculations

  • Mathematical Operations in Select: You can perform calculations directly in the SELECT statement:
    sql SELECT column_name + constant AS new_column_name FROM table_name;

  • Functions and Expressions: SQL supports built-in functions for operations such as rounding and generating new fields dynamically in the result set.

  • Returning Constants: You can include constants in the result set for each record retrieved.

Summary of Referential Actions on Foreign Keys

  • Restrict: Prevent operations that would violate referential integrity.

  • Cascade: Automatically delete or update referencing records when the referenced record is deleted or updated.

  • Set Null: Set foreign keys in referencing records to null upon deletion or update of the referenced record.

  • Implications of Conflicts: Attempting to create conflicting foreign key constraints will result in an error, ensuring the integrity of the database structure.