REATE TABLE [dbo].[UserSpecializations](
	[UserSpecializationRecId] [int] NOT NULL,
	[Username] [varchar](100) NOT NULL,
	[Password] [varchar](100) NULL,
	[ShouldChangePassword] [bit] NOT NULL,
	[LastLoginDateTime] [datetimeoffset](0) NULL,
	[SessionRecId] [varchar](100) NULL,
 CONSTRAINT [PK_UserSpecializations_UserSpecializationRecId] PRIMARY KEY CLUSTERED 
(
	[UserSpecializationRecId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[UserSpecializations]  WITH CHECK ADD  CONSTRAINT [FK_UserSpecializations_PersonSpecializations] FOREIGN KEY([UserSpecializationRecId])
REFERENCES [dbo].[PersonSpecializations] ([PersonSpecializationRecId])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[UserSpecializations] CHECK CONSTRAINT [FK_UserSpecializations_PersonSpecializations]
GO

ALTER TABLE [dbo].[UserSpecializations] ADD  CONSTRAINT [DF_Users_ShouldChangePassword]  DEFAULT ((0)) FOR [ShouldChangePassword]
GO

CREATE TABLE [dbo].[UserRoles](
	[UserRoleRecId] [int] IDENTITY(1,1) NOT NULL,
	[UserSpecializationRecId] [int] NOT NULL,
	[RoleRecId] [int] NOT NULL,
 CONSTRAINT [PK_UserInRole] PRIMARY KEY CLUSTERED 
(
	[UserRoleRecId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[UserRoles]  WITH CHECK ADD  CONSTRAINT [FK_UserRoles_Roles] FOREIGN KEY([RoleRecId])
REFERENCES [dbo].[Roles] ([RoleRecId])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[UserRoles] CHECK CONSTRAINT [FK_UserRoles_Roles]
GO

ALTER TABLE [dbo].[UserRoles]  WITH CHECK ADD  CONSTRAINT [FK_UserRoles_UserSpecializations] FOREIGN KEY([UserSpecializationRecId])
REFERENCES [dbo].[UserSpecializations] ([UserSpecializationRecId])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[UserRoles] CHECK CONSTRAINT [FK_UserRoles_UserSpecializations]
GO

CREATE TABLE [dbo].[Roles](
	[RoleRecId] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](100) NULL,
	[Description] [varchar](500) NULL,
	[RoleType] [varchar](50) NULL,
	[CreationDateTime] [datetimeoffset](0) NOT NULL,
	[RoleStatus] [tinyint] NOT NULL,
 CONSTRAINT [PK_Roles_RoleRecId] PRIMARY KEY CLUSTERED 
(
	[RoleRecId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [UNQ_Roles_Name] UNIQUE NONCLUSTERED 
(
	[Name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Roles] ADD  CONSTRAINT [DF_Roles_CreationDateTime]  DEFAULT (sysdatetimeoffset()) FOR [CreationDateTime]
GO

ALTER TABLE [dbo].[Roles] ADD  CONSTRAINT [DF_Roles_RoleStatus]  DEFAULT ((1)) FOR [RoleStatus]
GO

CREATE FUNCTION CheckUserRoles
(@UserSpecialisationRecId int)
RETURNS int
AS 
BEGIN
   DECLARE @UserRoleCnt int
   SELECT @UserRoleCnt = COUNT(*) FROM [dbo].[UserRoles]
   WHERE [UserSpecializationRecId] = @UserSpecialisationRecId
   RETURN @UserRoleCnt
END
GO

ALTER TABLE [dbo].[UserSpecializations]
ADD CONSTRAINT chkUserRoles CHECK (dbo.CheckUserRoles([UserSpecializationRecId]) <= 1 )

quote:

---

Originally posted by visakh16

you've to make use of a UDF for that.

see below

CREATE FUNCTION CheckUserRoles
(@UserSpecialisationRecId int)
RETURNS int
AS 
BEGIN
   DECLARE @UserRoleCnt int
   SELECT @UserRoleCnt = COUNT(*) FROM [dbo].[UserRoles]
   WHERE [UserSpecializationRecId] = @UserSpecialisationRecId
   RETURN @UserRoleCnt
END
GO

ALTER TABLE [dbo].[UserSpecializations]
ADD CONSTRAINT chkUserRoles CHECK (dbo.CheckUserRoles([UserSpecializationRecId]) <= 1 )

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

quote:

---

Originally posted by Djaved

Hi visakh16,

Thanks for your reply.
I'm very new in sql code, can you explain to me what your example code does? does it prevent me to link more then one userspecializations to a role?

Thanks in advance.

quote:

---

Originally posted by visakh16

you've to make use of a UDF for that.

see below

CREATE FUNCTION CheckUserRoles
(@UserSpecialisationRecId int)
RETURNS int
AS 
BEGIN
   DECLARE @UserRoleCnt int
   SELECT @UserRoleCnt = COUNT(*) FROM [dbo].[UserRoles]
   WHERE [UserSpecializationRecId] = @UserSpecialisationRecId
   RETURN @UserRoleCnt
END
GO

ALTER TABLE [dbo].[UserSpecializations]
ADD CONSTRAINT chkUserRoles CHECK (dbo.CheckUserRoles([UserSpecializationRecId]) <= 1 )

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

---

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by Djaved

Hi visakh16,

Thanks for your reply.
I'm very new in sql code, can you explain to me what your example code does? does it prevent me to link more then one userspecializations to a role?

Thanks in advance.

quote:

---

Originally posted by visakh16

you've to make use of a UDF for that.

see below

CREATE FUNCTION CheckUserRoles
(@UserSpecialisationRecId int)
RETURNS int
AS 
BEGIN
   DECLARE @UserRoleCnt int
   SELECT @UserRoleCnt = COUNT(*) FROM [dbo].[UserRoles]
   WHERE [UserSpecializationRecId] = @UserSpecialisationRecId
   RETURN @UserRoleCnt
END
GO

ALTER TABLE [dbo].[UserSpecializations]
ADD CONSTRAINT chkUserRoles CHECK (dbo.CheckUserRoles([UserSpecializationRecId]) <= 1 )

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

---

yep...exactly
it puts check constraint in place and whenever you try to assign more than one role to specialisation it will cause it to violate constraint so it wont allow multi role assignment

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---