Trigger or Constraint...you decide!
-
Ok, I have the proverbial "Users" table. I am using a UniqueIdentifier as the Primary Key, however, I need to also have the "UserName" Field be Unique as well.
The problem is that I need only UserNames that are not NULL to be Unique. If the Field Value Is Null, I do not care. When I try to put a Constraint on that Field, the Constraint does NOT Ignore NULLS. Is this possible? Am I just not seeing something?
If it's not possible with a Constraint, then how can I create a Trigger that runs on UPDATE and INSERT and validates the UserName before Saving the Record??
-
-
Thanks! That did the trick!
-
tymberwyld;
1) Reconsider your table design -- why would you allow NULL UserNames? What does that mean?
2) Here's another (IMO, more elegant) method that doesn't rely on procedural (trigger) logic. Creat a VIEW on the table with a unique index:
CREATE TABLE dbo.NoDupesExceptNull(
UniqueID int NOT NULL PRIMARY KEY CLUSTERED,
UserName varchar(35) NULL,
)
GO
CREATE VIEW dbo.vConstrainNoDupesExceptNull
WITH SCHEMABINDING
AS
SELECT UserName
FROM dbo.NoDupesExceptNull
WHERE UserName IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX udx_vConstrainNoDupesExceptNull
ON dbo.vConstrainNoDupesExceptNull(UserName)
GO
INSERT INTO dbo.NoDupesExceptNull
SELECT 1, 'TroyK' UNION ALL
SELECT 2, NULL UNION ALL
SELECT 3, NULL
GO
INSERT INTO dbo.NoDupesExceptNull
SELECT 4, 'TroyK'
GO
SELECT * FROM dbo.NoDupesExceptNull
GO
DROP VIEW dbo.vConstrainNoDupesExceptNull
DROP TABLE dbo.NoDupesExceptNull
HTH,
TroyK
-
Hmm, well, I lied, it's not really a "Users" table. It's more of an "Individuals" Table which stores Users and Contacts of Companies in the same table. The reason is complicated, but even a "Contact" record could eventually have a User name when they have "Signed-up" for access into our in-house application. I did not want a seperate Table for "Users" vs. "Contacts" because it was a lot of redundant data!
Thank you for your suggestion, but I try to get around using Temporary Tables. If there is something I can't do with the data I have (without creating a Temp Table) then I am designing the DB incorrectly (In my humble opinion
). Can you imagine creating a Temp table everytime a Record changes in that table? That's a lot of unneccessary processing.Anyway, it works great with the Trigger, and I have even returned an Error Message stating why it failed...which will return the error to the VB.NET App as well.
CREATE TRIGGER dbo.trg_OnUpdateIndividual ON [Individuals]
FOR INSERT, UPDATE
AS
BEGIN
If (Select Max(Cnt) From (Select Count(inserted.UserName) As Cnt
From Inserted
Inner Join dbo.Individuals ON Inserted.UserName = dbo.Individuals.UserName
Group By Inserted.UserName) x) > 1
BEGIN
RaisError('The UserName must be Unique!', 16, 1)
ROLLBACK TRAN
END
END
-
The VIEW is intended to be permanent... not something you create/delete each time you enforce the constraint.
My script is ambiguous in this respect because I left the cleanup code (DROP VIEW... and DROP TABLE...).
In my example, the table "NoDupesExceptNull" is analagous to your "Individuals" table, and the VIEW is a permanent reference to that table.
Using a TRIGGER to enforce db integrity should be a last resort, and as shown in my example, is not necessary in this case. The error message from attempting a duplicate insert can be quite informative if you use a meaningful name for the index on the VIEW.
TroyK
-
P.S.
Some of your statements about your table indicates that it is not properly designed (i.e., normalized). I'd be happy to work through that with you if you can post more detail on the business rules and your current table design.
TroyK
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply