How to Create Relationships Between MySQL Tables Using FOREIGN KEY Constraints?

Mastering MySQL Relationships with FOREIGN KEY Constraints

MySQL, a leading relational database management system (RDBMS), relies heavily on establishing relationships between tables for efficient data management. This article focuses on creating relationships, specifically linking accounts and customers tables, assigning each account to a unique customer.

The cornerstone of this process is the FOREIGN KEY constraint. Let's illustrate how to implement this in MySQL:

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;

The key line, FOREIGN KEY (customer_id) REFERENCES customers(customer_id), defines the relationship. It dictates that the customer_id in the accounts table must correspond to a valid customer_id in the customers table.

This constraint upholds referential integrity, ensuring data accuracy and consistency. It prevents the creation of "orphan" records (accounts without corresponding customers) and maintains data relationships.

Important Consideration: MySQL's MyISAM engine lacks FOREIGN KEY support. For MyISAM tables, alternative methods like triggers are necessary to mimic FOREIGN KEY functionality.

The above is the detailed content of How to Create Relationships Between MySQL Tables Using FOREIGN KEY Constraints?. For more information, please follow other related articles on the PHP Chinese website!