Index on two columns doesn't allow NULL in both - HELP!

Table DDL below:

The tables I have contain Timesheet information.  Each row in the
tblTSCollected table contains an entry for an employee into the
timesheet system, specifically by scanning the barcode on their badge.

A whole bunch of business logic periodically attempts to "pair" these
into logically matched scans.  For example, some employees will scan in
and out of a single place of work.  For these there will be a row
written to the tblTSRuleApplied table which contains, inter alia and
some redundant data, the fldCollectedID for the two rows.  The earlier
will be put into the  fldStartTimeCollecte
dID, and the later into the
 fldEndTimeCollectedI
D.  Some employees will clock on at their base,
then perform sub-duties at different locations during the day, and
clock off at their home base at the end of their shift.  For these, the
system would identify the outer records as a matching pair, and then
pair up inner records by location.

However, if the employee fails to enter a valid "clocking in and out"
pair (for example, if they clock in at the wrong location) the system
needs to generate a "dummy" "clocking in and out" record for the
payroll department.  Ideally, this would have NULL values in the
 fldStartTimeCollecte
dID and  fldEndTimeCollectedI
D columns.  This would
alert a user in a different part of the system, where missing
timesheets were being arbitrated, that an employee appeared to have
failed to clock in for that day.  Of course, the user could see
on-screen that they had clocked in, but at an incorrect location.

Unfortunately, the database designer is not here for the moment (he was
knocked off his bicycle recently), but he put a unique index on the
tblTSRuleApplied table that prevents the same value being entered into
the  fldStartTimeCollecte
dID and  fldEndTimeCollectedI
D columns.  This is
generally A Good Thing, since we don't want the same timesheet scan to
form both a "clocking on" event and a "clocking off" event.

So, is there any way of retaining the requirement that the
 fldStartTimeCollecte
dID and the  fldEndTimeCollectedI
D columns may not
contain the same value in a single row, UNLESS that value is NULL in
which case all is hunky dory.  I should add that the clients don't much
care for Triggers (and neither do I for that matter).

Many thanks if you are able to help.

Edward


if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].& #91;FK_tblTSRuleAppl
 ied_tblTSCollected]'
) and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].& #91;tblTSRuleApplied
] DROP CONSTRAINT
 FK_tblTSRuleApplied_
tblTSCollected
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].& #91;FK_tblTSRuleAppl
 ied_tblTSCollected1]
') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].& #91;tblTSRuleApplied
] DROP CONSTRAINT
 FK_tblTSRuleApplied_
tblTSCollected1
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].& #91;FK_tblTSArbAccep
 t_tblTSRuleApplied]'
) and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblTSArbAccept] DROP CONSTRAINT
 FK_tblTSArbAccept_tb
lTSRuleApplied
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].& #91;FK_tblTSCollecte
 d_tblTSRuleApplied]'
) and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblTSCollected] DROP CONSTRAINT
 FK_tblTSCollected_tb
lTSRuleApplied
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].& #91;tblTSCollected]'
) and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblTSCollected]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].& #91;tblTSRuleApplied
]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].& #91;tblTSRuleApplied
]
GO

CREATE TABLE [dbo].[tblTSCollected] (
[fldCollectedID] [int] IDENTITY (1, 1) NOT NULL ,
[fldEmployeeID] [int] NULL ,
& #91;fldLocationCode]
 [varchar] (50) COLLATE  SQL_Latin1_General_C
P1_CI_A
S
NULL ,
[fldTimeStamp] [datetime] NULL ,
& #91;fldRuleAppliedID
] [int] NULL ,
[fldBarCode] [varchar] (50) COLLATE  SQL_Latin1_General_C
P1_CI_AS NUL
L
,
[fldProcessed] [smallint] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].& #91;tblTSRuleApplied
] (
[fldEmpRuleID] [int] NOT NULL ,
& #91;fldRuleAppliedID
] [int] IDENTITY (1, 1) NOT NULL ,
[fldStartTime] [datetime] NULL ,
[fldEndTime] [datetime] NULL ,
& #91;fldStartTimeColl
ectedID] [int] NULL ,
& #91;fldEndTimeCollec
tedID] [int] NULL ,
& #91;fldStartArbStatu
s] [smallint] NULL ,
& #91;fldEndArbStatus]
 [smallint] NULL ,
& #91;fldDurationArbSt
atus] [smallint] NULL ,
[fldPrimary] [smallint] NOT NULL ,
[fldDateEntered] [datetime] NULL ,
[fldEnteredBy] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblTSCollected] WITH NOCHECK ADD
CONSTRAINT & #91;DF_tblTSCollecte
d_fldProcessed] DEFAULT (0) FOR
[fldProcessed],
CONSTRAINT & #91;PK_tblTimesheetC
ollected] PRIMARY KEY  CLUSTERED
(
[fldCollectedID]
) WITH  FILLFACTOR = 90  ON [PRIMARY]
GO

ALTER TABLE [dbo].& #91;tblTSRuleApplied
] WITH NOCHECK ADD
CONSTRAINT & #91;DF_tblTSRuleAppl
ied_fldPrimary] DEFAULT (1) FOR
[fldPrimary],
CONSTRAINT & #91;PK_tblTSRuleAppl
ied] PRIMARY KEY  CLUSTERED
(
& #91;fldRuleAppliedID
]
) WITH  FILLFACTOR = 90  ON [PRIMARY] ,
CONSTRAINT & #91;IX_tblTSRuleAppl
ied_1] UNIQUE  NONCLUSTERED
(
& #91;fldStartTimeColl
ectedID],
& #91;fldEndTimeCollec
tedID]
) WITH  FILLFACTOR = 90  ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblTSCollected] ADD
CONSTRAINT & #91;FK_tblTSCollecte
d_tblEmployee1] FOREIGN KEY
(
[fldEmployeeID]
) REFERENCES [dbo].[tblEmployee] (
[fldEmployeeID]
),
CONSTRAINT & #91;FK_tblTSCollecte
d_tblLocation] FOREIGN KEY
(
& #91;fldLocationCode]

) REFERENCES [dbo].[tblLocation] (
& #91;fldLocationCode]

),
CONSTRAINT & #91;FK_tblTSCollecte
d_tblTSRuleApplied] FOREIGN KEY
(
& #91;fldRuleAppliedID
]
) REFERENCES [dbo].& #91;tblTSRuleApplied
] (
& #91;fldRuleAppliedID
]
)
GO

ALTER TABLE [dbo].& #91;tblTSRuleApplied
] ADD
CONSTRAINT & #91;FK_tblTSRuleAppl
ied_tblTSCollected] FOREIGN KEY
(
& #91;fldStartTimeColl
ectedID]
) REFERENCES [dbo].[tblTSCollected] (
[fldCollectedID]
),
CONSTRAINT & #91;FK_tblTSRuleAppl
 ied_tblTSCollected1]
 FOREIGN KEY
(
& #91;fldEndTimeCollec
tedID]
) REFERENCES [dbo].[tblTSCollected] (
[fldCollectedID]
),
CONSTRAINT & #91;FK_tblTSRuleAppl
 ied_tblTSDurationSta
tus] FOREIGN KEY
(
& #91;fldDurationArbSt
atus]
) REFERENCES [dbo].& #91;tblTSDurationSta
tus] (
[fldStatus]
),
CONSTRAINT & #91;FK_tblTSRuleAppl
ied_tblTSEmpRules] FOREIGN KEY
(
[fldEmpRuleID]
) REFERENCES [dbo].[tblTSEmpRules] (
[fldEmpRuleID]
),
CONSTRAINT & #91;FK_tblTSRuleAppl
 ied_tblTSTimeStatus]
 FOREIGN KEY
(
& #91;fldStartArbStatu
s]
) REFERENCES [dbo].& #91;tblTSTimeStatus]
 (
[fldStatus]
),
CONSTRAINT & #91;FK_tblTSRuleAppl
 ied_tblTSTimeStatus1
] FOREIGN KEY
(
& #91;fldEndArbStatus]

) REFERENCES [dbo].& #91;tblTSTimeStatus]
 (
[fldStatus]
)
GO

> So, is there any way of retaining the requirement that the
>  fldStartTimeCollecte
dID and the  fldEndTimeCollectedI
D columns may not
> contain the same value in a single row, UNLESS that value is NULL in
> which case all is hunky dory.  I should add that the clients don't much
> care for Triggers (and neither do I for that matter).

There are a couple of methods to accomplish this.  One method is with a
trigger.  Another, with SQL 2000 and above, is using an index view including
non-null values instead of a unique constraint:

CREATE VIEW v_tblTSRuleApplied
WITH SCHEMABINDING
AS
SELECT  fldStartTimeCollecte
dID,  fldEndTimeCollectedI
D
FROM dbo.tblTSRuleApplied
WHERE  fldStartTimeCollecte
dID IS NOT NULL AND
 fldEndTimeCollectedI
D  IS NOT NULL
GO

CREATE UNIQUE CLUSTERED INDEX  v_tblTSRuleApplied_c
dx
ON  v_tblTSRuleApplied(f
 ldStartTimeCollected
ID,  fldEndTimeCollectedI
D)
GO


--
Hope this helps.

Dan Guzman
SQL Server MVP

<teddysnips@hotmail.com> wrote in message
news:1135265109.464713.76030@f14g2000cwb.googlegroups.com...
> Table DDL below:
>
> The tables I have contain Timesheet information.  Each row in the
> tblTSCollected table contains an entry for an employee into the
> timesheet system, specifically by scanning the barcode on their badge.
>
> A whole bunch of business logic periodically attempts to "pair" these
> into logically matched scans.  For example, some employees will scan in
> and out of a single place of work.  For these there will be a row
> written to the tblTSRuleApplied table which contains, inter alia and
> some redundant data, the fldCollectedID for the two rows.  The earlier
> will be put into the  fldStartTimeCollecte
dID, and the later into the
>  fldEndTimeCollectedI
D.  Some employees will clock on at their base,
> then perform sub-duties at different locations during the day, and
> clock off at their home base at the end of their shift.  For these, the
> system would identify the outer records as a matching pair, and then
> pair up inner records by location.
>
> However, if the employee fails to enter a valid "clocking in and out"
> pair (for example, if they clock in at the wrong location) the system
> needs to generate a "dummy" "clocking in and out" record for the
> payroll department.  Ideally, this would have NULL values in the
>  fldStartTimeCollecte
dID and  fldEndTimeCollectedI
D columns.  This would
> alert a user in a different part of the system, where missing
> timesheets were being arbitrated, that an employee appeared to have
> failed to clock in for that day.  Of course, the user could see
> on-screen that they had clocked in, but at an incorrect location.
>
> Unfortunately, the database designer is not here for the moment (he was
> knocked off his bicycle recently), but he put a unique index on the
> tblTSRuleApplied table that prevents the same value being entered into
> the  fldStartTimeCollecte
dID and  fldEndTimeCollectedI
D columns.  This is
> generally A Good Thing, since we don't want the same timesheet scan to
> form both a "clocking on" event and a "clocking off" event.
>
> So, is there any way of retaining the requirement that the
>  fldStartTimeCollecte
dID and the  fldEndTimeCollectedI
D columns may not
> contain the same value in a single row, UNLESS that value is NULL in
> which case all is hunky dory.  I should add that the clients don't much
> care for Triggers (and neither do I for that matter).
>
> Many thanks if you are able to help.
>
> Edward
>
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].& #91;FK_tblTSRuleAppl
 ied_tblTSCollected]'
) and
> OBJECTPROPERTY(id, N'IsForeignKey') = 1)
> ALTER TABLE [dbo].& #91;tblTSRuleApplied
] DROP CONSTRAINT
>  FK_tblTSRuleApplied_
tblTSCollected
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].& #91;FK_tblTSRuleAppl
 ied_tblTSCollected1]
') and
> OBJECTPROPERTY(id, N'IsForeignKey') = 1)
> ALTER TABLE [dbo].& #91;tblTSRuleApplied
] DROP CONSTRAINT
>  FK_tblTSRuleApplied_
tblTSCollected1
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].& #91;FK_tblTSArbAccep
 t_tblTSRuleApplied]'
) and
> OBJECTPROPERTY(id, N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[tblTSArbAccept] DROP CONSTRAINT
>  FK_tblTSArbAccept_tb
lTSRuleApplied
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].& #91;FK_tblTSCollecte
 d_tblTSRuleApplied]'
) and
> OBJECTPROPERTY(id, N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[tblTSCollected] DROP CONSTRAINT
>  FK_tblTSCollected_tb
lTSRuleApplied
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].& #91;tblTSCollected]'
) and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[tblTSCollected]
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].& #91;tblTSRuleApplied
]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].& #91;tblTSRuleApplied
]
> GO
>
> CREATE TABLE [dbo].[tblTSCollected] (
> [fldCollectedID] [int] IDENTITY (1, 1) NOT NULL ,
> [fldEmployeeID] [int] NULL ,
> & #91;fldLocationCode]
 [varchar] (50) COLLATE  SQL_Latin1_General_C
P1_CI
_AS
> NULL ,
> [fldTimeStamp] [datetime] NULL ,
> & #91;fldRuleAppliedID
] [int] NULL ,
> [fldBarCode] [varchar] (50) COLLATE  SQL_Latin1_General_C
P1_CI_AS N
ULL
> ,
> [fldProcessed] [smallint] NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].& #91;tblTSRuleApplied
] (
> [fldEmpRuleID] [int] NOT NULL ,
> & #91;fldRuleAppliedID
] [int] IDENTITY (1, 1) NOT NULL ,
> [fldStartTime] [datetime] NULL ,
> [fldEndTime] [datetime] NULL ,
> & #91;fldStartTimeColl
ectedID] [int] NULL ,
> & #91;fldEndTimeCollec
tedID] [int] NULL ,
> & #91;fldStartArbStatu
s] [smallint] NULL ,
> & #91;fldEndArbStatus]
 [smallint] NULL ,
> & #91;fldDurationArbSt
atus] [smallint] NULL ,
> [fldPrimary] [smallint] NOT NULL ,
> [fldDateEntered] [datetime] NULL ,
> [fldEnteredBy] [int] NULL
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[tblTSCollected] WITH NOCHECK ADD
> CONSTRAINT & #91;DF_tblTSCollecte
d_fldProcessed] DEFAULT (0) FOR
> [fldProcessed],
> CONSTRAINT & #91;PK_tblTimesheetC
ollected] PRIMARY KEY  CLUSTERED
> (
> [fldCollectedID]
> ) WITH  FILLFACTOR = 90  ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].& #91;tblTSRuleApplied
] WITH NOCHECK ADD
> CONSTRAINT & #91;DF_tblTSRuleAppl
ied_fldPrimary] DEFAULT (1) FOR
> [fldPrimary],
> CONSTRAINT & #91;PK_tblTSRuleAppl
ied] PRIMARY KEY  CLUSTERED
> (
> & #91;fldRuleAppliedID
]
> ) WITH  FILLFACTOR = 90  ON [PRIMARY] ,
> CONSTRAINT & #91;IX_tblTSRuleAppl
ied_1] UNIQUE  NONCLUSTERED
> (
> & #91;fldStartTimeColl
ectedID],
> & #91;fldEndTimeCollec
tedID]
> ) WITH  FILLFACTOR = 90  ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[tblTSCollected] ADD
> CONSTRAINT & #91;FK_tblTSCollecte
d_tblEmployee1] FOREIGN KEY
> (
> [fldEmployeeID]
> ) REFERENCES [dbo].[tblEmployee] (
> [fldEmployeeID]
> ),
> CONSTRAINT & #91;FK_tblTSCollecte
d_tblLocation] FOREIGN KEY
> (
> & #91;fldLocationCode]

> ) REFERENCES [dbo].[tblLocation] (
> & #91;fldLocationCode]

> ),
> CONSTRAINT & #91;FK_tblTSCollecte
d_tblTSRuleApplied] FOREIGN KEY
> (
> & #91;fldRuleAppliedID
]
> ) REFERENCES [dbo].& #91;tblTSRuleApplied
] (
> & #91;fldRuleAppliedID
]
> )
> GO
>
> ALTER TABLE [dbo].& #91;tblTSRuleApplied
] ADD
> CONSTRAINT & #91;FK_tblTSRuleAppl
ied_tblTSCollected] FOREIGN KEY
> (
> & #91;fldStartTimeColl
ectedID]
> ) REFERENCES [dbo].[tblTSCollected] (
> [fldCollectedID]
> ),
> CONSTRAINT & #91;FK_tblTSRuleAppl
 ied_tblTSCollected1]
 FOREIGN KEY
> (
> & #91;fldEndTimeCollec
tedID]
> ) REFERENCES [dbo].[tblTSCollected] (
> [fldCollectedID]
> ),
> CONSTRAINT & #91;FK_tblTSRuleAppl
 ied_tblTSDurationSta
tus] FOREIGN KEY
> (
> & #91;fldDurationArbSt
atus]
> ) REFERENCES [dbo].& #91;tblTSDurationSta
tus] (
> [fldStatus]
> ),
> CONSTRAINT & #91;FK_tblTSRuleAppl
ied_tblTSEmpRules] FOREIGN KEY
> (
> [fldEmpRuleID]
> ) REFERENCES [dbo].[tblTSEmpRules] (
> [fldEmpRuleID]
> ),
> CONSTRAINT & #91;FK_tblTSRuleAppl
 ied_tblTSTimeStatus]
 FOREIGN KEY
> (
> & #91;fldStartArbStatu
s]
> ) REFERENCES [dbo].& #91;tblTSTimeStatus]
 (
> [fldStatus]
> ),
> CONSTRAINT & #91;FK_tblTSRuleAppl
 ied_tblTSTimeStatus1
] FOREIGN KEY
> (
> & #91;fldEndArbStatus]

> ) REFERENCES [dbo].& #91;tblTSTimeStatus]
 (
> [fldStatus]
> )
> GO
>

Dan Guzman  wrote: 
>
> There are a couple of methods to accomplish this.  One method is with a
> trigger.  Another, with SQL 2000 and above, is using an index view includi
ng
> non-null values instead of a unique constraint:
[snip]

Many thanks - I'll put this to the vote just after the holidays.

I *love* usenet.

Edward

I don't think Mr. Guzman's solution will work for the business problem
you are trying to solve. It does allow the index, but you will never be
able to retrieve any of the data where EITHER start OR end time is
null.

I guess I'm trying to understand when a record would be created when
both entries are null?

On 22 Dec 2005 11:26:28 -0800, Doug wrote:

>I don't think Mr. Guzman's solution will work for the business problem
>you are trying to solve. It does allow the index, but you will never be
>able to retrieve any of the data where EITHER start OR end time is
>null.

Hi Doug,

Not from the indexed view, but you can still get this data from the
table itself.

The view suggested by Dan is intended merely to enforce the constraint,
not to replace the table in queries.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Hmmmm.......

I don't think the view forces the constraint onto the table. Is this
correct?

On 22 Dec 2005 16:06:57 -0800, Doug wrote:

>Hmmmm.......
>
>I don't think the view forces the constraint onto the table. Is this
>correct?

Hi Doug,

Have you tried it?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

> I don't think the view forces the constraint onto the table. Is this
> correct?

SQL Server automatically maintains the view index to reflect underlying
table changes.  This will have the effect of a unique constraint that
ignores null values.  Duplicate non-null values will not be allowed in the
underlying table.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Doug" <drmiller100@hotmail.com> wrote in message
news:1135296417.507570.119610@f14g2000cwb.googlegroups.com...
> Hmmmm.......
>
> I don't think the view forces the constraint onto the table. Is this
> correct?
>