How to create relationships in MySQL

Question

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 )

)

and

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 )

)

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

Answer 1

If your tables are InnoDB then you can create it this way:

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;

For detailed information, refer to this:

https://dev.mysql.com/doc/refman/8.0/en/innodb-foreign-key-constraints.html