Check Constraint Expression

I'm using SQL Server 2005 and new to it.
I'm wanting to modify a Check constraint expression to say that the field (a
description) needs to be unique. I also want it to be null, but figure I ca
n
allow nulls or not within the field definition. Can I specify that in the
constraint as well?
Mainly, I need to be able to say that the field is unique, and since it's a
description.
How do I do this?I tried the following, but this didn't work. I was able to enter duplicate
descriptions.
([OrderTypeDescription] IS NOT NULL AND
[OrderTypeDescription] NOT IN (SELECT OrderTypeDescription FROM T_OrderType))
"HockeyFan" wrote:

> I'm using SQL Server 2005 and new to it.
> I'm wanting to modify a Check constraint expression to say that the field
(a
> description) needs to be unique. I also want it to be null, but figure I
can
> allow nulls or not within the field definition. Can I specify that in the
> constraint as well?
> Mainly, I need to be able to say that the field is unique, and since it's
a
> description.
> How do I do this?
>|||You don't want a CHECK constraint. Drop any existing CHECK constraint.
Make the column NOT NULL and add a UNIQUE constraint.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"HockeyFan" <HockeyFan@.discussions.microsoft.com> wrote in message
news:D6DEF6E6-3B74-47F9-92DE-9688198EE8EF@.microsoft.com...
I'm using SQL Server 2005 and new to it.
I'm wanting to modify a Check constraint expression to say that the field (a
description) needs to be unique. I also want it to be null, but figure I
can
allow nulls or not within the field definition. Can I specify that in the
constraint as well?
Mainly, I need to be able to say that the field is unique, and since it's a
description.
How do I do this?|||Where do I do that from within the SQL Server Management Studio?
"Tom Moreau" wrote:

> You don't want a CHECK constraint. Drop any existing CHECK constraint.
> Make the column NOT NULL and add a UNIQUE constraint.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "HockeyFan" <HockeyFan@.discussions.microsoft.com> wrote in message
> news:D6DEF6E6-3B74-47F9-92DE-9688198EE8EF@.microsoft.com...
> I'm using SQL Server 2005 and new to it.
> I'm wanting to modify a Check constraint expression to say that the field
(a
> description) needs to be unique. I also want it to be null, but figure I
> can
> allow nulls or not within the field definition. Can I specify that in the
> constraint as well?
> Mainly, I need to be able to say that the field is unique, and since it's
a
> description.
> How do I do this?
>|||I'd just do it in a query:
alter table MyTable
drop
constraint MyConstraint
go
alter table MyTable
alter column
MyCol int not null
go
alter table MyTable
add
constraint MyConstraint unique (MyCol)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"HockeyFan" <HockeyFan@.discussions.microsoft.com> wrote in message
news:D3170FBC-98AB-4FC5-9D20-4B697F698781@.microsoft.com...
Where do I do that from within the SQL Server Management Studio?
"Tom Moreau" wrote:

> You don't want a CHECK constraint. Drop any existing CHECK constraint.
> Make the column NOT NULL and add a UNIQUE constraint.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "HockeyFan" <HockeyFan@.discussions.microsoft.com> wrote in message
> news:D6DEF6E6-3B74-47F9-92DE-9688198EE8EF@.microsoft.com...
> I'm using SQL Server 2005 and new to it.
> I'm wanting to modify a Check constraint expression to say that the field
> (a
> description) needs to be unique. I also want it to be null, but figure I
> can
> allow nulls or not within the field definition. Can I specify that in the
> constraint as well?
> Mainly, I need to be able to say that the field is unique, and since it's
> a
> description.
> How do I do this?
>|||>I'm using SQL Server 2005 and new to it.
>I'm wanting to modify a Check constraint expression to say that the field (
a
>description) needs to be unique. I also want it to be null, but figure I c
an
>allow nulls or not within the field definition. Can I specify that in the
>constraint as well?
If I understand you, you want to allow the column to be NULL, but if
it is NOT null you want it to be UNIQUE. Is that correct?
If so, the next question is whether only one row can be NULL, or any
number of rows can be NULL. If only a single NULL row is allowed then
Tom's approach of adding a UNIQUE contraint will work fine. However,
if multiple NULL rows is allowed UNIQUE will fail on the second
occurance.
If what you need is multiple NULLs but unique non-NULL values, then we
just need a small adjustment to the code you posted.
(OrderTypeDescription IS NULL OR
OrderTypeDescription NOT IN
(SELECT OrderTypeDescription FROM T_OrderType))
Roy|||The proposed solution will allow a single null value.
I'd rather create an indexed view for that constraint (providing that the
description column allows nulls):
create view dbo.UniqueDescription
with schemabinding
as
select <column list>
,<description column>
from <table>
where (<description column> is not null)
go
create unique clustered index <index name>
on dbo.UniqueDescription
(
<clustered index candidate>
)
go
create unique nonclustered index <index name>
on dbo.UniqueDescription
(
<description column>
)
go
ML
http://milambda.blogspot.com/