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).
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With