August 2, 2002 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/sortofunique.asp
August 12, 2002 at 3:55 am
Never thought of solving this problem using only a single table.
Typically, I opt for adding a 'history' table in which the non-active records get archived. But this becomes a hassle when you have to enforce RI on other tables.
August 12, 2002 at 12:41 pm
Hmm...I wouldn't reuse those IDs, as it will become impossible to track which SJones has created what, at a later date 🙂 There could be a better example, though I can't think of one at the moment 🙂
Further, the trigger and UDF code can not handle multi row INSERTs. For example, the following INSERT will fail, though it is trying to insert valid rows:
INSERT MyLogin
SELECT 'b', 1
UNION ALL
SELECT 'c', 1
GO
HTH,
Vyas
HTH,
Vyas
SQL Server MVP
http://vyaskn.tripod.com/
August 13, 2002 at 8:52 am
It was a problem presented to me and it was in a narrow scope. Thanks for pointing out those bugs, I should have included those limitations in the article so people were aware of the limitations.
As far as reusing the IDs, it may be a requirement for people. Having some auditing dates (created, deactivated, etc.) would alleviate the tracking issue. One could also shut down the other related data with inactive flags as well. In many retail environments, it would be advatageous to allow the resuse of ids.
Steve Jones
August 13, 2002 at 12:47 pm
Please do post or update the article, if there's a scenario that benefits from reusing of IDs and yet keep track of info related to things done by old owners of those IDs. It would be helpful to know.
As far as the trigger code is concerned, may be you could write something like this to overcome the bug (untested code :-)):
CREATE TRIGGER MyLogin_Insert
ON MyLogin
FOR INSERT
AS
BEGIN
IF EXISTS
(
SELECT m.email, COUNT(*)
FROM mylogin m
JOIN
inserted n
ONm.email = n.email
AND m.active = n.active
AND n.active = 1
GROUP BY m.email HAVING COUNT(*) > 1
)
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Duplicate Email', 12, 1)
END
END
HTH,
Vyas
HTH,
Vyas
SQL Server MVP
http://vyaskn.tripod.com/
August 13, 2002 at 4:33 pm
While I appreciate the solution is designed to fit the scope of the problem, I can't help but feel there is a fundamental flaw behind the original approach.
For example, lets say Steve Jones leaves and Sam Jones starts soon after. However, while Sam is still employed, Steve returns - ideally he should have the same login details and the same UserID so that his history is preserved. Using the current solution, the old sjones would have to be given a different LoginID and Login Name - not an ideal solution I would suggest?
Perhaps we should have given greater consideration to a proper PK in the first place? Why not consider information about the user that we may already know (e.g. NT Domain, Location, Position, IP Address (although never ideal these days), etc).
August 14, 2002 at 10:46 am
A good point and worth considering. as with most things, it depends on the business rules.
There may be a transient environment, like an ISP for example. If I sign up and get sjones@dkranch.net for an email, I have the use of it. It's like to billing, etc.
Now I leave.
Another Sam Jones comes along and gets sjones@dkranch.net. Now there needs to be a way to d-link history. However, it I had used some other key, say email + phone to link to history, I should have separate history that doesn't appear for Sam Jones.
If Steve Jones comes back, I'll grant that finding his old history might be problematic. But the case that was presented was narrow in scope.
What if this is a simple authentication table and it has the username and other information about the user? There might not be other history in other tables.
Again, I'd admit that it may be a poor design for YOUR environment, even for many environments, but that doesn't mean the change for it's use doesn't exist. I also wanted to point out an interesting way to get at validation using a UDF.
Steve Jones
October 14, 2002 at 2:20 pm
Where you use
if (select count(*)
from MyTable
where email = @email
and active = 1
) > 1
select @allow = 0
would this be faster -----
If Exists
(
Select top 1 1 From MyTable
where email = @email
and active = 1
)
October 14, 2002 at 2:47 pm
Not sure. It might using the TOP. I avoid the exists because select count(*) tends to go after a single row in sysindexes if there is a clustered index rather than read the table.
Steve Jones
March 20, 2009 at 8:23 am
Thanks for the article, it helped me get started on a similar situation. Just to help out others that might be following the same path, I will mention that using the udf in the check constraint method did not work for updates and the trigger did not work for bulk operations. To overcome these limitations, I added an extra mechanism: Created a view that was schemabound to the table with ActiveFlag = 1, then placed a clustered unique index on the id column of the view.
Also, I have a question about:
alter table MyTable
add constraint check( dbo.uniqueemail = 0)
Should this be:
alter table MyTable
add constraint check( dbo.uniqueemail = 1)
If the variable @allow is set to 0 when the count is greater than 1? If I misunderstood I apologize.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply