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

User · Answer

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

User · Answer

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     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     You have to ask yourself is this a 1 to 1 relationship or a 1 out of many relationship  That is  does every account have a customer and every customer have an account  Or will there be customers without accounts  Your question implies the latter   If you want to have a strict 1 to 1 relationship  just merge the two tables   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      account type ENUM   savings    credit    NOT NULL      balance FLOAT  9   NOT NULL      In the other case  the correct way to create a relationship between two tables is to create a relationship table   CREATE TABLE customersaccounts      customer id INT NOT NULL      account id INT NOT NULL      PRIMARY KEY  customer id  account id      FOREIGN KEY customer id references customers  customer id  on delete cascade      FOREIGN KEY account id  references accounts   account id  on delete cascade     Then if you have a customer id and want the account info  you join on customersaccounts and accounts   SELECT a       FROM customersaccounts ca         INNER JOIN accounts a ca account id a account id             AND ca customer id mycustomerid    Because of indexing this will be blindingly quick   You could also create a VIEW which gives you the effect of the combined customersaccounts table while keeping them separate  CREATE VIEW customeraccounts AS      SELECT a    c   FROM customersaccounts ca         INNER JOIN accounts a ON ca account id a account id         INNER JOIN customers c ON ca customer id c customer id

User · Answer

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

User · Answer

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

User · Answer

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     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     You have to ask yourself is this a 1 to 1 relationship or a 1 out of many relationship  That is  does every account have a customer and every customer have an account  Or will there be customers without accounts  Your question implies the latter   If you want to have a strict 1 to 1 relationship  just merge the two tables   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      account type ENUM   savings    credit    NOT NULL      balance FLOAT  9   NOT NULL      In the other case  the correct way to create a relationship between two tables is to create a relationship table   CREATE TABLE customersaccounts      customer id INT NOT NULL      account id INT NOT NULL      PRIMARY KEY  customer id  account id      FOREIGN KEY customer id references customers  customer id  on delete cascade      FOREIGN KEY account id  references accounts   account id  on delete cascade     Then if you have a customer id and want the account info  you join on customersaccounts and accounts   SELECT a       FROM customersaccounts ca         INNER JOIN accounts a ca account id a account id             AND ca customer id mycustomerid    Because of indexing this will be blindingly quick   You could also create a VIEW which gives you the effect of the combined customersaccounts table while keeping them separate  CREATE VIEW customeraccounts AS      SELECT a    c   FROM customersaccounts ca         INNER JOIN accounts a ON ca account id a account id         INNER JOIN customers c ON ca customer id c customer id

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

One of the rules you have to know is that the  table column you want to reference to has to be with the same data type as  The referencing table   2 if you decide to use mysql you have to use InnoDB Engine because according to your question that   s the engine which supports what you want to achieve in mysql    Bellow is the code try it though the first people to answer this question  they  100  provided great answers and please consider them all    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   ENGINE InnoDB   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   account id     FOREIGN KEY  customer id  REFERENCES customers customer id    ENGINE InnoDB

User · Answer

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

User · Answer

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

User · Answer

create table departement      dep id      int primary key auto increment      dep name    varchar 100  not null      dep descriptin      text      dep photo       varchar 100  not null      dep video       varchar 300  not null     create table newsfeeds      news id         int primary key auto increment      news title      varchar 200  not null      news description    text      news photo          varchar 300        news date           varchar 30  not null      news video          varchar 300       news comment        varchar 200       news departement    int foreign key dep id  references departement dep id

User · Answer

Here are a couple of resources that will help get started  http   www anchor com au hosting support CreatingAQuickMySQLRelationalDatabase and http   code tutsplus com articles sql-for-beginners-part-3-database-relationships--net-8561  Also as others said  use a GUI - try downloading and installing Xampp  or Wamp  which run server-software  Apache and mySQL  on your computer   Then when you navigate to   localhost in a browser  select PHPMyAdmin to start working with a mySQL database visually  As mentioned above  used innoDB to allow you to make relationships as you requested  Makes it heaps easier to see what you re doing with the database tables  Just remember to STOP Apache and mySQL services when finished - these can open up ports which can expose you to hacking malicious threats

User · Answer

Here are a couple of resources that will help get started  http   www anchor com au hosting support CreatingAQuickMySQLRelationalDatabase and http   code tutsplus com articles sql-for-beginners-part-3-database-relationships--net-8561  Also as others said  use a GUI - try downloading and installing Xampp  or Wamp  which run server-software  Apache and mySQL  on your computer   Then when you navigate to   localhost in a browser  select PHPMyAdmin to start working with a mySQL database visually  As mentioned above  used innoDB to allow you to make relationships as you requested  Makes it heaps easier to see what you re doing with the database tables  Just remember to STOP Apache and mySQL services when finished - these can open up ports which can expose you to hacking malicious threats

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

create table departement      dep id      int primary key auto increment      dep name    varchar 100  not null      dep descriptin      text      dep photo       varchar 100  not null      dep video       varchar 300  not null     create table newsfeeds      news id         int primary key auto increment      news title      varchar 200  not null      news description    text      news photo          varchar 300        news date           varchar 30  not null      news video          varchar 300       news comment        varchar 200       news departement    int foreign key dep id  references departement dep id

User · Answer

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

User · Answer

One of the rules you have to know is that the  table column you want to reference to has to be with the same data type as  The referencing table   2 if you decide to use mysql you have to use InnoDB Engine because according to your question that   s the engine which supports what you want to achieve in mysql    Bellow is the code try it though the first people to answer this question  they  100  provided great answers and please consider them all    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   ENGINE InnoDB   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   account id     FOREIGN KEY  customer id  REFERENCES customers customer id    ENGINE InnoDB

User · Answer

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

[mysql] How to create relationships in MySQL

Examples related to mysql

Examples related to sql

Examples related to foreign-keys

Examples related to relational-database