Primary key allows null?

XP Pro / Access 2003 Project / SQL Server 2000 Backend

I have a table that I have assigned multiple fields as the primary key(s).
There are about 6 fields that make up the primary key for this table.  When
entering a new record into the table the primary key forces me to enter all
the data fields for the primary key.  BUT..  if I were to copy a record and
remove the data from one of the PK fields, SQL Server accepts this.  How is
this possible?  I have this field designated as PK and it cannot accept a
null, but it does.  Any help on this would be much appreciated.

Mark  wrote:
> XP Pro / Access 2003 Project / SQL Server 2000 Backend
>
> I have a table that I have assigned multiple fields as the primary key(s).
> There are about 6 fields that make up the primary key for this table.  Whe
n
> entering a new record into the table the primary key forces me to enter al
l
> the data fields for the primary key.  BUT..  if I were to copy a record an
d
> remove the data from one of the PK fields, SQL Server accepts this.  How i
s
> this possible?  I have this field designated as PK and it cannot accept a
> null, but it does.  Any help on this would be much appreciated.

Depends what you mean by "remove all the data". If it's a string then
it can be empty. An empty string isn't null. If it's a numeric then it
can be zero. Did you run a query to check what the value *really* is in
the table rather than just look at what you are shown in a grid
control?

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

> this possible?  I have this field designated as PK and it cannot accept a
> null, but it does.  Any help on this would be much appreciated.

Are you sure?  Are you certain that a unique constraint is not being used as
a PK (which will allow nulls)?  Below is a simple proof.

set nocount on

-- this will fail
create table #test (id1 int not null, id2 int null, other_junk varchar(20)
not null,
constraint pkx primary key (id1, id2) )
go

-- this will succeed
create table #test (id1 int not null, id2 int null, other_junk varchar(20)
not null,
constraint pkx unique (id1, id2) )
go

-- 1 of 2 will succeed
insert #test (id1, id2, other_junk) values (1, 1, 'test')
insert #test (id1, id2, other_junk) values (1, 1, 'test')
go

-- all will succeed
insert #test (id1, id2, other_junk) values (1, 2, 'test')
insert #test (id1, id2, other_junk) values (2, 1, 'test')
insert #test (id1, id2, other_junk) values (1, null, 'test')
go

-- this will fail
insert #test (id1, id2, other_junk) values (1, null, 'test')
go

-- this will succeed
update #test set id2 = null where id1 = 2 and id2 = 1
go

-- final result
select * from #test
go
drop table #test
go