primary keys

Hello,
I've a problem setting up a constraint on a table. There are two primary
keys in that table and one of them will be filled in through an
access application, wether it be nothing or some integer value.
What i want to do is to set a zero-value in a primary key column, how
can i do this? I've tried coalesce([columnname],'',0), but that gives me
an error. I think the syntax for the constraint is wrong, but i don't
know how to set it correctly.
Can anyone help me?Jason
You cannot do that . It seems you need create an UNIQUE CONTRAINT which will
accept a NULL value
Look at this example written 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
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:ekQO1ci2FHA.472@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I've a problem setting up a constraint on a table. There are two primary
> keys in that table and one of them will be filled in through an access
> application, wether it be nothing or some integer value.
> What i want to do is to set a zero-value in a primary key column, how can
> i do this? I've tried coalesce([columnname],'',0), but that gives me an
> error. I think the syntax for the constraint is wrong, but i don't know
> how to set it correctly.
> Can anyone help me?|||There can only be one designated primary key for a table; any additional
unique keys are called candidate keys. Column(s) with primary key constraint
cannot have NULL value and all values must be unique.
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:ekQO1ci2FHA.472@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I've a problem setting up a constraint on a table. There are two primary
> keys in that table and one of them will be filled in through an access
> application, wether it be nothing or some integer value.
> What i want to do is to set a zero-value in a primary key column, how can
> i do this? I've tried coalesce([columnname],'',0), but that gives me an
> error. I think the syntax for the constraint is wrong, but i don't know
> how to set it correctly.
> Can anyone help me?sql