INSERT/DELETE examples in a many-to-many relationship

3
votes

I have three tables, Users, Roles, and a junction UserRoles.

Users       UserRoles         Roles
=======     =============     =========
UserId      UserId            RoleId
Username    RoleId            Role
Firstname   Date

My tables are joined like this:

Users.UserId -> UserRoles.UserId
Roles.RoleId -> UserRoles.RoleId

Basically, what I am looking for is an simple example that shows:

  1. How to INSERT in UserRoles a UserId and a RoleId for a specific Username with a specific Role.
  2. Delete a role for a specific Username

I managed to make a simple select on Roles for a specific Username like so:

SELECT 
    dbo.Roles.Role 
FROM dbo.Roles 
INNER JOIN dbo.UserRoles 
    ON dbo.Roles.RoleId = dbo.UserRoles.RoleId 
INNER JOIN dbo.Users 
    ON dbo.UserRoles.UserId = dbo.Users.UserId 
WHERE (Username = @Username)
2
sql-servermany-to-many
Are you sure your select query is working, I doubt? Check "WHERE" clause in Select query. - Paresh J
yes it is working "@Username" is a parameter from my .NET code. Can be used like this also: WHERE (Username = 'John Doe'). Thanks - Gerald Hughes
Not possible to insert into UserRoles based on current table design. Since when I insert User in Users table there's no relation between Users and Roles. I can insert UserID into UserRoles but not RoleID - Paresh J

2 Answers

3
votes

To INSERT in UserRoles a UserId and a RoleId for a specific Username with a specific Role, you need to redesign your tables such that the User table has a foreign key to the Roles tables as follows:

Users       UserRoles         Roles
=======     =============     =========
UserId      UserId            RoleId
Username    RoleId            Role
Firstname   Date
RoleId

Then insert as follows: 

insert into dbo.UserRoles (UserId, RoleId, [Date])
select
    u.UserId,
    r.RoleId,
    getdate() as [Date]
from dbo.Users u
inner join dbo.Roles r
   on r.RoleId = u.RoleId
   and u.Username = @Username

To delete a role for a specific Username with the above schema:

delete r
from dbo.Roles r
inner join dbo.User u
   on r.RoleId = u.Roleid
where u.Username = @Username

EDIT: Another solution suggested by @sidux

DELETE ur 
FROM dbo.UserRoles ur 
JOIN dbo.Roles r 
    ON r.RoleId = ur.RoleId 
INNER JOIN dbo.Users u 
    ON ur.UserId = u.UserId 
WHERE (Username = @Username);
0
votes

you can use Merge and write everything in proc.Don't forget to Test and give error details here.

I am using @Flg because you may need Update Statement also.in that case it will be helpful .

Declare @Flg char(1)='D' -- D/I/U

Declare @Username varchar(50)

Merge into UserRoles  as trg
using(Select
    u.UserId,
    r.RoleId,
    getdate() as [Date]
from dbo.Users u
inner join dbo.Roles r
   on r.RoleId = u.RoleId
   and u.Username = @Username)src
on trg.UserId=src.UserId and trg.RoleId=src.RoleId
when not matched and @Flg='I' THen
insert values(src.UserId,src.RoleId,[Date])
When matched and  @Flg='D' THen
Delete ;