checking for duplicate entries

[DTSFreak]

Hi,

I've a problem to check if there is a duplicate entry.

In my table i have a computed column which checks for a certain type of entry which must be unique. E.g. there can be two types of entries. One which has a type of 1 where period can be 6 or 7. The other type is 2 where period can be from 1 to 5.

Now what i would like to do is when type 1 is being entered it can only be unique for 6 or 7. There cannot be both entries of 6 and 7. For type 2 it should be unique within each period, in that case there may be more entries.

E.g.

type | contactid | period
1 1 6
1 1 7 <-should not be possible
2 1 1
2 1 2
2 1 3
2 5 1
2 5 1 <-should not be possible



Below is my ddl of my table and trigger:

CREATE TABLE [Mytable] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[Type] [int] NOT NULL ,
[contactId] [int] NOT NULL ,
[Period] [int] NOT NULL ,
[Date] [datetime] NOT NULL ,
[TypeChk] AS (case when ([Type] = 1 and (([Period] = 6 or [Period] = 7))) then null else [Id] end) ,
CONSTRAINT [PK_Mytable] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY] ,
CONSTRAINT [IX_Mytable] UNIQUE NONCLUSTERED
(
[Id],
[TypeChk]
) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TRIGGER DupCheck ON [dbo].[Mytable]
FOR INSERT, UPDATE
AS

IF (select count(contactid)
from mytable
group by contactid, period, type
having count(contactid)>1 and type=2)>1
ROLLBACK TRANSACTION
RAISERROR('there is a duplicate entry',16,1)


Can anyone help me?

 

[druer]

To me a computer column means that the value is computed by the system based on other values in the table. You say computed, but the rest of your conversation implies that somebody/something assigns the value of 6 or 7, which implies that the field is not computed but normal input.

If the field is being input, then it makes sense to me that what you want is a unique constraint on all 3 fields: Type, Contact, PeriodId. A unique constaint on those 3 fields should remove the ability ever input duplicates.