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. 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.
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