如何在 MySQL 中创建关系

2021-11-20 00:00:00 sql foreign-keys mysql relational-database

在课堂上，我们都在学习"数据库，每个人都在使用 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.

相关文章