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

Now, 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 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;

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

Answer 2

Adding onto the comment by ehogue, you should make the size of the keys on both tables match. Rather than

customer_id INT( 4 ) NOT NULL ,

make it

customer_id INT( 10 ) NOT NULL ,

and make sure your int column in the customers table is int(10) also.

Answer 3

as ehogue said, put this in your CREATE TABLE

FOREIGN KEY (customer_id) REFERENCES customers(customer_id)

alternatively, if you already have the table created, use an ALTER TABLE command:

ALTER TABLE `accounts`
  ADD CONSTRAINT `FK_myKey` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`) ON DELETE CASCADE ON UPDATE CASCADE;

One good way to start learning these commands is using the MySQL GUI Tools, which give you a more "visual" interface for working with your database. The real benefit to that (over Access's method), is that after designing your table via the GUI, it shows you the SQL it's going to run, and hence you can learn from that.

Answer 4

If you want to go really deep into visual database design try MySQL Workbench

Answer 5

Certain MySQL engines support foreign keys. For example, InnoDB can establish constraints based on foreign keys. If you try to delete an entry in one table that has dependents in another, the delete will fail.

If you are using a table type in MySQL, such as MyISAM, that doesn't support foreign keys, you don't link the tables anywhere except your diagrams and queries.

For example, in a query you link two tables in a select statement with a join:

SELECT a, b from table1 LEFT JOIN table2 USING (common_field);