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

[teddysnips]

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 tblTSRuleApplie d table which contains, inter alia and
some redundant data, the fldCollectedID for the two rows. The earlier
will be put into the fldStartTimeCol lectedID, and the later into the
fldEndTimeColle ctedID. 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
fldStartTimeCol lectedID and fldEndTimeColle ctedID 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
tblTSRuleApplie d table that prevents the same value being entered into
the fldStartTimeCol lectedID and fldEndTimeColle ctedID 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
fldStartTimeCol lectedID and the fldEndTimeColle ctedID 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].[FK_tblTSRuleApp lied_tblTSColle cted]') and
OBJECTPROPERTY( id, N'IsForeignKey' ) = 1)
ALTER TABLE [dbo].[tblTSRuleApplie d] DROP CONSTRAINT
FK_tblTSRuleApp lied_tblTSColle cted
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblTSRuleApp lied_tblTSColle cted1]') and
OBJECTPROPERTY( id, N'IsForeignKey' ) = 1)
ALTER TABLE [dbo].[tblTSRuleApplie d] DROP CONSTRAINT
FK_tblTSRuleApp lied_tblTSColle cted1
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblTSArbAcce pt_tblTSRuleApp lied]') and
OBJECTPROPERTY( id, N'IsForeignKey' ) = 1)
ALTER TABLE [dbo].[tblTSArbAccept] DROP CONSTRAINT
FK_tblTSArbAcce pt_tblTSRuleApp lied
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblTSCollect ed_tblTSRuleApp lied]') and
OBJECTPROPERTY( id, N'IsForeignKey' ) = 1)
ALTER TABLE [dbo].[tblTSCollected] DROP CONSTRAINT
FK_tblTSCollect ed_tblTSRuleApp lied
GO

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

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblTSRuleApplie d]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
drop table [dbo].[tblTSRuleApplie d]
GO

CREATE TABLE [dbo].[tblTSCollected] (
[fldCollectedID] [int] IDENTITY (1, 1) NOT NULL ,
[fldEmployeeID] [int] NULL ,
[fldLocationCode] [varchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[fldTimeStamp] [datetime] NULL ,
[fldRuleAppliedI D] [int] NULL ,
[fldBarCode] [varchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
,
[fldProcessed] [smallint] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblTSRuleApplie d] (
[fldEmpRuleID] [int] NOT NULL ,
[fldRuleAppliedI D] [int] IDENTITY (1, 1) NOT NULL ,
[fldStartTime] [datetime] NULL ,
[fldEndTime] [datetime] NULL ,
[fldStartTimeCol lectedID] [int] NULL ,
[fldEndTimeColle ctedID] [int] NULL ,
[fldStartArbStat us] [smallint] NULL ,
[fldEndArbStatus] [smallint] NULL ,
[fldDurationArbS tatus] [smallint] NULL ,
[fldPrimary] [smallint] NOT NULL ,
[fldDateEntered] [datetime] NULL ,
[fldEnteredBy] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblTSCollected] WITH NOCHECK ADD
CONSTRAINT [DF_tblTSCollect ed_fldProcessed] DEFAULT (0) FOR
[fldProcessed],
CONSTRAINT [PK_tblTimesheet Collected] PRIMARY KEY CLUSTERED
(
[fldCollectedID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblTSRuleApplie d] WITH NOCHECK ADD
CONSTRAINT [DF_tblTSRuleApp lied_fldPrimary] DEFAULT (1) FOR
[fldPrimary],
CONSTRAINT [PK_tblTSRuleApp lied] PRIMARY KEY CLUSTERED
(
[fldRuleAppliedI D]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [IX_tblTSRuleApp lied_1] UNIQUE NONCLUSTERED
(
[fldStartTimeCol lectedID],
[fldEndTimeColle ctedID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblTSCollected] ADD
CONSTRAINT [FK_tblTSCollect ed_tblEmployee1] FOREIGN KEY
(
[fldEmployeeID]
) REFERENCES [dbo].[tblEmployee] (
[fldEmployeeID]
),
CONSTRAINT [FK_tblTSCollect ed_tblLocation] FOREIGN KEY
(
[fldLocationCode]
) REFERENCES [dbo].[tblLocation] (
[fldLocationCode]
),
CONSTRAINT [FK_tblTSCollect ed_tblTSRuleApp lied] FOREIGN KEY
(
[fldRuleAppliedI D]
) REFERENCES [dbo].[tblTSRuleApplie d] (
[fldRuleAppliedI D]
)
GO

ALTER TABLE [dbo].[tblTSRuleApplie d] ADD
CONSTRAINT [FK_tblTSRuleApp lied_tblTSColle cted] FOREIGN KEY
(
[fldStartTimeCol lectedID]
) REFERENCES [dbo].[tblTSCollected] (
[fldCollectedID]
),
CONSTRAINT [FK_tblTSRuleApp lied_tblTSColle cted1] FOREIGN KEY
(
[fldEndTimeColle ctedID]
) REFERENCES [dbo].[tblTSCollected] (
[fldCollectedID]
),
CONSTRAINT [FK_tblTSRuleApp lied_tblTSDurat ionStatus] FOREIGN KEY
(
[fldDurationArbS tatus]
) REFERENCES [dbo].[tblTSDurationSt atus] (
[fldStatus]
),
CONSTRAINT [FK_tblTSRuleApp lied_tblTSEmpRu les] FOREIGN KEY
(
[fldEmpRuleID]
) REFERENCES [dbo].[tblTSEmpRules] (
[fldEmpRuleID]
),
CONSTRAINT [FK_tblTSRuleApp lied_tblTSTimeS tatus] FOREIGN KEY
(
[fldStartArbStat us]
) REFERENCES [dbo].[tblTSTimeStatus] (
[fldStatus]
),
CONSTRAINT [FK_tblTSRuleApp lied_tblTSTimeS tatus1] FOREIGN KEY
(
[fldEndArbStatus]
) REFERENCES [dbo].[tblTSTimeStatus] (
[fldStatus]
)
GO

[Dan Guzman]

> So, is there any way of retaining the requirement that the

fldStartTimeCol lectedID and the fldEndTimeColle ctedID 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_tblTSRuleAppl ied
WITH SCHEMABINDING
AS
SELECT fldStartTimeCol lectedID, fldEndTimeColle ctedID
FROM dbo.tblTSRuleAp plied
WHERE fldStartTimeCol lectedID IS NOT NULL AND
fldEndTimeColle ctedID IS NOT NULL
GO

CREATE UNIQUE CLUSTERED INDEX v_tblTSRuleAppl ied_cdx
ON v_tblTSRuleAppl ied(fldStartTim eCollectedID, fldEndTimeColle ctedID)
GO
--
Hope this helps.

Dan Guzman
SQL Server MVP

<te********@hot mail.com> wrote in message
news:11******** *************@f 14g2000cwb.goog legroups.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 tblTSRuleApplie d table which contains, inter alia and
some redundant data, the fldCollectedID for the two rows. The earlier
will be put into the fldStartTimeCol lectedID, and the later into the
fldEndTimeColle ctedID. 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
fldStartTimeCol lectedID and fldEndTimeColle ctedID 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
tblTSRuleApplie d table that prevents the same value being entered into
the fldStartTimeCol lectedID and fldEndTimeColle ctedID 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
fldStartTimeCol lectedID and the fldEndTimeColle ctedID 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].[FK_tblTSRuleApp lied_tblTSColle cted]') and
OBJECTPROPERTY( id, N'IsForeignKey' ) = 1)
ALTER TABLE [dbo].[tblTSRuleApplie d] DROP CONSTRAINT
FK_tblTSRuleApp lied_tblTSColle cted
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblTSRuleApp lied_tblTSColle cted1]') and
OBJECTPROPERTY( id, N'IsForeignKey' ) = 1)
ALTER TABLE [dbo].[tblTSRuleApplie d] DROP CONSTRAINT
FK_tblTSRuleApp lied_tblTSColle cted1
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblTSArbAcce pt_tblTSRuleApp lied]') and
OBJECTPROPERTY( id, N'IsForeignKey' ) = 1)
ALTER TABLE [dbo].[tblTSArbAccept] DROP CONSTRAINT
FK_tblTSArbAcce pt_tblTSRuleApp lied
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblTSCollect ed_tblTSRuleApp lied]') and
OBJECTPROPERTY( id, N'IsForeignKey' ) = 1)
ALTER TABLE [dbo].[tblTSCollected] DROP CONSTRAINT
FK_tblTSCollect ed_tblTSRuleApp lied
GO

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

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblTSRuleApplie d]') and OBJECTPROPERTY( id,
N'IsUserTable') = 1)
drop table [dbo].[tblTSRuleApplie d]
GO

CREATE TABLE [dbo].[tblTSCollected] (
[fldCollectedID] [int] IDENTITY (1, 1) NOT NULL ,
[fldEmployeeID] [int] NULL ,
[fldLocationCode] [varchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NULL ,
[fldTimeStamp] [datetime] NULL ,
[fldRuleAppliedI D] [int] NULL ,
[fldBarCode] [varchar] (50) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL
,
[fldProcessed] [smallint] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblTSRuleApplie d] (
[fldEmpRuleID] [int] NOT NULL ,
[fldRuleAppliedI D] [int] IDENTITY (1, 1) NOT NULL ,
[fldStartTime] [datetime] NULL ,
[fldEndTime] [datetime] NULL ,
[fldStartTimeCol lectedID] [int] NULL ,
[fldEndTimeColle ctedID] [int] NULL ,
[fldStartArbStat us] [smallint] NULL ,
[fldEndArbStatus] [smallint] NULL ,
[fldDurationArbS tatus] [smallint] NULL ,
[fldPrimary] [smallint] NOT NULL ,
[fldDateEntered] [datetime] NULL ,
[fldEnteredBy] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblTSCollected] WITH NOCHECK ADD
CONSTRAINT [DF_tblTSCollect ed_fldProcessed] DEFAULT (0) FOR
[fldProcessed],
CONSTRAINT [PK_tblTimesheet Collected] PRIMARY KEY CLUSTERED
(
[fldCollectedID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblTSRuleApplie d] WITH NOCHECK ADD
CONSTRAINT [DF_tblTSRuleApp lied_fldPrimary] DEFAULT (1) FOR
[fldPrimary],
CONSTRAINT [PK_tblTSRuleApp lied] PRIMARY KEY CLUSTERED
(
[fldRuleAppliedI D]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [IX_tblTSRuleApp lied_1] UNIQUE NONCLUSTERED
(
[fldStartTimeCol lectedID],
[fldEndTimeColle ctedID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblTSCollected] ADD
CONSTRAINT [FK_tblTSCollect ed_tblEmployee1] FOREIGN KEY
(
[fldEmployeeID]
) REFERENCES [dbo].[tblEmployee] (
[fldEmployeeID]
),
CONSTRAINT [FK_tblTSCollect ed_tblLocation] FOREIGN KEY
(
[fldLocationCode]
) REFERENCES [dbo].[tblLocation] (
[fldLocationCode]
),
CONSTRAINT [FK_tblTSCollect ed_tblTSRuleApp lied] FOREIGN KEY
(
[fldRuleAppliedI D]
) REFERENCES [dbo].[tblTSRuleApplie d] (
[fldRuleAppliedI D]
)
GO

ALTER TABLE [dbo].[tblTSRuleApplie d] ADD
CONSTRAINT [FK_tblTSRuleApp lied_tblTSColle cted] FOREIGN KEY
(
[fldStartTimeCol lectedID]
) REFERENCES [dbo].[tblTSCollected] (
[fldCollectedID]
),
CONSTRAINT [FK_tblTSRuleApp lied_tblTSColle cted1] FOREIGN KEY
(
[fldEndTimeColle ctedID]
) REFERENCES [dbo].[tblTSCollected] (
[fldCollectedID]
),
CONSTRAINT [FK_tblTSRuleApp lied_tblTSDurat ionStatus] FOREIGN KEY
(
[fldDurationArbS tatus]
) REFERENCES [dbo].[tblTSDurationSt atus] (
[fldStatus]
),
CONSTRAINT [FK_tblTSRuleApp lied_tblTSEmpRu les] FOREIGN KEY
(
[fldEmpRuleID]
) REFERENCES [dbo].[tblTSEmpRules] (
[fldEmpRuleID]
),
CONSTRAINT [FK_tblTSRuleApp lied_tblTSTimeS tatus] FOREIGN KEY
(
[fldStartArbStat us]
) REFERENCES [dbo].[tblTSTimeStatus] (
[fldStatus]
),
CONSTRAINT [FK_tblTSRuleApp lied_tblTSTimeS tatus1] FOREIGN KEY
(
[fldEndArbStatus]
) REFERENCES [dbo].[tblTSTimeStatus] (
[fldStatus]
)
GO

[teddysnips]

Dan Guzman wrote:

So, is there any way of retaining the requirement that the
fldStartTimeCol lectedID and the fldEndTimeColle ctedID 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:

[snip]

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

I *love* usenet.

Edward

[Doug]

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?

[Hugo Kornelis]

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)

[Doug]

Hmmmm.......

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

[Hugo Kornelis]

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)

[Dan Guzman]

> 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" <dr*********@ho tmail.com> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.com.. . Hmmmm.......

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