How to make this Constraint - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to make this Constraint

Author

Topic

Studevs
Starting Member

15 Posts

Posted - 2012-08-06 : 10:41:45

Dear all,

I have this table in my database:


USE [MyDb]
GO

/****** Object:  Table [dbo].[Persons]    Script Date: 08/06/2011 16:22:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Persons](
	[PersonRecId] [int] IDENTITY(1,1) NOT NULL,
	[PersonNumber] [varchar](100) NOT NULL,
	[FirstName] [varchar](100) NOT NULL,
	[LastName] [varchar](100) NOT NULL,
	[MiddleName] [varchar](100) NOT NULL,
	[NameSuffix] [varchar](100) NOT NULL,
	[MaidenName] [varchar](100) NOT NULL,
	[FullName]  AS (case when [IsAnonymous]=(1) then [FirstName] else ltrim(rtrim(([FirstName]+' ')+ltrim(([MiddleName]+' ')+[LastName]))) end) PERSISTED,
	[Street] [varchar](100) NOT NULL,
	[HouseNumber] [varchar](100) NOT NULL,
	[ZipCode] [varchar](20) NOT NULL,
	[City] [varchar](100) NOT NULL,
	[Gender] [tinyint] NOT NULL,
	[Birthdate] [date] NULL,
	[EmailAddress] [varchar](100) NULL,
	[FaxNumber] [varchar](30) NULL,
	[PhoneNumber] [varchar](30) NULL,
	[IsAnonymous] [bit] NOT NULL,
	[PersonStatus] [tinyint] NULL,
	[CountryRecId] [smallint] NULL,
	[MobileNumber] [varchar](30) NULL,
	[Removed] [bit] NOT NULL,
 CONSTRAINT [PK_Persons_PersonRecId] PRIMARY KEY CLUSTERED 
(
	[PersonRecId] 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].[Persons]  WITH CHECK ADD  CONSTRAINT [FK_Persons_Countries] FOREIGN KEY([CountryRecId])
REFERENCES [dbo].[Countries] ([CountryRecId])
GO

ALTER TABLE [dbo].[Persons] ADD  CONSTRAINT [DF_Persons_FirstName]  DEFAULT ('') FOR [FirstName]
GO

ALTER TABLE [dbo].[Persons] ADD  CONSTRAINT [DF_Persons_LastName]  DEFAULT ('') FOR [LastName]
GO

ALTER TABLE [dbo].[Persons] ADD  CONSTRAINT [DF_Persons_MiddleName]  DEFAULT ('') FOR [MiddleName]
GO

ALTER TABLE [dbo].[Persons] ADD  CONSTRAINT [DF_Persons_NameSuffix]  DEFAULT ('') FOR [NameSuffix]
GO

ALTER TABLE [dbo].[Persons] ADD  CONSTRAINT [DF_Persons_MaidenName]  DEFAULT ('') FOR [MaidenName]
GO

ALTER TABLE [dbo].[Persons] ADD  CONSTRAINT [DF_Persons_Street]  DEFAULT ('') FOR [Street]
GO

ALTER TABLE [dbo].[Persons] ADD  CONSTRAINT [DF_Persons_HouseNumber]  DEFAULT ('') FOR [HouseNumber]
GO

ALTER TABLE [dbo].[Persons] ADD  CONSTRAINT [DF_Persons_PersonStatus]  DEFAULT ((1)) FOR [PersonStatus]
GO

ALTER TABLE [dbo].[Persons] ADD  CONSTRAINT [DF_Persons_Removed]  DEFAULT ((0)) FOR [Removed]
GO

Now when i try to insert a new Person in my table with PersonNumber 'AlphaOmega', i'm getting the message :

'Cannot insert duplicate key row in object 'dbo.Persons' with unique index 'UNQ_Persons_PersonNumber'. The duplicate key value is (AlphaOmega).
The statement has been terminated.'

But i want to get this message only when the Person with PersonNumber 'AlphaOmega' , has 0 in his 'Removed' column.
So when Removed = 1 , then i want to be able to reuse the PersonNumber.

How can i do this, please some help.

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-06 : 10:58:43

then you should remove unique index on PersonNumber and replace it with check constraint like


ALTER TABLE dbo.Persons ADD CONSTRAINT Chk_PersonNumber CHECK (dbo.PersonNumberReuse(PersonNumber,Removed)=1)

AND function PersonNumberReuse should be like

CREATE FUNCTION PersonNumberReuse
(
@PersonNumber varchar(100),
@Removed bit
)
RETURNS bit
AS
BEGIN
DECALRE @Ret bit,@Cnt int

SELECT @Cnt=COUNT(*) 
FROM dbo.Persons
WHERE PersonNumber=@PersonNumber
AND Removed=@Removed

SELECT @Ret=CASE WHEN @Removed=0 AND @Cnt > 1 THEN 0 ELSE 1 END
RETURN (@Ret)
END

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

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-08-06 : 11:24:38

Think carefully about this. What happens to the "old" person's rows when you select based on person name? Are you going to include them in the search when the person has been replaced?
You obviously know your requirement better than I do but this is a classic antipattern/pitfall and is rarely as simple as you show.

Studevs
Starting Member

15 Posts

Posted - 2012-08-07 : 02:55:20

quote:
Originally posted by visakh16

then you should remove unique index on PersonNumber and replace it with check constraint like

ALTER TABLE dbo.Persons ADD CONSTRAINT Chk_PersonNumber CHECK (dbo.PersonNumberReuse(PersonNumber,Removed)=1)

AND function PersonNumberReuse should be like

CREATE FUNCTION PersonNumberReuse
(
@PersonNumber varchar(100),
@Removed bit
)
RETURNS bit
AS
BEGIN
DECALRE @Ret bit,@Cnt int

SELECT @Cnt=COUNT(*)
FROM dbo.Persons
WHERE PersonNumber=@PersonNumber
AND Removed=@Removed

SELECT @Ret=CASE WHEN @Removed=0 AND @Cnt > 1 THEN 0 ELSE 1 END
RETURN (@Ret)
END

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

Hi Visakh16,

Thank you for your answer.
I tried to create the function like this:



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION PersonNumberReuse
(
@PersonNumber varchar(100),
@Removed bit
)
RETURNS bit
AS
BEGIN
DECALRE @Ret bit,@Cnt int

SELECT @Cnt=COUNT(*) 
FROM dbo.Persons
WHERE PersonNumber=@PersonNumber
AND Removed=@Removed

SELECT @Ret=CASE WHEN @Removed=0 AND @Cnt > 1 THEN 0 ELSE 1 END
RETURN (@Ret)
END
GO

When executing, i'm getting this errors:

Msg 102, Level 15, State 1, Procedure PersonNumberReuse, Line 14
Incorrect syntax near 'DECALRE'.
Msg 137, Level 15, State 1, Procedure PersonNumberReuse, Line 16
Must declare the scalar variable "@Cnt".
Msg 137, Level 15, State 2, Procedure PersonNumberReuse, Line 21
Must declare the scalar variable "@Cnt".
Msg 137, Level 15, State 2, Procedure PersonNumberReuse, Line 22
Must declare the scalar variable "@Ret".

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-08-07 : 03:55:41

Just a typo..

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION PersonNumberReuse
(
@PersonNumber varchar(100),
@Removed bit
)
RETURNS bit
AS
BEGIN
DECLARE @Ret bit,@Cnt int

SELECT @Cnt=COUNT(*)
FROM dbo.Persons
WHERE PersonNumber=@PersonNumber
AND Removed=@Removed

SELECT @Ret=CASE WHEN @Removed=0 AND @Cnt > 1 THEN 0 ELSE 1 END
RETURN (@Ret)
END
GO

--------------------------
http://connectsql.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources