Creating a constraint on a table

My table on the SQL 2000 Server has a filed Id which is a (clustered)
primary key.
There is but another field named Field1 and I wish to define it as unique or
it could be Null.
How can I manage this constraint with help of SQL Server Enterprise manager
direct on the table or with help of Quer Analyzer?
Thanks
IvanIvan
Take a look at this example posted by Steve Kass long time ago
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
"Ivan" <ivan@.nekje.si> wrote in message
news:O2cqstz0HHA.5980@.TK2MSFTNGP04.phx.gbl...
> My table on the SQL 2000 Server has a filed Id which is a (clustered)
> primary key.
> There is but another field named Field1 and I wish to define it as unique
> or it could be Null.
> How can I manage this constraint with help of SQL Server Enterprise
> manager direct on the table or with help of Quer Analyzer?
> Thanks
> Ivan
>|||Thank you Uri. In the meantime I found also with help of Google the same
whole discussion about inserting a new computed column and ceating a unique
index based on both columns - it is pretty interesting!
Ivan
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23cNLFzz0HHA.3536@.TK2MSFTNGP06.phx.gbl...
> Ivan
> Take a look at this example posted by Steve Kass long time ago
> 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
> "Ivan" <ivan@.nekje.si> wrote in message
> news:O2cqstz0HHA.5980@.TK2MSFTNGP04.phx.gbl...
>> My table on the SQL 2000 Server has a filed Id which is a (clustered)
>> primary key.
>> There is but another field named Field1 and I wish to define it as unique
>> or it could be Null.
>> How can I manage this constraint with help of SQL Server Enterprise
>> manager direct on the table or with help of Quer Analyzer?
>> Thanks
>> Ivan
>|||> There is but another field named Field1 and I wish to define it as unique
> or it could be Null.
Another technique is to create a view that excludes NULLs and then create a
unique index on the view. For example
CREATE TABLE dbo.MyTable
(
Id int NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY,
Field1 int NULL
)
GO
CREATE VIEW dbo.MyTable_Unique_Field1
WITH SCHEMABINDING
AS
SELECT
Field1
FROM dbo.MyTable
WHERE Field1 IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX MyTable_Unique_Field1
ON dbo.MyTable_Unique_Field1(Field1)
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <ivan@.nekje.si> wrote in message
news:O2cqstz0HHA.5980@.TK2MSFTNGP04.phx.gbl...
> My table on the SQL 2000 Server has a filed Id which is a (clustered)
> primary key.
> There is but another field named Field1 and I wish to define it as unique
> or it could be Null.
> How can I manage this constraint with help of SQL Server Enterprise
> manager direct on the table or with help of Quer Analyzer?
> Thanks
> Ivan
>|||Thanks Dan,
I 'm sure that both tehnique are not valuable only for me.
Ivan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:F7642E07-D7D0-4EE8-8B32-9171638FE53F@.microsoft.com...
>> There is but another field named Field1 and I wish to define it as unique
>> or it could be Null.
> Another technique is to create a view that excludes NULLs and then create
> a unique index on the view. For example
> CREATE TABLE dbo.MyTable
> (
> Id int NOT NULL
> CONSTRAINT PK_MyTable PRIMARY KEY,
> Field1 int NULL
> )
> GO
> CREATE VIEW dbo.MyTable_Unique_Field1
> WITH SCHEMABINDING
> AS
> SELECT
> Field1
> FROM dbo.MyTable
> WHERE Field1 IS NOT NULL
> GO
> CREATE UNIQUE CLUSTERED INDEX MyTable_Unique_Field1
> ON dbo.MyTable_Unique_Field1(Field1)
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan" <ivan@.nekje.si> wrote in message
> news:O2cqstz0HHA.5980@.TK2MSFTNGP04.phx.gbl...
>> My table on the SQL 2000 Server has a filed Id which is a (clustered)
>> primary key.
>> There is but another field named Field1 and I wish to define it as unique
>> or it could be Null.
>> How can I manage this constraint with help of SQL Server Enterprise
>> manager direct on the table or with help of Quer Analyzer?
>> Thanks
>> Ivan
>