Very Computer

Quote:> Well now is your chance to speak up!

TITLE: Enforce uniqueness for all non-NULL values (ANSI UNIQUE CONSTRAINT)
SCENARIO: We want to create a column that does not allow duplicates. However
we want to be able to insert many rows, where this column is NULL without
breaking the constraint.

Please note, that most other RDBMS, including, but not limited to Oracle and
MS Access enforce ANSI-standard unique constraints.

I want to give some reasons, why I call this ANSI UNIQUE CONSTRAINT.

Excerpt from ANSI standard. Please note the "if and only if" notation:
//
A unique constraint is satisfied if and only if no two rows in
a table have the same non-null values in the unique columns.
//

Excerpts from SQL Server 2000 Books On Line:
//
(a) Topic "Creating and Modifying UNIQUE Constraints":
When a UNIQUE constraint is added to an existing column or columns in the
table, MicrosoftR SQL ServerT 2000 by default checks the existing data in
the columns to ensure all values, except NULL, are unique
//
Looks like there was an idea SQL Server unique constraint to be ANSI
standard, but alas.

//
(b) Topic "Creating a Unique Constraint"
When you attach a unique constraint to a column allowing null values, you
ensure that at most one row will have a null value in the constrained
column.
//
This contradiction in SQL BOL, however, doesn't help us too much. (b) is the
correct as per implementation.

SOLUTION:
This solution only works with SQL Server 2000.

CREATE TABLE ANSI_Unique(
 PK INT PRIMARY KEY,
 UQ INT,
 UQ_Col AS CASE
  WHEN UQ IS NOT NULL THEN CAST(UQ AS VARCHAR(16))
  ELSE '_' + CAST(PK AS VARCHAR(15))
 END
 CONSTRAINT uq_ANSI_UQ UNIQUE
)
GO
INSERT INTO ANSI_Unique VALUES(1,1) -- This succeedes
INSERT INTO ANSI_Unique VALUES(2,1) -- This fails
INSERT INTO ANSI_Unique VALUES(3,NULL) -- This succeedes
INSERT INTO ANSI_Unique VALUES(4,NULL) -- This succeedes

The basic idea is that we calculate UQ_Col in a way that it is always
unique. Then we define an UNIQUE constraint on it.
--
Ivan Arjentinski
----------------------------------------------------------------------
Please reply to newsgroups. Inclusion of table schemas (CREATE TABLE
scripts), sample data (INSERT INTO....), verbal explanation and desired
result set when asking for assistance is highly appreciated
----------------------------------------------------------------------

Quote:> Hello Everyone!

> My name is Stephen Dybing and I am the Microsoft Forum Manager for SQL
> Server. I wanted to let you know about an initiative we have to bring you
> targeted HowTo content that is specific to a given task.

> Have you ever wanted to accomplish a specific task with SQL Server and
> couldn't find a simple HowTo document anywhere on the net that walked you
> through it step by step? Well now is your chance to speak up!

> What do you need to do? Simple, just reply to this post and provide the
> following:

> 1. Title of the idea
> 2. Describe a basic scenario where this would come in handy

> That's it! Of course there are no guarantees that an article will be
> written or when but I will respond back with updates. And, if an article
> already exists, I will post a link to it back to this discussion so you
can
> verify that covers the idea you had.

> Here are some HowTo links to give examples about what I'm talking about:

> TechNet:

Quote:> ns/howto/default.asp

> MSDN:
> http://msdn.microsoft.com/howto/default.asp

> Thanks!!

> Stephen Dybing
> SQL Server Forum Manager
> Microsoft Dev Communities

> Ive been "encouraged" to share this helpful information from our lawyers.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> You assume all risk for your use. ? 2001 Microsoft Corporation. All rights
> reserved.

Sincerely,
Stephen Dybing
Microsoft Developer Support

Please do not send email directly to this alias. This alias is for
newsgroup purposes and is intended to prevent automated spam.

Ive been "encouraged" to share this helpful information from our lawyers.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use. ? 2001 Microsoft Corporation. All rights
reserved.
--------------------

|
| Steve,
|
| > Well now is your chance to speak up!
| I'll give it a try.
|
| TITLE: Enforce uniqueness for all non-NULL values (ANSI UNIQUE CONSTRAINT)
| SCENARIO: We want to create a column that does not allow duplicates.
However
| we want to be able to insert many rows, where this column is NULL without
| breaking the constraint.
|
| Please note, that most other RDBMS, including, but not limited to Oracle
and
| MS Access enforce ANSI-standard unique constraints.
|
| I want to give some reasons, why I call this ANSI UNIQUE CONSTRAINT.
|
| Excerpt from ANSI standard. Please note the "if and only if" notation:
| //
| A unique constraint is satisfied if and only if no two rows in
| a table have the same non-null values in the unique columns.
| //
|
| Excerpts from SQL Server 2000 Books On Line:
| //
| (a) Topic "Creating and Modifying UNIQUE Constraints":
| When a UNIQUE constraint is added to an existing column or columns in the
| table, MicrosoftR SQL ServerT 2000 by default checks the existing data in
| the columns to ensure all values, except NULL, are unique
| //
| Looks like there was an idea SQL Server unique constraint to be ANSI
| standard, but alas.
|
| //
| (b) Topic "Creating a Unique Constraint"
| When you attach a unique constraint to a column allowing null values, you
| ensure that at most one row will have a null value in the constrained
| column.
| //
| This contradiction in SQL BOL, however, doesn't help us too much. (b) is
the
| correct as per implementation.
|
| SOLUTION:
| This solution only works with SQL Server 2000.
|
| CREATE TABLE ANSI_Unique(
|  PK INT PRIMARY KEY,
|  UQ INT,
|  UQ_Col AS CASE
|   WHEN UQ IS NOT NULL THEN CAST(UQ AS VARCHAR(16))
|   ELSE '_' + CAST(PK AS VARCHAR(15))
|  END
|  CONSTRAINT uq_ANSI_UQ UNIQUE
| )
| GO
| INSERT INTO ANSI_Unique VALUES(1,1) -- This succeedes
| INSERT INTO ANSI_Unique VALUES(2,1) -- This fails
| INSERT INTO ANSI_Unique VALUES(3,NULL) -- This succeedes
| INSERT INTO ANSI_Unique VALUES(4,NULL) -- This succeedes
|
| The basic idea is that we calculate UQ_Col in a way that it is always
| unique. Then we define an UNIQUE constraint on it.
| --
| Ivan Arjentinski
| ----------------------------------------------------------------------
| Please reply to newsgroups. Inclusion of table schemas (CREATE TABLE
| scripts), sample data (INSERT INTO....), verbal explanation and desired
| result set when asking for assistance is highly appreciated
| ----------------------------------------------------------------------
|


| > Hello Everyone!
| >
| > My name is Stephen Dybing and I am the Microsoft Forum Manager for SQL
| > Server. I wanted to let you know about an initiative we have to bring
you
| > targeted HowTo content that is specific to a given task.
| >
| > Have you ever wanted to accomplish a specific task with SQL Server and
| > couldn't find a simple HowTo document anywhere on the net that walked
you
| > through it step by step? Well now is your chance to speak up!
| >
| > What do you need to do? Simple, just reply to this post and provide the
| > following:
| >
| > 1. Title of the idea
| > 2. Describe a basic scenario where this would come in handy
| >
| > That's it! Of course there are no guarantees that an article will be
| > written or when but I will respond back with updates. And, if an article
| > already exists, I will post a link to it back to this discussion so you
| can
| > verify that covers the idea you had.
| >
| > Here are some HowTo links to give examples about what I'm talking about:
| >
| > TechNet:
| >
| >
|
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/it...
| > ns/howto/default.asp
| >
| > MSDN:
| > http://msdn.microsoft.com/howto/default.asp
| >
| > Thanks!!
| >
| > Stephen Dybing
| > SQL Server Forum Manager
| > Microsoft Dev Communities
| >
| > Ive been "encouraged" to share this helpful information from our
lawyers.
| > This posting is provided "AS IS" with no warranties, and confers no
| rights.
| > You assume all risk for your use. ? 2001 Microsoft Corporation. All
rights
| > reserved.
| >
|
|
|

I took time to read your post carefully. Please re-read mine more carefully.

This is something which some time ago I wanted to achieve with SQL Server,
but didn't know how. Now I know how and want to share some knowledge.
--
Ivan Arjentinski

Quote:> | > ns/howto/default.asp
> | >
> | > MSDN:
> | > http://msdn.microsoft.com/howto/default.asp
> | >
> | > Thanks!!
> | >
> | > Stephen Dybing
> | > SQL Server Forum Manager
> | > Microsoft Dev Communities
> | >
> | > Ive been "encouraged" to share this helpful information from our
> lawyers.
> | > This posting is provided "AS IS" with no warranties, and confers no
> | rights.
> | > You assume all risk for your use. ? 2001 Microsoft Corporation. All
> rights
> | > reserved.
> | >
> |
> |
> |

Your solution looks like it will work for tables that contain primary keys,
but we cannot gaurantee that all tables will have a primary key. Yes, it's
a central tenet of database theory that every tuple of every relation can
be uniquely identified, but this isn't enforced in SQL Server. So my mind
got stuck on the desire to make this work for any table and lost sight of
what you were actually saying. Again, I apologize for that. This is a great
bit of info and I'll get it written up into a KB article. Thanks!

Sincerely,
Stephen Dybing
Microsoft Developer Support

Please do not send email directly to this alias. This alias is for
newsgroup purposes and is intended to prevent automated spam.

Ive been "encouraged" to share this helpful information from our lawyers.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use. ? 2001 Microsoft Corporation. All rights
reserved.
--------------------

|
| Steve,
|
| I took time to read your post carefully. Please re-read mine more
carefully.
|
| This is something which some time ago I wanted to achieve with SQL Server,
| but didn't know how. Now I know how and want to share some knowledge.
| --
| Ivan Arjentinski
|


| > If I understand you correctly, you're not asking for an article to
explain
| > how to do something, what you're asking for is a feature change to SQL
| > Server. What you need to do for this one is send a request to

| >
| > Sincerely,
| > Stephen Dybing
| > Microsoft Developer Support
| >
| >
| > Please do not send email directly to this alias. This alias is for
| > newsgroup purposes and is intended to prevent automated spam.
| >
| > Ive been "encouraged" to share this helpful information from our
lawyers.
| > This posting is provided "AS IS" with no warranties, and confers no
| rights.
| > You assume all risk for your use. ? 2001 Microsoft Corporation. All
rights
| > reserved.
| > --------------------

| > |
| > | Steve,
| > |
| > | > Well now is your chance to speak up!
| > | I'll give it a try.
| > |
| > | TITLE: Enforce uniqueness for all non-NULL values (ANSI UNIQUE
| CONSTRAINT)
| > | SCENARIO: We want to create a column that does not allow duplicates.
| > However
| > | we want to be able to insert many rows, where this column is NULL
| without
| > | breaking the constraint.
| > |
| > | Please note, that most other RDBMS, including, but not limited to
Oracle
| > and
| > | MS Access enforce ANSI-standard unique constraints.
| > |
| > | I want to give some reasons, why I call this ANSI UNIQUE CONSTRAINT.
| > |
| > | Excerpt from ANSI standard. Please note the "if and only if" notation:
| > | //
| > | A unique constraint is satisfied if and only if no two rows in
| > | a table have the same non-null values in the unique columns.
| > | //
| > |
| > | Excerpts from SQL Server 2000 Books On Line:
| > | //
| > | (a) Topic "Creating and Modifying UNIQUE Constraints":
| > | When a UNIQUE constraint is added to an existing column or columns in
| the
| > | table, MicrosoftR SQL ServerT 2000 by default checks the existing data
| in
| > | the columns to ensure all values, except NULL, are unique
| > | //
| > | Looks like there was an idea SQL Server unique constraint to be ANSI
| > | standard, but alas.
| > |
| > | //
| > | (b) Topic "Creating a Unique Constraint"
| > | When you attach a unique constraint to a column allowing null values,
| you
| > | ensure that at most one row will have a null value in the constrained
| > | column.
| > | //
| > | This contradiction in SQL BOL, however, doesn't help us too much. (b)
is
| > the
| > | correct as per implementation.
| > |
| > | SOLUTION:
| > | This solution only works with SQL Server 2000.
| > |
| > | CREATE TABLE ANSI_Unique(
| > |  PK INT PRIMARY KEY,
| > |  UQ INT,
| > |  UQ_Col AS CASE
| > |   WHEN UQ IS NOT NULL THEN CAST(UQ AS VARCHAR(16))
| > |   ELSE '_' + CAST(PK AS VARCHAR(15))
| > |  END
| > |  CONSTRAINT uq_ANSI_UQ UNIQUE
| > | )
| > | GO
| > | INSERT INTO ANSI_Unique VALUES(1,1) -- This succeedes
| > | INSERT INTO ANSI_Unique VALUES(2,1) -- This fails
| > | INSERT INTO ANSI_Unique VALUES(3,NULL) -- This succeedes
| > | INSERT INTO ANSI_Unique VALUES(4,NULL) -- This succeedes
| > |
| > | The basic idea is that we calculate UQ_Col in a way that it is always
| > | unique. Then we define an UNIQUE constraint on it.
| > | --
| > | Ivan Arjentinski
| > | ----------------------------------------------------------------------
| > | Please reply to newsgroups. Inclusion of table schemas (CREATE TABLE
| > | scripts), sample data (INSERT INTO....), verbal explanation and
desired
| > | result set when asking for assistance is highly appreciated
| > | ----------------------------------------------------------------------
| > |


| > | > Hello Everyone!
| > | >
| > | > My name is Stephen Dybing and I am the Microsoft Forum Manager for
SQL
| > | > Server. I wanted to let you know about an initiative we have to
bring
| > you
| > | > targeted HowTo content that is specific to a given task.
| > | >
| > | > Have you ever wanted to accomplish a specific task with SQL Server
and
| > | > couldn't find a simple HowTo document anywhere on the net that
walked
| > you
| > | > through it step by step? Well now is your chance to speak up!
| > | >
| > | > What do you need to do? Simple, just reply to this post and provide
| the
| > | > following:
| > | >
| > | > 1. Title of the idea
| > | > 2. Describe a basic scenario where this would come in handy
| > | >
| > | > That's it! Of course there are no guarantees that an article will be
| > | > written or when but I will respond back with updates. And, if an
| article
| > | > already exists, I will post a link to it back to this discussion so
| you
| > | can
| > | > verify that covers the idea you had.
| > | >
| > | > Here are some HowTo links to give examples about what I'm talking
| about:
| > | >
| > | > TechNet:
| > | >
| > | >
| > |
| >
|
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/it...
| > | > ns/howto/default.asp
| > | >
| > | > MSDN:
| > | > http://msdn.microsoft.com/howto/default.asp
| > | >
| > | > Thanks!!
| > | >
| > | > Stephen Dybing
| > | > SQL Server Forum Manager
| > | > Microsoft Dev Communities
| > | >
| > | > Ive been "encouraged" to share this helpful information from our
| > lawyers.
| > | > This posting is provided "AS IS" with no warranties, and confers no
| > | rights.
| > | > You assume all risk for your use. ? 2001 Microsoft Corporation. All
| > rights
| > | > reserved.