March 7, 2002 at 1:35 am
Hi,
I'm trying to enforce uniqueness on a column only when a flag in the table is set to 1. ie I have a table of users and I want all users where the ActiveFlag is set to 1 to be unique. This code here isn’t doing it for me. See the code below:
--create a test table
create table dbuser (
rid int not null primary key identity (1,1),
ntlogin varchar (10) not null,
forename varchar(30) not null,
surname varchar(30) not null,
email varchar(50) not null,
active bit not null)
go
-- create the funky function
create function dbo.checkemailunq
(
@email varchar(50)
)
RETURNS bit
AS
BEGIN
declare @unique bit
if exists (select rid from dbuser where email = @email and active = 1)
set @unique = 0
else
set @unique = 1
return @unique
end
--test the function works OK
declare @result bit
select @result = dbo.checkemailunq('mark.allison@email.com')
select @result
--bind the function to a check constraint
alter table dbuser --with nocheck
add constraint checkemail check
(dbo.checkemailunq(email) = 1)
--try and do an insert and the thing doesn’t work
INSERT INTO [dbo].[dbuser]([ntlogin], [forename], [surname], , [active])
VALUES('071328','mark', 'allison','mark@allisonmitchell.com', 1)
Any ideas?
Mark.
March 7, 2002 at 6:49 am
You are 100% sure that 'mark@allisonmitchell.com' is already in the database and active is 1 for it. Check you data to be sure, otherwise so far I cannot see a problem and will test when I am near a 2K server.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 7, 2002 at 10:18 am
There is no data in the table. When I insert the first row, the constraint gets violated. Not sure why this is happening. The function works when tested in isolation, but stops working when bound to the check constraint.
March 7, 2002 at 10:55 am
Doesn't appear to like functions in the constraint. Either ignored or always fails.
Tried some variations:
1. Reverse logic
2. Change to char() and return the email in the function, check that the function is null
Steve Jones
March 7, 2002 at 12:31 pm
Alter the function to return 1 either way to see what happens. If still fails then remove the input and return 1 only. Backtrack thru it until it works to see if you can pinpoint the problem. Also what is the error or is it just that it was violated. Also try it this way
if exists (select rid from dbuser where email = @email and active = 1)
BEGIN
set @unique = 0
END
else
BEGIN
set @unique = 1return @unique
END
Just to be sure it parses as you think it is.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 7, 2002 at 8:18 pm
I didnt think functions worked in constraints either, though I haven't spent much time trying to get it work yet!
Andy
March 7, 2002 at 8:37 pm
I am going to double check tomorrow and in Books On Line there was a statement they couldn't and a statement on how to use them (which keymoo's looks right) and this was confirmed when I downloaded the white paper on UDF.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 8, 2002 at 9:34 am
I did some checking because this intrigued me. It appears that UDFs are valid in check constraints, but it doesn't want to work. Tried a bunch of variations, even returning the email when it didn't exist in the function and aspace otherwise and using a check( email = edf()). Nothing appears to work.
My suggestion is to use a trigger to enforce this.
Steve Jones
March 8, 2002 at 10:14 am
Ok I tested until I was blue in the face but here is what you need to do and why.
create function dbo.checkemailunq(
@email varchar(50))
RETURNS bit
AS
BEGIN
declare @unique bit
if (select COUNT(rid) from dbuser where email = @email and active = 1) > 1
set @unique = 0
else
set @unique = 1
return @unique
end
You notice all I did was change if from an exists to a select count > 1. Constraints have to do dirty reads of the data to ensure that multiple users are albe to insert data and not cause a conflict, thus when it looks at yours it sees you inserted value as well as those that exist and thus EXISTS will return true and thus you get blocked. However we know for a record to be unique it mut exist no more than once. By do a SELECT COUNT(*) FROM WHATEVER IF > 1 we know that when our record goes in it will create a count larger than one. Anyway try it out and let me know if you get it to work.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 8, 2002 at 11:29 am
March 10, 2002 at 5:53 am
Thanks for that I will test it out. It is certainly behaviour that I didn't expect. UDFs open up a whole world of possibilities when used with CHECK constraints.
Keymoo.
March 10, 2002 at 7:32 am
Yeah, really trying to get my department to move to SQL 2000 on all our servers as I can already point out about 10 improvements that would make. Especiialy with UDFs.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply