UNIQUE CONSTRAINT 1

[Luzian]

CREATE TABLE [UsersRoles_MAP]
(
[tab][user_id] int NOT NULL FOREIGN KEY REFERENCES [Users]([id]),
[tab][role_id] int NOT NULL FOREIGN KEY REFERENCES [Roles]([id]),
[tab][red]CONSTRAINT UNIQUE ([user_id], [role_id])[/red]
)

What is the syntax to make sure a combination of role_id and user_id is unique?

I never tried this before, and the code in red, does not work.

 

[jbenson001]

ALTER TABLE [dbo].[UsersRoles_MAP] ADD 
	CONSTRAINT [IX_SomeName] UNIQUE  NONCLUSTERED 
	(
		[user_id],
		[role_id]
	)  ON [PRIMARY] 
GO

 

[Luzian]

Thanks. Can you explain what [tt]NONCLUSTERED[/tt] and [tt]ON [PRIMARY][/tt] does?

 

[Luzian]

And I don't want to specify a name for the index. I'd rather use SQL Server's way of assigning names to keys than my memory.

 

[Luzian]

To answer my own question, you just leave out "CONSTRAINT [IX_SomeName]" if you don't want to name it.

ALTER TABLE [dbo].[UsersRoles_MAP]
ADD UNIQUE
(
[tab][user_id],
[tab][role_id]
)
GO

 

[jbenson001]

Glad you got it

Jim