tag:blogger.com,1999:blog-5334232245085456076.post776856822275533553..comments2024-01-27T03:41:15.871-08:00Comments on Dimant DataBase Solutions: UNIQUE Constraint vs NULL'sUri Dimanthttp://www.blogger.com/profile/17252545378139162559noreply@blogger.comBlogger3125tag:blogger.com,1999:blog-5334232245085456076.post-58647466714365564472010-11-21T18:58:32.329-08:002010-11-21T18:58:32.329-08:00This comment has been removed by a blog administrator.aiyahttps://www.blogger.com/profile/11926567556955437833noreply@blogger.comtag:blogger.com,1999:blog-5334232245085456076.post-50459112283857461182010-06-17T13:01:05.626-07:002010-06-17T13:01:05.626-07:00Excellent. Love the nullbuster solution.Excellent. Love the nullbuster solution.Sean Brownhttps://www.blogger.com/profile/00404048066551319931noreply@blogger.comtag:blogger.com,1999:blog-5334232245085456076.post-52753545141843810372009-03-06T08:07:00.000-08:002009-03-06T08:07:00.000-08:00Here is a Guid (surrogate key) version, just to sa...Here is a Guid (surrogate key) version, just to save (a future reader) a little time.<BR/><BR/>Yes, I know there are cons to using GUID's as primary keys.<BR/><BR/>But sometimes they make sense.<BR/><BR/><BR/>----START TSQL<BR/><BR/><BR/>CREATE TABLE GuidNullBuster (<BR/>SurrogateUUID uniqueidentifier primary key not null default newsequentialid() ,<BR/>ShouldBeUniqueOrNullUUID uniqueidentifier NULL,<BR/>NullBusterUUID as (case when ShouldBeUniqueOrNullUUID is null then SurrogateUUID else '00000000-0000-0000-0000-000000000000' end),<BR/>CONSTRAINT NullBusterContraintsForShouldBeUniqueOrNullUUID UNIQUE (ShouldBeUniqueOrNullUUID,NullBusterUUID)<BR/>)<BR/><BR/>INSERT INTO dbo.GuidNullBuster (SurrogateUUID, ShouldBeUniqueOrNullUUID) values ( 'AAAAAAAA-0000-0000-0000-000000000011' , 'BBBBBBBB-0000-0000-0000-000000000021' )<BR/>INSERT INTO dbo.GuidNullBuster (SurrogateUUID, ShouldBeUniqueOrNullUUID) values ( 'AAAAAAAA-0000-0000-0000-000000000012' , NULL )<BR/>INSERT INTO dbo.GuidNullBuster (SurrogateUUID, ShouldBeUniqueOrNullUUID) values ( 'AAAAAAAA-0000-0000-0000-000000000013' , NULL )<BR/><BR/>Select * from dbo.GuidNullBuster ORDER BY SurrogateUUID<BR/><BR/><BR/>--Should Fail<BR/>UPDATE dbo.GuidNullBuster SET ShouldBeUniqueOrNullUUID = 'BBBBBBBB-0000-0000-0000-000000000021' WHERE NullBusterUUID = 'AAAAAAAA-0000-0000-0000-000000000012'<BR/><BR/>--Should Fail<BR/>INSERT INTO dbo.GuidNullBuster (SurrogateUUID, ShouldBeUniqueOrNullUUID) values ( 'AAAAAAAA-0000-0000-0000-000000000019' , 'BBBBBBBB-0000-0000-0000-000000000021' )<BR/><BR/>--Should be Ok<BR/>UPDATE dbo.GuidNullBuster SET ShouldBeUniqueOrNullUUID = 'BBBBBBBB-0000-0000-0000-000000000031' WHERE NullBusterUUID = 'AAAAAAAA-0000-0000-0000-000000000012'<BR/><BR/>DROP TABLE dbo.GuidNullBustergranadaCoderhttps://www.blogger.com/profile/02752822942527478679noreply@blogger.com