-----------------------------------------------------------
New Message on BDOTNET
-----------------------------------------------------------
From: VinodK [TLabs, SCT]
Message 3 in Discussion
Well, There is always a confusion of using NULL in SQL Server ... And it is to be
understood that NULLs is also NOT a value, it is something like UNDEFINED. And to
elaborate in context with your question, here is an example ... Assume I've a table
with a unique column(a,b) with b as unique. When I insert (1,1) it would get through
... Later I insert (2,NULL), when this happens SQL Server identifies that we have an
UNDEFINED value and hence inserts it. Later we try to insert (3,NULL) now SQL Server
has a problem we have two UNDEFINED values and there is a probability of both these
values becoming same some point in time (from update) ... And SQL Server is not sure,
hence it takes the safest possibility of not allowing the same ... Understand that
this is by design ... And to add to NetPointers comments. Unique key allows NULL but
when two columns are having NULL is not allowed. And to test the same use the script
below to verify: Create table vin1 (id int)
Go
ALTER TABLE vin1 ADD emailID VARCHAR(20) NULL
CONSTRAINT unique_name UNIQUE
GO
Insert into vin1 (id, emailID) values (1,NULL)
GO
Insert into vin1 (id, emailID) values (2,NULL)
GO Basically the difference between primary key and unique key are : 1. Primary key
is a NOT NULL Unique key 2. Unique key checks for Uniqueness only. Now the
workaround for the problem in hand. You can use a trigger that can do this check
during the insert operation ... Here is an example to illustrate the same : Drop
table vin1
Go
Create table vin1 (id int, emailID Varchar(30))
Go
CREATE TRIGGER CheckEmail ON vin1
INSTEAD OF INSERT
AS
BEGIN
Declare @InsertEmail Varchar(30)
Select @InsertEmail=emailID from Inserted
Set @InsertEmail = ISNULL(@InsertEmail,'')
If NOT Exists (Select 1 from vin1 Where Upper(emailID) like Upper(@Insertemail) and
emailID IS NOT NULL)
Insert into vin1 Select * from Inserted
Else
Begin
RaisError('Cannot Insert', 16,10)
Rollback Tran
End
END
GO
Insert into vin1 (id, emailID) values (1,NULL)
GO
Insert into vin1 (id, emailID) values (2,NULL)
GO
Select * from vin1
GO
Insert into vin1 (id, emailID) values (3,'abc.com')
GO
Insert into vin1 (id, emailID) values (4,'abc.COM')
GO
Select * from vin1
GO
I hope the concepts and the usage are clear now ... HTH, Vinod Kumar Microsoft
India Community Star www.ExtremeExperts.com
-----------------------------------------------------------
To stop getting this e-mail, or change how often it arrives, go to your E-mail
Settings.
http://groups.msn.com/BDotNet/_emailsettings.msnw
Need help? If you've forgotten your password, please go to Passport Member Services.
http://groups.msn.com/_passportredir.msnw?ppmprop=help
For other questions or feedback, go to our Contact Us page.
http://groups.msn.com/contact
If you do not want to receive future e-mail from this MSN group, or if you received
this message by mistake, please click the "Remove" link below. On the pre-addressed
e-mail message that opens, simply click "Send". Your e-mail address will be deleted
from this group's mailing list.
mailto:[EMAIL PROTECTED]
