How Do Foreign Keys Ensure Data Integrity in MySQL Relationships?

MySQL Foreign Keys: Maintaining Data Integrity in Table Relationships

Building robust and reliable databases requires carefully managing relationships between tables. MySQL's foreign key mechanism is vital for ensuring data integrity within these relationships.

Let's illustrate with an example involving two tables: accounts and customers. Each account belongs to a specific customer, identified by a unique customer_id. A foreign key constraint establishes this link.

CREATE TABLE accounts (
    account_id INT NOT NULL AUTO_INCREMENT,
    customer_id INT(4) NOT NULL,
    account_type ENUM('savings', 'credit') NOT NULL,
    balance FLOAT(9) NOT NULL,
    PRIMARY KEY (account_id),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
) ENGINE=INNODB;

This code creates the accounts table and includes a foreign key constraint on the customer_id column. This constraint references the customer_id in the customers table, enforcing that every customer_id in accounts must match an existing customer_id in the customers table.

The core benefit of foreign keys is their enforcement of referential integrity. Any attempt to insert or update data in the accounts table that violates this relationship (e.g., referencing a non-existent customer_id) will be rejected by the database, maintaining data consistency.

The above is the detailed content of How Do Foreign Keys Ensure Data Integrity in MySQL Relationships?. For more information, please follow other related articles on the PHP Chinese website!