August 12, 2010 at 9:51 pm
Any thoughts out there about preventing dups? Well, not really dups. What I mean is every column is the same except the primary key. Seems like there ought to be an easy way to prevent these. I have a middle tier app that accepts data from very careless clients. If the only thing unique about an incoming row is the pkey, seems like a problem. A unique compound index on everything BUT the pkey seems laughable. Maybe not?
Here's some code:
declare @customer table (
CustId uniqueidentifier primary key,
LName varchar(30),
FName varchar(20),
Age int,
City varchar(128)
)
insert @Customer(CustId, LName, FName, Age, City)
values (NEWID(), 'Smith', 'Bob', 40, 'Phoenix')
insert @Customer(CustId, LName, FName, Age, City)
values (NEWID(), 'Jomes', 'Dan', 50, 'Phoenix')
insert @Customer(CustId, LName, FName, Age, City)
values (NEWID(), 'Black', 'Ron', 40, 'Phoenix')
insert @Customer(CustId, LName, FName, Age, City)
values (NEWID(), 'White', 'Betty', 40, 'Phoenix')
insert @Customer(CustId, LName, FName, Age, City)
values (NEWID(), 'Green', 'Joe', 40, 'Phoenix')
--* Everything good so far
--* Now, client trying to trick me into accepting
--* Bob Smith again, but with a different guid.
--* Sadly, I fall for it
insert @Customer(CustId, LName, FName, Age, City)
values (NEWID(), 'Smith', 'Bob', 40, 'Phoenix')
select * from @Customer
.
August 12, 2010 at 11:02 pm
This is the problem with this type of Primary Key.
I would say yes, you should create an index (or unique constraint) on the data that you expect to be unique. Unfortunately people are remarkable non-unique from the perspective of their names etc., so I'd be cautious with this one
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply