如何在 MySQL 中创建关系

How to create relationships in MySQL(如何在 MySQL 中创建关系)

问题描述

在课堂上，我们都在学习"数据库，每个人都在使用 Access.对此感到厌烦，我正在尝试做其他课程正在做的事情，但是使用 MySQL 的原始 SQL 命令而不是使用 Access.

In class, we are all 'studying' databases, and everyone is using Access. Bored with this, I am trying to do what the rest of the class is doing, but with raw SQL commands with MySQL instead of using Access.

我已经成功创建了数据库和表，但现在如何在两个表之间建立关系?

I have managed to create databases and tables, but now how do I make a relationship between two tables?

如果我有两个这样的表:

If I have my two tables like this:

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

和

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

如何在两个表之间创建关系"?我希望每个帐户都被分配"一个 customer_id(以表明它的所有者).

How do I create a 'relationship' between the two tables? I want each account to be 'assigned' one customer_id (to indicate who owns it).

推荐答案

如果表是 innodb 你可以这样创建:

If the tables are innodb you can create it like this:

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;

你必须指定表是 innodb 因为 myisam 引擎不支持外键.查看此处了解更多信息.

You have to specify that the tables are innodb because myisam engine doesn't support foreign key. Look here for more info.

这篇关于如何在 MySQL 中创建关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！