Need help to prevent row to insert

• Little Nick

  SSCrazy

  Points: 2202

  More actions

  February 23, 2019 at 12:24 am

  #381555

  My table and data as following,
  
USE [myERPMARA]
GO
ALTER TABLE [dbo].[firstTimeReg_UsrCommunity] DROP CONSTRAINT [CK_firstTimeReg_UsrCommunity]
GO
/****** Object: Index [firstTimeReg_UsrCommunity_UQ1]  Script Date: 23/2/2019 3:16:27 PM ******/
ALTER TABLE [dbo].[firstTimeReg_UsrCommunity] DROP CONSTRAINT [firstTimeReg_UsrCommunity_UQ1]
GO
/****** Object: Table [dbo].[firstTimeReg_UsrCommunity]  Script Date: 23/2/2019 3:16:27 PM ******/
DROP TABLE [dbo].[firstTimeReg_UsrCommunity]
GO
/****** Object: Table [dbo].[firstTimeReg_UsrCommunity]  Script Date: 23/2/2019 3:16:27 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[firstTimeReg_UsrCommunity](
    [idx] [int] IDENTITY(1,1) NOT NULL,
    [ComputerGenNo] [varchar](20) NOT NULL,
    [ERPMaraUserTypeCode] [varchar](50) NOT NULL,
    [fullNme] [varchar](200) NOT NULL,
    [icNo] [varchar](20) NOT NULL,
     [varchar](50) NOT NULL,
    [hpNo] [varchar](50) NOT NULL,
    [activateId] [uniqueidentifier] NOT NULL,
    [smsId] [char](6) NULL,
    [whatStatus] [char](1) NOT NULL CONSTRAINT [DF_firstTimeReg_UsrCommunity_whatStatus] DEFAULT ((1)),
    [isActivated] [bit] NOT NULL CONSTRAINT [DF_firstTimeReg_UsrCommunity_isActivated] DEFAULT ('false'),
    [crtDte] [datetime] NOT NULL CONSTRAINT [DF_firstTimeReg_UsrCommunity_crtDte] DEFAULT (getdate()),
    [updDte] [datetime] NULL,
    [dtVersion] [timestamp] NULL,
CONSTRAINT [PK_firstTimeReg_UsrCommunity] PRIMARY KEY CLUSTERED
(
    [icNo] 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
SET IDENTITY_INSERT [dbo].[firstTimeReg_UsrCommunity] ON

  GO
INSERT [dbo].[firstTimeReg_UsrCommunity] ([idx], [ComputerGenNo], [ERPMaraUserTypeCode], [fullNme], [icNo], , [hpNo], [activateId], [smsId], [whatStatus], [isActivated], [crtDte], [updDte]) VALUES (1004, N'PTP201902000001', N'004', N'Salmi Binti Aman', N'750318086756', N'salmi.aman.6181@gmail.com', N'0122315157', N'437b82b1-bff4-44bd-a855-c5d846b51b1f', N'222633', N'2', 1, CAST(N'2019-02-23 12:14:10.410' AS DateTime), CAST(N'2019-02-23 12:17:13.970' AS DateTime))
GO
INSERT [dbo].[firstTimeReg_UsrCommunity] ([idx], [ComputerGenNo], [ERPMaraUserTypeCode], [fullNme], [icNo], , [hpNo], [activateId], [smsId], [whatStatus], [isActivated], [crtDte], [updDte]) VALUES (1005, N'PTP201902000002', N'004', N'Sharul Nizam Bin Ab Rahim', N'790318145357', N'wkm1925@gmail.com', N'0127413497', N'ce3bbe3b-7e34-47d6-9e95-0dc908280767', N'811987', N'2', 1, CAST(N'2019-02-23 12:58:59.677' AS DateTime), CAST(N'2019-02-23 13:03:38.853' AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[firstTimeReg_UsrCommunity] OFF
GO
/****** Object: Index [firstTimeReg_UsrCommunity_UQ1]  Script Date: 23/2/2019 3:16:27 PM ******/
ALTER TABLE [dbo].[firstTimeReg_UsrCommunity] ADD CONSTRAINT [firstTimeReg_UsrCommunity_UQ1] UNIQUE NONCLUSTERED
(
    [activateId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[firstTimeReg_UsrCommunity] WITH CHECK ADD CONSTRAINT [CK_firstTimeReg_UsrCommunity] CHECK (([whatStatus]='1' OR [whatStatus]='2'))
GO
ALTER TABLE [dbo].[firstTimeReg_UsrCommunity] CHECK CONSTRAINT [CK_firstTimeReg_UsrCommunity]
GO

  I know how to using Unique contraint. But how to prevent below having a row?
  
INSERT [dbo].[firstTimeReg_UsrCommunity] ([idx], [ComputerGenNo], [ERPMaraUserTypeCode], [fullNme], [icNo],
, [hpNo], [activateId], [smsId], [whatStatus], [isActivated], [crtDte], [updDte])
VALUES (1005, N'PTP201902000002', N'004', N'Alexander', N'790318145357',
N'wkm1925@gmail.com', N'0127413497', N'ce3bbe3b-7e34-47d6-9e95-0dc908280767', N'811987',
N'2', 1, CAST(N'2019-02-23 12:58:59.677' AS DateTime),
CAST(N'2019-02-23 13:03:38.853' AS DateTime))


  1) Same email with whatStatus=2 is prohibited

  2) Different email with whatStatus=2 is ok
  3) Same  email with whatStatus=1 is ok

  Please help

• pietlinden

  SSC Guru

  Points: 63506

  More actions

  February 23, 2019 at 1:26 am

  #2022732

  Little Nick - Saturday, February 23, 2019 12:24 AM

  My table and data as following,
  
  USE [myERPMARA]
  GO
  ALTER TABLE [dbo].[firstTimeReg_UsrCommunity] DROP CONSTRAINT [CK_firstTimeReg_UsrCommunity]
  GO
  /****** Object: Index [firstTimeReg_UsrCommunity_UQ1]  Script Date: 23/2/2019 3:16:27 PM ******/
  ALTER TABLE [dbo].[firstTimeReg_UsrCommunity] DROP CONSTRAINT [firstTimeReg_UsrCommunity_UQ1]
  GO
  /****** Object: Table [dbo].[firstTimeReg_UsrCommunity]  Script Date: 23/2/2019 3:16:27 PM ******/
  DROP TABLE [dbo].[firstTimeReg_UsrCommunity]
  GO
  /****** Object: Table [dbo].[firstTimeReg_UsrCommunity]  Script Date: 23/2/2019 3:16:27 PM ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  SET ANSI_PADDING ON
  GO
  CREATE TABLE [dbo].[firstTimeReg_UsrCommunity](
      [idx] [int] IDENTITY(1,1) NOT NULL,
      [ComputerGenNo] [varchar](20) NOT NULL,
      [ERPMaraUserTypeCode] [varchar](50) NOT NULL,
      [fullNme] [varchar](200) NOT NULL,
      [icNo] [varchar](20) NOT NULL,
       [varchar](50) NOT NULL,
      [hpNo] [varchar](50) NOT NULL,
      [activateId] [uniqueidentifier] NOT NULL,
      [smsId] [char](6) NULL,
      [whatStatus] [char](1) NOT NULL CONSTRAINT [DF_firstTimeReg_UsrCommunity_whatStatus] DEFAULT ((1)),
      [isActivated] [bit] NOT NULL CONSTRAINT [DF_firstTimeReg_UsrCommunity_isActivated] DEFAULT ('false'),
      [crtDte] [datetime] NOT NULL CONSTRAINT [DF_firstTimeReg_UsrCommunity_crtDte] DEFAULT (getdate()),
      [updDte] [datetime] NULL,
      [dtVersion] [timestamp] NULL,
  CONSTRAINT [PK_firstTimeReg_UsrCommunity] PRIMARY KEY CLUSTERED
  (
      [icNo] 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
  SET IDENTITY_INSERT [dbo].[firstTimeReg_UsrCommunity] ON

  GO
  INSERT [dbo].[firstTimeReg_UsrCommunity] ([idx], [ComputerGenNo], [ERPMaraUserTypeCode], [fullNme], [icNo], , [hpNo], [activateId], [smsId], [whatStatus], [isActivated], [crtDte], [updDte]) VALUES (1004, N'PTP201902000001', N'004', N'Salmi Binti Aman', N'750318086756', N'salmi.aman.6181@gmail.com', N'0122315157', N'437b82b1-bff4-44bd-a855-c5d846b51b1f', N'222633', N'2', 1, CAST(N'2019-02-23 12:14:10.410' AS DateTime), CAST(N'2019-02-23 12:17:13.970' AS DateTime))
  GO
  INSERT [dbo].[firstTimeReg_UsrCommunity] ([idx], [ComputerGenNo], [ERPMaraUserTypeCode], [fullNme], [icNo], , [hpNo], [activateId], [smsId], [whatStatus], [isActivated], [crtDte], [updDte]) VALUES (1005, N'PTP201902000002', N'004', N'Sharul Nizam Bin Ab Rahim', N'790318145357', N'wkm1925@gmail.com', N'0127413497', N'ce3bbe3b-7e34-47d6-9e95-0dc908280767', N'811987', N'2', 1, CAST(N'2019-02-23 12:58:59.677' AS DateTime), CAST(N'2019-02-23 13:03:38.853' AS DateTime))
  GO
  SET IDENTITY_INSERT [dbo].[firstTimeReg_UsrCommunity] OFF
  GO
  /****** Object: Index [firstTimeReg_UsrCommunity_UQ1]  Script Date: 23/2/2019 3:16:27 PM ******/
  ALTER TABLE [dbo].[firstTimeReg_UsrCommunity] ADD CONSTRAINT [firstTimeReg_UsrCommunity_UQ1] UNIQUE NONCLUSTERED
  (
      [activateId] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  GO
  ALTER TABLE [dbo].[firstTimeReg_UsrCommunity] WITH CHECK ADD CONSTRAINT [CK_firstTimeReg_UsrCommunity] CHECK (([whatStatus]='1' OR [whatStatus]='2'))
  GO
  ALTER TABLE [dbo].[firstTimeReg_UsrCommunity] CHECK CONSTRAINT [CK_firstTimeReg_UsrCommunity]
  GO

  I know how to using Unique contraint. But how to prevent below having a row?
  
INSERT [dbo].[firstTimeReg_UsrCommunity] ([idx], [ComputerGenNo], [ERPMaraUserTypeCode], [fullNme], [icNo],
, [hpNo], [activateId], [smsId], [whatStatus], [isActivated], [crtDte], [updDte])
VALUES (1005, N'PTP201902000002', N'004', N'Alexander', N'790318145357',
N'wkm1925@gmail.com', N'0127413497', N'ce3bbe3b-7e34-47d6-9e95-0dc908280767', N'811987',
N'2', 1, CAST(N'2019-02-23 12:58:59.677' AS DateTime),
CAST(N'2019-02-23 13:03:38.853' AS DateTime))


  1) Same email with whatStatus=2 is prohibited

  2) Different email with whatStatus=2 is ok
  3) Same  email with whatStatus=1 is ok

  Please help

  You'd have to create a stored procedure to insert into the table and check the table for the existence of those combinations, because unless I'm missing something, there's no way to express those rules with a UNIQUE constraint.

• Little Nick

  SSCrazy

  Points: 2202

  More actions

  February 23, 2019 at 2:01 am

  #2022733

  Oh ok. If there's no way to express those rules in table, I will write SQL in my Stored Procedure

  Thanks your Sir

• Eirikur Eiriksson

  SSC Guru

  Points: 182915

  More actions

  February 23, 2019 at 3:47 am

  #2022736

  Suggest you add a calculated column for the constraint
  😎

  Here is an example
  
ROW_HASH AS (HASHBYTES('MD5',CONCAT(,[whatStatus]))) PERSISTED CONSTRAINT UNQ_DBO_FIRSTTIMEREG_USRCOMMUNITY_EMAIL_STATUS UNIQUE