Very Computer

Thanks, Amos

Adi

Quote:> I have a question regarding a unique constraint:
> Is there a way to create such a constraint so that it allows duplicate
null
> values (i.e., it only rejects non-null values inserted to the table). SQL
> Server books online describes such constraints's behavior as "column or
> combination of columns comprising the UNIQUE constraint contains only
unique
> or NULL values", but if I put 2 Null values the constraint rejects the
> second one. Any explanation?

> Thanks, Amos

create view dbo.MyView
as
select
    col1
,   col2
,   col3
from
    dbo.MyView
where
    col1 is not null
go

create unique clustered index on dbo.MyView (col1)
go

--
   Tom

----------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCT
SQL Server MVP
Columnist, SQL Server Professional

Toronto, ON Canada
www.pinnaclepublishing.com/sql
www.apress.com
---
I have a question regarding a unique constraint:
Is there a way to create such a constraint so that it allows duplicate null
values (i.e., it only rejects non-null values inserted to the table). SQL
Server books online describes such constraints's behavior as "column or
combination of columns comprising the UNIQUE constraint contains only unique
or NULL values", but if I put 2 Null values the constraint rejects the
second one. Any explanation?

Thanks, Amos

Thanks for the help
  Amos

Amos

Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy  Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

Quote:> create unique clustered index on dbo.MyView (col1)

Amos

Can anyone let me know this looks like an appropriate way to write this
trigger, or if I can do things more efficiently? My (simplified) table,
index, and trigger definitions are as follows:

==============
CREATE TABLE dbo.Customers
 (
 CustID int NOT NULL IDENTITY (1, 1),
 Name varchar(50) NULL,
 Address varchar(50) NULL,
 City varchar(50) NULL,
 State varchar(2) NULL,
 Zipcode varchar(9) NULL,
 Phone varchar(20) NULL,
 )  ON [PRIMARY]
GO

ALTER TABLE dbo.Customers ADD CONSTRAINT
 PK_Customers PRIMARY KEY CLUSTERED
 (
 CustID
 ) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX IX_Customers_Phone ON dbo.Customers
 (
 Phone
 ) ON [PRIMARY]
GO

CREATE TRIGGER tCustomerUniquePhone ON dbo.Customers
FOR INSERT, UPDATE
AS

-- Only if Phone number was changed
IF NOT UPDATE(Phone)
  RETURN

-- Don't allow duplicates
IF EXISTS (SELECT *
           FROM inserted a
    INNER JOIN Customers b ON a.Phone = b.Phone
    WHERE a.Phone IS NOT NULL AND a.Phone IS NOT NULL AND
a.CustID<>b.CustID)
BEGIN
  RAISERROR ('Duplicate Phone numbers are not allowed.', 16, 1)
  ROLLBACK TRANSACTION
END
GO
==============

Thanks for the help,

--
Greg

--
-oj
Rac v2.1 coming soon
http://www.rac4sql.net

Quote:> I've just tried writing my first trigger that would enforce non-null
> duplicates.  This is my first attempt at it.  It seems to work (I also
> noticed that Query Analyzer allowed the transaction if I didn't include
> the Rollback).

> Can anyone let me know this looks like an appropriate way to write this
> trigger, or if I can do things more efficiently? My (simplified) table,
> index, and trigger definitions are as follows:

> ==============
> CREATE TABLE dbo.Customers
>  (
>  CustID int NOT NULL IDENTITY (1, 1),
>  Name varchar(50) NULL,
>  Address varchar(50) NULL,
>  City varchar(50) NULL,
>  State varchar(2) NULL,
>  Zipcode varchar(9) NULL,
>  Phone varchar(20) NULL,
>  )  ON [PRIMARY]
> GO

> ALTER TABLE dbo.Customers ADD CONSTRAINT
>  PK_Customers PRIMARY KEY CLUSTERED
>  (
>  CustID
>  ) ON [PRIMARY]
> GO

> CREATE NONCLUSTERED INDEX IX_Customers_Phone ON dbo.Customers
>  (
>  Phone
>  ) ON [PRIMARY]
> GO

> CREATE TRIGGER tCustomerUniquePhone ON dbo.Customers
> FOR INSERT, UPDATE
> AS

> -- Only if Phone number was changed
> IF NOT UPDATE(Phone)
>   RETURN

> -- Don't allow duplicates
> IF EXISTS (SELECT *
>            FROM inserted a
>     INNER JOIN Customers b ON a.Phone = b.Phone
>     WHERE a.Phone IS NOT NULL AND a.Phone IS NOT NULL AND
> a.CustID<>b.CustID)
> BEGIN
>   RAISERROR ('Duplicate Phone numbers are not allowed.', 16, 1)
>   ROLLBACK TRANSACTION
> END
> GO
> ==============

> Thanks for the help,

> --
> Greg

  It looks to me like this will work.  I don't think you 't need to check against
null unless you have ANSI NULLS off, since a.Phone = b.Phone
will not be true if either side is NULL, and I think you can leave off the
check that a.CustID <> b.CustID, unless something is funny with the
identity column.

If you are using SQL Server 2000, however, you can enforce non-null
duplicates without a trigger (see below).

You can test to see which solution turns out to be more efficient -
either might be helped by an index to speed up the checking.

Here's how to do it (Both Ivan Arjentinski and I posted this several
months ago):

CREATE TABLE dbo.Customers
 (
 CustID int NOT NULL IDENTITY (1, 1),
 Name varchar(50) NULL,
 Address varchar(50) NULL,
 City varchar(50) NULL,
 State varchar(2) NULL,
 Zipcode varchar(9) NULL,
 Phone varchar(20) NULL,
 NoDuplicates as CASE WHEN Phone IS NULL
    THEN CONVERT(varchar(21),CustID) ELSE '*'+Phone END,
 CONSTRAINT Customers_NoDupPhone UNIQUE (NoDuplicates)
 )  ON [PRIMARY]
GO

The computed column NoDuplicates is enforced to be unique, and
since it contains the phone number whenever the phone number is not
null, there can be no duplicate phone numbers.  When the phone
number is null, it contains the primary key, which is unique.

There is the chance that a primary key and a phone number will match,
so in this case I store the phone number with a non-numeric character
on the left, so it can't match a CustID.

I'm virtually certain that you can't put a unique constraint on a computed
column in 7.0, since unique constraints are implemented with indexes,
and indexes on computed columns are new to SQL Server 2000.

Yet another solution is to create a view consisting of all the customers
with non-null phone numbers and enforce the uniqueness there.

Steve Kass
Drew University

 CREATE  UNIQUE  CLUSTERED  INDEX [IX_vCustomerUniquePhone] ON
[dbo].[vCustomerUniquePhone]([Phone]) ON [PRIMARY]
GO

Also, I should mention that I created a view that only has one column in
it - 'Phone' which is the one that I want to have unique values.  It
seems that is the only one that I need, since I'm only using the view to
enforce the rule (if I'm understanding things right here).

One thing that I noticed, are that the error message you get in Query
Analyzer is pretty misleading: 'UPDATE failed because the following SET
options have incorrect settings: 'ARITHABORT'.'  Enterprise Manager,
OTOH, says 'cannot insert a duplicate key row...'

Now the decision is whether using this technique is the best way to
achieve what we want.  It's certainly easier to define, but does it
perform better?

--
Greg

Quote:

> > create unique clustered index on dbo.MyView (col1)

> Well, this line fails, saying "incorrect syntax" next to both the
words "on"
> and "create".

> Amos

-- Greg

Quote:> the trigger would work, but it would be better to put an unique
> constraint/index on phone column.

> --
> -oj
> Rac v2.1 coming soon
> http://www.rac4sql.net

if you're on sql2k, put an unique constraint on a calc column would work
(see steve's post). else, trigger is the only way to allow multi nulls and
unique non-nulls.

--
-oj
Rac v2.1 coming soon
http://www.rac4sql.net

Quote:> Thanks oj:  I wasn't very clear - I want to allow nulls, but I need to
> enforce uniqueness for those values that aren't null - it seems to be a
> popular need & it's surprising that MS doesn't support it in a more
> straightforward fashion.

> -- Greg

Great feedback - thanks.  Since the unique index treats multiple NULL's
as the same, I thought I would have to check for that condition in my
query as well.  Gee, it sure would be nice if it behaved consistently.
<g>  Of course if it did, we wouldn't need to do all of this workaround!

I did a test without the 'a.CustID<>b.CustID', and it fired EVERY time!
If I understand things right, it kind of makes sense: the trigger
happens after the table has been updated, so the inserted/updated record
already exists with it's new value; including that clause checks to see
if any OTHER records have the same phone number.

Thanks for reposting the solution.  I tried to do a google groups search
on this before I wrote this.  I know it's a pain to keep re-answering
the same questions & I really appreciate you taking the time.  Your
technique is a very cool way of tackling the problem!

I did go ahead and figure out how to create a view of Customers with
non-null phone numbers & that does seem to work for me (I posted another
message in another sub-thread of this topic).  It is quite easy to
implement, though it does give a misleading error message in Query
Analyzer when it fires.  I'll have to see about performing some
benchmarks with my tables to see how well each solution performs.

Thanks again for your help,

--
Greg

Quote:> Greg,

>   It looks to me like this will work.  I don't think you 't need to
check against
> null unless you have ANSI NULLS off, since a.Phone = b.Phone
> will not be true if either side is NULL, and I think you can leave off
the
> check that a.CustID <> b.CustID, unless something is funny with the
> identity column.

> If you are using SQL Server 2000, however, you can enforce non-null
> duplicates without a trigger (see below).

> You can test to see which solution turns out to be more efficient -
> either might be helped by an index to speed up the checking.

> Here's how to do it (Both Ivan Arjentinski and I posted this several
> months ago):

> CREATE TABLE dbo.Customers
>  (
>  CustID int NOT NULL IDENTITY (1, 1),
>  Name varchar(50) NULL,
>  Address varchar(50) NULL,
>  City varchar(50) NULL,
>  State varchar(2) NULL,
>  Zipcode varchar(9) NULL,
>  Phone varchar(20) NULL,
>  NoDuplicates as CASE WHEN Phone IS NULL
>     THEN CONVERT(varchar(21),CustID) ELSE '*'+Phone END,
>  CONSTRAINT Customers_NoDupPhone UNIQUE (NoDuplicates)
>  )  ON [PRIMARY]
> GO

> The computed column NoDuplicates is enforced to be unique, and
> since it contains the phone number whenever the phone number is not
> null, there can be no duplicate phone numbers.  When the phone
> number is null, it contains the primary key, which is unique.

> There is the chance that a primary key and a phone number will match,
> so in this case I store the phone number with a non-numeric character
> on the left, so it can't match a CustID.

> I'm virtually certain that you can't put a unique constraint on a
computed
> column in 7.0, since unique constraints are implemented with indexes,
> and indexes on computed columns are new to SQL Server 2000.

> Yet another solution is to create a view consisting of all the
customers
> with non-null phone numbers and enforce the uniqueness there.

> Steve Kass
> Drew University