Preventing Duplicate Rows on Insert

[Elroyskimms]

I have a table using an identity column as its Primary Key and two
columns (table reduced for simplicity) EmployeeNumber and ArrivalTime.

CREATE TABLE [tblRecords] (
[ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[EmployeeNumber] [varchar] (10) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NOT NULL ,
[ArrivalTime] [datetime] NOT NULL ,
CONSTRAINT [PK_tblRecords] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

I have an insert procedure that checks for duplicates before inserting
a new record:

IF (SELECT TOP 1 [ID] FROM tblRecords WHERE EmployeeNumber =
@SocialSecurity ) IS NULL
BEGIN
INSERT INTO tblRecords(Empl oyeeNumber,Arri valTime)
VALUES (@EmployeeNumbe r, @ArrivalTime)
SELECT SCOPE_IDENTITY( )
END
ELSE
SELECT 0 AS DuplicateRecord

In 99.9% of the cases, this works well. However, in the event that the
insert attempts are literally "ticks" apart, the "SELECT TOP 1..."
command completes on both attempts before the first attempt completes.
So I end up with duplicate entries if the procedure is called multiple
times vey quickly. The system needs to prevent duplicate
EmployeeNumbers within the past 45 days so setting the EmployeeNumber
to UNIQUE would not work. I can check for older entries (45 days or
newer) very easily, but I do not know how to handle the times when the
procedure is called multiple times within milliseconds. Would a
TRANSACTION with a duplicate check after the INSERT with a ROLLBACK
work in this case? Any help is greatly appreciated!

-E

[David Portas]

Try this:

INSERT INTO tblRecords (employeenumber ,arrivaltime)
SELECT @employeenumber , @arrivaltime
WHERE NOT EXISTS
(SELECT *
FROM tblRecords
WHERE employeenumber = @employeenumber
AND arrivaltime >= DATEADD(DAY,-45,@arrivaltime )) ;

As posted your table design is flawed because it has no alternate keys.
IDENTITY should never be the only key of a table.

--
David Portas
SQL Server MVP
--

[David Portas]

A better solution is probably to use a trigger.

First, add the all-important UNIQUE constraint:

ALTER TABLE tblRecords ADD CONSTRAINT ak_employees UNIQUE
(employeenumber ,arrivaltime)

Now here's a trigger which will enforce your business rule for UPDATEs as
well as INSERTs:

CREATE TRIGGER trg_employee ON tblRecords FOR UPDATE, INSERT
AS
IF EXISTS
(SELECT T1.employeenumb er
FROM inserted AS T1
JOIN tblrecords AS T2
ON T1.employeenumb er = T2.employeenumb er
AND T1.arrivaltime <> T2.arrivaltime
AND T2.arrivaltime <= DATEADD(DAY,45, T1.arrivaltime)
AND T2.arrivaltime >= DATEADD(DAY,-45,T1.arrivalti me))
BEGIN
ROLLBACK TRAN
RAISERROR('Empl oyee number repeated within 45 days',16,1)
END

GO

--
David Portas
SQL Server MVP
--

[Erland Sommarskog]

David Portas (RE************ *************** *@acm.org) writes:

Try this:

INSERT INTO tblRecords (employeenumber ,arrivaltime)
SELECT @employeenumber , @arrivaltime
WHERE NOT EXISTS
(SELECT *
FROM tblRecords
WHERE employeenumber = @employeenumber
AND arrivaltime >= DATEADD(DAY,-45,@arrivaltime )) ;

You would need serializable isolation level here. I think the UPDLOCK
in the SELECT statement is the best. (With plain HOLDLOCK you could
get a deadlock.)

As for whether a trigger is best... You don't have to lose sleep over
concurrent inserts, but in case of an error you do more work (INSERT +
ROLLBACK). Whether an error message is desired we don't know. Then again,
you can have both. The trigger to enforce the business rule, and the
application code to give the nice behaviour.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Gert-Jan Strik]

As mentioned by Erland, you could place your batch in a transaction, and
serialize the action either by settings the TRANSACTION ISOLATION LEVEL
to SERIALIZABLE or to use locking hints, as shown below:
BEGIN TRANSACTION

If NOT EXISTS (
SELECT *
FROM tblRecords (UPDLOCK HOLDLOCK)
WHERE EmployeeNumber = @SocialSecurity
AND ArrivalTime >= DATEADD(day,-45,@ArrivalTime )
)
Begin
INSERT INTO tblRecords(Empl oyeeNumber,Arri valTime)
VALUES (@EmployeeNumbe r, @ArrivalTime)
SELECT SCOPE_IDENTITY( )
End
Else
SELECT 0 AS DuplicateRecord

COMMIT TRANSACTION
HTH,
Gert-Jan
Elroyskimms wrote:

I have a table using an identity column as its Primary Key and two
columns (table reduced for simplicity) EmployeeNumber and ArrivalTime.

CREATE TABLE [tblRecords] (
[ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[EmployeeNumber] [varchar] (10) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS
NOT NULL ,
[ArrivalTime] [datetime] NOT NULL ,
CONSTRAINT [PK_tblRecords] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

I have an insert procedure that checks for duplicates before inserting
a new record:

IF (SELECT TOP 1 [ID] FROM tblRecords WHERE EmployeeNumber =
@SocialSecurity ) IS NULL
BEGIN
INSERT INTO tblRecords(Empl oyeeNumber,Arri valTime)
VALUES (@EmployeeNumbe r, @ArrivalTime)
SELECT SCOPE_IDENTITY( )
END
ELSE
SELECT 0 AS DuplicateRecord

In 99.9% of the cases, this works well. However, in the event that the
insert attempts are literally "ticks" apart, the "SELECT TOP 1..."
command completes on both attempts before the first attempt completes.
So I end up with duplicate entries if the procedure is called multiple
times vey quickly. The system needs to prevent duplicate
EmployeeNumbers within the past 45 days so setting the EmployeeNumber
to UNIQUE would not work. I can check for older entries (45 days or
newer) very easily, but I do not know how to handle the times when the
procedure is called multiple times within milliseconds. Would a
TRANSACTION with a duplicate check after the INSERT with a ROLLBACK
work in this case? Any help is greatly appreciated!

-E

[--CELKO--]

>> I have a table using an identity column [sic] Primary Key and two columns (table reduced for simplicity) EmployeeNumber and ArrivalTime. <<

Have you ever read the definition of a key??? An IDENTITY can never.
never be a PRIMARY KEY by defintion because it is not a subset of the
entities attributes. It is an exposed physical storage locator derived
from the state of the hardware and not the data model. Why did you
give it the vague, useless name "ID"?

Why did you put ":tbl-" on your table names? Read ISO-11179 for the
right way to name things. Rows are not records. Columns are not
fields. Why do you unneedlessly use proprietary datatypes and the most
procedural T-SQL you could write? Do you see destruction of portable
code and nonrelational programming as a good thing?

How do you have an arrival without a departure? Is the ISO model of
time wrong?

CREATE TABLE Events -- needs a better name
(emp_nbr CHAR (10) NOT NULL
REFERENCES Personnel (emp_nbr)
ON UPDATE CASCADE,
arrival_time DATETIME DEFAULT CURRRENT_TIMEST AMP NOT NULL,
depart_time DATETIME, -- null means current
PRIMARY KEY (emp_nbr, arrival_time));

You have missing the major points of RDBMS and nee to get some help,
more than the kludges you will get a newsgroup.

[AK]

>> An IDENTITY can never.
never be a PRIMARY KEY by defintion because it is not a subset of the
entities attributes. It is an exposed physical storage locator derived

from the state of the hardware and not the data model.
<<

with all due respect, Oracle's rowid is what are speking about -
"an exposed physical storage locator". Identity is an entirely
different beast...

[--CELKO--]

>> Oracle's rowid is what are speaking about - "an exposed physical storage locator". Identity is an entirely different beast... <<

No, it is not significantly different. Both of them are numbers that
have nothing to do with the data model or the reality of the data
model. Both are created inside the hardware used to store the data.
Both use a bit of software to move the read-write head on a disk drive
directly to a physical location.

The rowid is merely easier programming and faster than IDENTITY, which
is using the indexing code. Both are exposed physical locators, not
surrogates as per Dr. Codd. That is not a logical difference.

The IDENTITY property is a hold-over from the days when the only way we
knew to storage data involved physically contigous records, made of
physically contigous fields, with tree-structured indexing. This is
the worse way to store relational data.

[Elroyskimms]

For those of you who offered suggestions to my question, thanks for
your help. It is greatly appreciated.

Celko,
I don't know what to make of your response, but I will try and address
your concerns individually:

Have you ever read the definition of a key??? Yes, I have. Thanks for asking.
An IDENTITY can never never be a PRIMARY KEY by defintion
because it is not a subset of the entities attributes. I appreciate your concern, but you are assuming things about these
entities that are incorrect. In this case the IDENTITY _is_ a subset
of the attributes. I only displayed a simplified version of the tables
because my question was not really specific to the tables in my
database.
Why did you give it the vague, useless name "ID"? Because it is neither vague nor useless to everyone who works with the
data in these tables. Again, you are assuming that you know everything
about this data and how it is being used. I will remind you that I
posted a simplified set of tables.
Why did you put ":tbl-" on your table names? Read ISO-11179 for the
right way to name things. I prefixed my table names with tbl because there is a lot more code
that I work with besides SQL. So to keep all of my objects straight,
their names also reflect what type of object they are. So, my textboxes
are named txt_____ and my buttons are named btn___... I'm sure you get
the idea.
Rows are not records. Columns are not fields. You are right. I actually knew that, but I prefer to use lamens terms
because I hate sounding like a prick no-it-all that is out to teach
everyone something. No one likes those types of people, don't you
agree?
Why do you unneedlessly use proprietary datatypes and the most
procedural T-SQL you could write? Do you see destruction of portable
code and nonrelational programming as a good thing? I'm not sure I understand your concern here. Again, you are assuming
that by seeing these 2 sample tables you have an omniscient view of my
database. My code is portable and my data relational. I know some
people are better than me, I'm OK with that. However, I choose not to
bash them simply because they know less than me. I hate those types of
people, don't you?
How do you have an arrival without a departure? Again, you assume that because you see a couple of tables you know all
about my data. I know you are the smartest person in these groups, but
here is something you didn't know. I did not include a departure time
because each record can have one arrival time and many departure times.
I think that is called a one-to-many relationship. If I'm wrong, I'm
sure you'll point it out. Because of this "relationsh ip" between my
tables, departures are stored in a separate table. I think that is
called relational data, but I could be wrong... no one likes being
right all of the time. I hate those types of people, don't you?
CREATE TABLE Events -- needs a better name Events is a better name? What kind of vague and useless name is
"Event"? For your information, my actual table name is not Record I
just used it to irritate you. Did it work?
(emp_nbr CHAR (10) NOT NULL I prefer to spell out the table name so I would use number instead of
nbr. Sometimes, the people I work with don't read English very well and
they don't know what to do with nbr. But 'number' is in their
vocabulary.
depart_time DATETIME, -- null means current Only one departure per arrival? This doesn't look very relational to
me.
You have missing the major points of RDBMS and
nee to get some help, more than the kludges you
will get a newsgroup.

My data is relational, my code is portable, and my table names are
readable to anyone with a 3rd grade English vocabulary. I'm sorry if
that is not to your liking. I'll try and do better next time.

[David Portas]

> In this case the IDENTITY _is_ a subset

of the attributes

That's dangerous. IDENTITY is generally suitable only as an artificial
key. It should not have any business meaning, in fact it should be
completely hidden from users of the system. If you assign any business
meaning to an IDENTITY key you set yourself up for a whole lot of
potential problems to do with migrating and deploying data in a live
environment. Also, your design is fundamental flawed if you lack a
natural candidate key - this is a frequent cause of incorrect results
and invalid data in poorly designed databases.

--
David Portas
SQL Server MVP
--