How Do I Create and Manage Relationships Between Tables in MySQL?

MySQL database table relationship Construction

High -efficiency MySQL database design depends on the reasonable construction of inter -table relationships. The external key constraint is one of the common types of relationships, which allows you to connect the data in different tables.

The following examples demonstrate how to establish an external key relationship:

<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)
);</code>

In the Table, use

<code class="language-sql">CREATE TABLE customers(
    customer_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    address VARCHAR(20) NOT NULL,
    city VARCHAR(20) NOT NULL,
    state VARCHAR(20) NOT NULL,
    PRIMARY KEY (customer_id)
);</code>

clause to connect these two tables:

accounts FOREIGN KEY This constraint ensures that each

in the table is matched with the

<code class="language-sql">CREATE TABLE accounts(
    ...
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
) ENGINE=INNODB;</code>

in the

table. By quoting the accounts column, one -to -multi -relationship is established: each customer can have multiple accounts, and each account belongs to only one customer. customer_id customers MySQL also supports other types of table relationships. For example, you can use the primary key and the outer key constraint to establish multiple relationships. For more information about the types and constraints, please refer to MySQL official documentation. customer_id

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