How Do I Establish Relationships Between Tables in MySQL?

Create inter -table relationship in MySQL

Although most people use Access for database management, you want to use the SQL command and MySQL. You have created a table, but how do you build the relationship between them?

Let's consider the following scenes: you have two tables, accounts and Customers. Each account should be assigned a unique Customer_id to indicate its owner. The following is how to achieve this relationship in MySQL:

<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>

In order to establish this relationship, you need to specify the ACCOUNTS table to use the InnoDB engine because Myisam does not support external keys. This Foreign Key constraint ensures that each account is associated with the valid Customer_id in the Customers table. This constraint is forced to execute data integrity and prevent isolation.

The above is the detailed content of How Do I Establish Relationships Between Tables in MySQL?. For more information, please follow other related articles on the PHP Chinese website!