SQL Tutorial – MySQL Many-to-Many Relationship

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

– Create Role table ->

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

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

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@ozenero.com', 'Jack', 'jack123');
INSERT INTO user VALUES (2, 'adam@ozenero.com', 'Adam', 'adam123');

– Insert into role table:

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

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

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: