Unique constraint with multiple NULL's - Chris

We have a table called Customers.  Some of our customers have accounts that
can be accessed over the internet.  The username to log into their account
is their e-mail address.  I therefore want to have a constraint that does
not allow duplicate e-mail addresses within our Customers table, but I want
to allow multiple NULL's because not every customer has an e-mail address
(the non internet customers).

I could control this through a trigger, but is there as better way?

Thanks

Unique constraint with multiple NULL's - Uri Dimant

Chris
See this technique posted by Steve Kass
CREATE TABLE dupNulls (
pk int identity(1,1) primary key,
X  int NULL,
nullbuster as (case when X is null then pk else 0 end),
CONSTRAINT dupNulls_uqX UNIQUE (X,nullbuster)
)

INSERT INTO dupNulls(X) VALUES (1)
INSERT INTO dupNulls(X) VALUES (NULL)
INSERT INTO dupNulls(X) VALUES (NULL)
GO
SELECT pk, X, nullbuster FROM dupNulls
UPDATE dupNulls SET X = 1 WHERE pk = 2
GO
SELECT pk, X, nullbuster FROM dupNulls
UPDATE dupNulls SET X = 2 WHERE pk = 2
SELECT pk, X, nullbuster FROM dupNulls
DROP TABLE dupNulls

Unique constraint with multiple NULL's - Chris

Hi,

Thanks for that.

Chris

Unique constraint with multiple NULL's - Plamen Ratchev

You can create a view on the table filtering on non-NULL values for the
e-mail column. Then create unique index on that column of the view. This
will enforce unique values for non-NULLs.

On SQL Server 2008 you will be able to use filtered indexes to accomplish
this.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Unique constraint with multiple NULL's - David Portas

The easiest way is just to create a new table for the customers with email
addresses.

--
David Portas

Unique constraint with multiple NULL's - David Portas

All customers would be in the Customers table. Optionally a customer would
appear in the OnlineCustomers table too. Example:

CREATE TABLE Customers
(CustomerId INT NOT NULL PRIMARY KEY,
... other columns except EmailAddress ...
);

CREATE TABLE OnlineCustomers
(CustomerId INT NOT NULL PRIMARY KEY REFERENCES Customers (CustomerId),
EmailAddress VARCHAR(128) NOT NULL UNIQUE /* unique constraint here! */);

Don't make the mistake of duplicating the same (non-key) columns in both
tables. That's the meaning of the "Principle of Orthogonal Design" (Date and
McGoveran).

--
David Portas

Unique constraint with multiple NULL's - --CELKO--

I assume that you have another identifier for the customer's account,
Internet and otherwise.  I also assume that this account id does not
look like an email address (no @ in it at least).  Could you simply
load that unique value into the email address column and keep the
email column NOT NULL UNIQUE?  You can then compare the customer_email
and acct_id to get the Internet and non-Internet customers in VIEWs.

You might want to Google around and read up on UNIQUE constraints and
multiple NULLs in Standard SQL and SQL Server.  It has been a hot
topic for awhile :)

Unique constraint with multiple NULL's - Eric Isaacs

You could create a unique view, you could create a trigger, or you
could create an additional calculated column that is the email address
OR the unique ID (assuming INT IDENTITY or something non-email like
that is also unique.)  That calculated column can have a unique index
on it.  I would probably opt for the calculated column in this
instance over the unique view or trigger.  It's a lot easier to
maintain.  But this technique doesn't always work.  It depends on the
uniqueness of the PK vs the data you're trying to keep unique when not
null.

Yet another option would be to break the email address into another
table with a unique constraint on the email address in that other
table.  In that case, the email address would be required in that
other table, and the primary key from your customer table would be the
foreign key in this new table.  The relationship between this other
table and the customer table would only exist when the customer had an
email address.

Unique constraint with multiple NULL's - Alex Kuznetsov

On May 28, 2:32 pm, "David Portas"

What if for soem customer there is a row in both tables? How would you
prevent that?

Unique constraint with multiple NULL's - Payson

hat
nt
s
t

You might try creating a secondary table to hold customer emails when
they exist.  Something like this (not tested):

CREATE TABLE Customers (
Customer_nbr INT NOT NULL,
Customer_name VARCHAR(30) NOT NULL,
CONSTRAINT Cust_key PRIMARY KEY (Customer_nbr)
) ;
GO
CREATE TABLE Cust_emails (
Customer_nbr INT NOT NULL,
Customer_email VARCHAR(50) NOT NULL,
CONSTRAINT Cust_email_key PRIMARY KEY (Customer_nbr),
CONSTRAINT Cust_to_cust_email FOREIGN KEY (Customer_nbr) REFERENCES
Customers (Customer_nbr),
CONSTRAINT Unique_email UNIQUE (Customer_email)
) ;
GO

Good luck

Payson

Unique constraint with multiple NULL's - Chris

Thanks everyone for your answers.  I quite like the idea of the computer
column but my only concern if whether this will impact performance in any
way.  From what i've read, the column is computed everytime it is queried.
I wonder if it is better to check the data as it is input instead, either by
using a trigger or as some have suggested moving the e-mail into a separate
table.  Customers who do not have an online account can also have an e-mail
address.  They are only considered an online customer if they have an e-mail
address and a password.

Thanks for the input, it gives me some alternatives to check out.

Chris

Unique constraint with multiple NULL's - Gert-Jan Strik

Then you should not have a problem with Uri's solution, since you never
have the need to query the nullbuster column.

As for the UNIQUE constraint: this is not a problem either, since SQL
Server will create a unique index to enforce the UNIQUE constraint, and
this will materialize the value of the nullbuster, and thus will not
trigger a recomputation.

--
Gert-Jan
SQL Server MVP

Unique constraint with multiple NULL's - Alex Kuznetsov

Not quite right - when you build an index on a computed column, the
computed value is stored. On 2005 you can have persisted computed
columns, which are stored even if you don't have an index on them.