5.2 Defining constraints and relationships

Constraints and relationships are the backbone of database design, ensuring data integrity and consistency. They define rules for data entry, establish connections between tables, and maintain the overall structure of your database.

Primary keys, unique constraints, and foreign keys work together to create a robust database schema. These tools help prevent data inconsistencies, enforce referential integrity, and enable efficient data retrieval and manipulation across related tables.

Key Constraints

Ensuring Data Integrity with Primary Keys and Unique Constraints

Top images from around the web for Ensuring Data Integrity with Primary Keys and Unique Constraints

• 

  Chapter 9 Integrity Rules and Constraints – Database Design – 2nd Edition View original

  Is this image relevant?

• 

  Chapter 9 Integrity Rules and Constraints – Database Design – 2nd Edition View original

  Is this image relevant?

• 

  Database Design and flattening composite primary keys - Stack Overflow View original

  Is this image relevant?

• 

  Chapter 9 Integrity Rules and Constraints – Database Design – 2nd Edition View original

  Is this image relevant?

• 

  Chapter 9 Integrity Rules and Constraints – Database Design – 2nd Edition View original

  Is this image relevant?

1 of 3

Top images from around the web for Ensuring Data Integrity with Primary Keys and Unique Constraints

• 

  Chapter 9 Integrity Rules and Constraints – Database Design – 2nd Edition View original

  Is this image relevant?

• 

  Chapter 9 Integrity Rules and Constraints – Database Design – 2nd Edition View original

  Is this image relevant?

• 

  Database Design and flattening composite primary keys - Stack Overflow View original

  Is this image relevant?

• 

  Chapter 9 Integrity Rules and Constraints – Database Design – 2nd Edition View original

  Is this image relevant?

• 

  Chapter 9 Integrity Rules and Constraints – Database Design – 2nd Edition View original

  Is this image relevant?

1 of 3

• PRIMARY KEY constraint uniquely identifies each record in a table
  • Consists of one or more columns that together form a unique combination for each row
  • Prevents duplicate rows from being inserted into the table (students table with

    student_id

    as the primary key)
• UNIQUE constraint ensures that all values in a column or a group of columns are distinct
  • Similar to PRIMARY KEY, but a table can have multiple UNIQUE constraints
  • Allows NULL values, unlike PRIMARY KEY (email column in users table)
• NOT NULL constraint enforces that a column must always contain a non-NULL value
  • Prevents incomplete or missing data from being inserted into the column
  • Commonly used for columns that are essential for the meaning and integrity of the data (name, date_of_birth)

Validating Data with CHECK Constraints

• CHECK constraint defines a condition that must be satisfied for each row in a table
  • Ensures that the values in a column meet specific criteria or fall within a certain range
  • Helps maintain data consistency and prevents invalid data from being inserted (age >= 18, salary > 0)
  • Can involve multiple columns and use complex expressions or functions (start_date < end_date, discount BETWEEN 0 AND 100)
  • Provides an additional layer of data validation beyond data types and other constraints

Foreign Key Constraints

Establishing Relationships with Foreign Keys

• FOREIGN KEY constraint establishes a link between two tables based on a column or a set of columns
  • Refers to the PRIMARY KEY or a UNIQUE constraint in another table
  • Ensures referential integrity by enforcing that the values in the foreign key column(s) must exist in the referenced table (order_details table with

    order_id

    as a foreign key referencing the orders table)
• REFERENCES clause specifies the table and column(s) that the foreign key references
  • Defines the parent table and the corresponding PRIMARY KEY or UNIQUE constraint
  • Establishes the relationship between the child table (containing the foreign key) and the parent table (departments table referenced by

    department_id

    in the employees table)

Maintaining Referential Integrity with ON DELETE and ON UPDATE

• ON DELETE clause specifies the action to be taken when a referenced row in the parent table is deleted
  • CASCADE: Automatically deletes the corresponding rows in the child table
  • SET NULL: Sets the foreign key column(s) in the child table to NULL
  • RESTRICT: Prevents the deletion of the referenced row in the parent table if there are associated rows in the child table (default behavior)
• ON UPDATE clause defines the action to be taken when a referenced column in the parent table is updated
  • Similar options as ON DELETE (CASCADE, SET NULL, RESTRICT)
  • Ensures data consistency and maintains referential integrity when changes occur in the parent table
• Referential integrity maintains the consistency and accuracy of data across related tables
  • Prevents orphaned records in the child table that reference non-existing rows in the parent table
  • Ensures that the relationships between tables remain valid and consistent (deleting a department cascades the deletion to associated employees)