SQL Tutorial – MySQL Many-to-Many Relationship

sql-tutorial-mysql-script-many-to-many-mapping-feature-image

In the tutorial, we guides how to use SQL queries to create Many-to-Many association tables with MySQL database.

Goal

Many-to-Many relationship:

Create 3 tables:

user table -> attributes: (id, email, username, password)
role table -> attributes: (id, description, name)
user_roles table -> attributes: (user_id, role_id)

Create Tables

SQL creating table syntax:

– Create User table ->

sql-many-to-many-tables-create-user-table

– Create Role table ->

sql-many-to-many-tables-create-role-table

– Create User_Roles table ->

sql-many-to-many-tables-create-user-role-table

Insert Data Into Tables

Insert Into syntax:

  • Specifies both the column names and the values to be inserted

  • If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query.

– Insert into user table:

sql-many-to-many-tables-insert-data-to-user-table

– Insert into role table:

sql-many-to-many-tables-insert-data-to-role-table

– Insert into user_roles table:

sql-many-to-many-tables-insert-data-to-user_roles-table

Join Query

-> Result:

sql-many-to-many-tables-join-query

By grokonez | September 21, 2018.



Related Posts


    Got Something To Say:

    Your email address will not be published. Required fields are marked *

    *