Unique or null - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Unique or null

Author

Topic

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2007-06-08 : 06:45:16

I read these:
http://weblogs.sqlteam.com/davidm/archive/2004/05/21/1364.aspx
http://weblogs.sqlteam.com/mladenp/archive/2007/05/17/60209.aspx

I thought I'd try something different (do a Find-and-replace on the word "YourDB"):
How about this? No Calculated column, no view - so no arithabort pains (or any other common set option challenges for that matter). This will perform slower though:

use YourDB

print 'DDL'
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.TestAccounts
(
AccID int NOT NULL IDENTITY (1, 1),
EMailAdr varchar(255) NULL
) ON [PRIMARY]
GO
COMMIT

BEGIN TRANSACTION
GO
CREATE TABLE dbo.UniqueEmails
(
UniqueEmail varchar(255) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.UniqueEmails ADD CONSTRAINT
[Email Addresses must be unique!] PRIMARY KEY CLUSTERED
(
UniqueEmail
) WITH( PAD_INDEX = ON, FILLFACTOR = 95, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
COMMIT

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
USE YourDB
GO
--The USE statement might fail. The GO statement clears out @@error. So you have to check DB_NAME if you don't want to run the risk of creating SProcs in the wrong DB.
IF DB_NAME()<>'YourDB' BEGIN
RAISERROR('Wrong database.',16,10)
RETURN
END
GO
IF EXISTS(SELECT * FROM sysobjects WHERE name='tri_TestAccounts_Update') DROP trigger dbo.tri_TestAccounts_Update
GO
CREATE trigger tri_TestAccounts_Update on TestAccounts after update
----------------------------------------------------------------------------------------------------
-- OBJECT NAME : tri_TestAccounts_Update
--
-- AUTHOR : Coolerbob
-- DATE : 08/06/2007
--
-- INPUTS :
-- OUTPUTS :
-- DEPENDENCIES : None
--
-- CALLED FROM :
--
-- DESCRIPTION :
--
-- EXAMPLES (optional) :
--
-- DEBUG (optional) :
----------------------------------------------------------------------------------------------------
--
--
----------------------------------------------------------------------------------------------------
-- MODIFICATION HISTORY :
----------------------------------------------------------------------------------------------------
-- dd/mm/yyyy - Coolerbob
-- no changes yet
--
----------------------------------------------------------------------------------------------------
AS

insert into UniqueEmails (UniqueEmail)
select s.EMailAdr
from inserted s
where s.EMailAdr is not null

delete t
from UniqueEmails t
where exists (select * from deleted d where t.UniqueEmail=d.EMailAdr)
and not exists (select * from TestAccounts s where t.UniqueEmail=s.EMailAdr)

GO
--EXEC isp_Grant_Permissions
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
USE YourDB
GO
--The USE statement might fail. The GO statement clears out @@error. So you have to check DB_NAME if you don't want to run the risk of creating SProcs in the wrong DB.
IF DB_NAME()<>'YourDB' BEGIN
RAISERROR('Wrong database.',16,10)
RETURN
END
GO
IF EXISTS(SELECT * FROM sysobjects WHERE name='tri_TestAccounts_Delete') DROP trigger dbo.tri_TestAccounts_Delete
GO
CREATE trigger tri_TestAccounts_Delete on TestAccounts after delete
----------------------------------------------------------------------------------------------------
-- OBJECT NAME : tri_TestAccounts_Delete
--
-- AUTHOR : Coolerbob
-- DATE : 08/06/2007
--
-- INPUTS :
-- OUTPUTS :
-- DEPENDENCIES : None
--
-- CALLED FROM :
--
-- DESCRIPTION :
--
-- EXAMPLES (optional) :
--
-- DEBUG (optional) :
----------------------------------------------------------------------------------------------------
--
--
----------------------------------------------------------------------------------------------------
-- MODIFICATION HISTORY :
----------------------------------------------------------------------------------------------------
-- dd/mm/yyyy - Coolerbob
-- no changes yet
--
----------------------------------------------------------------------------------------------------
AS

delete t
from UniqueEmails t
where exists (select * from deleted d where t.UniqueEmail=d.EMailAdr)
and not exists (select * from TestAccounts s where t.UniqueEmail=s.EMailAdr)

GO
--EXEC isp_Grant_Permissions
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
USE YourDB
GO
--The USE statement might fail. The GO statement clears out @@error. So you have to check DB_NAME if you don't want to run the risk of creating SProcs in the wrong DB.
IF DB_NAME()<>'YourDB' BEGIN
RAISERROR('Wrong database.',16,10)
RETURN
END
GO
IF EXISTS(SELECT * FROM sysobjects WHERE name='tri_TestAccounts_Insert') DROP trigger dbo.tri_TestAccounts_Insert
GO
CREATE trigger tri_TestAccounts_Insert on TestAccounts after insert
----------------------------------------------------------------------------------------------------
-- OBJECT NAME : tri_TestAccounts_Insert
--
-- AUTHOR : Coolerbob
-- DATE : 08/06/2007
--
-- INPUTS :
-- OUTPUTS :
-- DEPENDENCIES : None
--
-- CALLED FROM :
--
-- DESCRIPTION :
--
-- EXAMPLES (optional) :
--
-- DEBUG (optional) :
----------------------------------------------------------------------------------------------------
--
--
----------------------------------------------------------------------------------------------------
-- MODIFICATION HISTORY :
----------------------------------------------------------------------------------------------------
-- dd/mm/yyyy - Coolerbob
-- no changes yet
--
----------------------------------------------------------------------------------------------------
AS

insert into UniqueEmails (UniqueEmail)
select s.EMailAdr
from inserted s
where s.EMailAdr is not null

GO
--EXEC isp_Grant_Permissions
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

print 'DML'

print '--Add Email Address'
insert into TestAccounts(EMailAdr)
select 'Test@ms.com'

print '--Now try a second time unsuccesfully'
insert into TestAccounts(EMailAdr)
select 'Test@ms.com'

print '--Remove it'
delete from TestAccounts
where EMailAdr='Test@ms.com'

print '--Add it again'
insert into TestAccounts(EMailAdr)
select 'Test@ms.com'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-08 : 10:08:36

You should have posted this in Script Library Forum