Home >Database >Mysql Tutorial >How Do I Establish Relationships Between MySQL Tables Using Foreign Keys?
Mastering MySQL Table Relationships with Foreign Keys
Relational database management hinges on effectively linking tables. MySQL uses foreign keys to achieve this, ensuring data integrity and efficient organization. This guide illustrates how to establish these crucial relationships.
Connecting Tables: A Practical Example
Let's consider creating a relationship between accounts
and customers
tables. The process involves these steps:
accounts
table, we add a foreign key referencing the customers
table.<code class="language-sql">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;</code>
<code class="language-sql">ALTER TABLE accounts ENGINE=INNODB; ALTER TABLE customers ENGINE=INNODB;</code>
Understanding the Foreign Key Constraint
The FOREIGN KEY
constraint enforces referential integrity. It mandates that every customer_id
in the accounts
table must exist as a customer_id
in the customers
table. This prevents orphaned records and maintains data accuracy.
Advantages of Table Relationships
Establishing table relationships offers significant advantages:
The above is the detailed content of How Do I Establish Relationships Between MySQL Tables Using Foreign Keys?. For more information, please follow other related articles on the PHP Chinese website!