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 TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

– Create User table ->

CREATE TABLE user (
	id bigint(20) NOT NULL, 
	email varchar(40) NOT NULL,
	username varchar(15) NOT NULL,
	password varchar(100) NOT NULL,
	PRIMARY KEY (id)
);

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

– Create Role table ->

CREATE TABLE role (
	id bigint(20) NOT NULL,
	name varchar(60) NOT NULL, 
	PRIMARY KEY (id)
);

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

– Create User_Roles table ->

CREATE TABLE user_roles (
	user_id bigint(20) NOT NULL,
	role_id bigint(20) NOT NULL,
	FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE RESTRICT ON UPDATE CASCADE,
	FOREIGN KEY (role_id) REFERENCES role (id) ON DELETE RESTRICT ON UPDATE CASCADE,
	PRIMARY KEY (user_id, role_id)
);

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
    INSERT INTO table_name (column1, column2, column3, ...)
    VALUES (value1, value2, value3, ...);
    
  • 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 table_name
    VALUES (value1, value2, value3, ...);
    

– Insert into user table:

INSERT INTO user VALUES (1, 'jack@grokonez.com', 'Jack', 'jack123');
INSERT INTO user VALUES (2, 'adam@grokonez.com', 'Adam', 'adam123');

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

– Insert into role table:

INSERT INTO role VALUES (3, 'ADMIN');
INSERT INTO role VALUES (4, 'USER');

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

– Insert into user_roles table:

INSERT INTO user_roles VALUES (1, 3);
INSERT INTO user_roles VALUES (1, 4);
INSERT INTO user_roles VALUES (2, 4);

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

Join Query

SELECT user.id, user.email, user.username, role.id AS role_id, role.name AS role_name
FROM user 
JOIN user_roles on (user.id=user_roles.user_id)
JOIN role on (role.id=user_roles.role_id);

-> Result:

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



By grokonez | September 21, 2018.


Related Posts


    1 thought on “SQL Tutorial – MySQL Many-to-Many Relationship”

    Got Something To Say:

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

    *