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

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

Preventing overlapping data entry

Hello Everyone,

I have a web form that allows the user to select a time to reserve. I am trying to make it so that when a user selects a date to schedule something (which i have working) a drop down list will fill with times that have not been reserved.

The data is stored in two tables: tblCalendar and tblTime. tblTime contains pkTimeID and times (which are all possible times to select from in half hour intervals). tblCalendar contains a few fields but timeID and date (which is in the format M/d/yyyy) are what I need to test against. timeID is the foreign key of pkTimeID.

Basically when the user selects the date, a function gets called that will run a SELECT statement to get the times available. All of this works, I am able to fill the ddl with all times available no matter what the date is or what has already been reserved. I want to check if a time has been already selected based on the date selected by the user and only allow times not selected to be listed.

After acheiving this I would like to prevent the immediate time before and immediate time after from being displayed because each reserved time will last for one hour but the data is stored in half hour increments.

Any help/suggestions/links will be greatly appreciated. If I need to provide anything else please let me know.

Thanks in advance,

Brian

This query should give you the timeIDs that have not been scheduled:

SELECT t.pkTimeIDFROM tblTime tWHERE t.pkTimeIDNOT IN (SELECT c.timeIDFROM tblCalendar cWHERE c.date ='4/20/2007' )

Replace the date with the date the user has selected.|||

This query leaves out the prior and next time slots to avoid overlapping a one hour appointment.

SELECT t.pkTimeIDFROM tblTime tWHERE t.pkTimeIDNOT IN (SELECT unavailableTimes.timeIDFROM (SELECT c.timeID, c.dateFROM tblCalendar cLEFTJOIN tblTime tON t.pkTimeID = c.timeIDUNION SELECT t1.pkTimeID, c.dateFROM tblCalendar cLEFTJOIN tblTime tON t.pkTimeID = c.timeIDLEFTJOIN tblTime t1ON t1.time =SUBSTRING(CONVERT(VARCHAR(20),DATEADD(n, -30, c.date +' ' + t.time)), 14, 7)UNION SELECT t2.pkTimeID, c.dateFROM tblCalendar cLEFTJOIN tblTime tON t.pkTimeID = c.timeIDLEFTJOIN tblTime t2ON t2.time =SUBSTRING(CONVERT(VARCHAR(20),DATEADD(n, 30, c.date +' ' + t.time)), 14, 7) ) unavailableTimesWHERE unavailableTimes.date ='4/20/2007' )

This query is not optimal but you get the idea. Again, replace the date with the date the user has selected. You may also need to tweak the SUBSTRING params based on the format of your DATETIMEs.|||

Thank you so much for your help. I think I was a little to vague on my description but this solution helped me get a solution that worked for me. I needed to check for the previous and next pkTimeID value to eliminate not the actual datetime values. Just to keep this short my statement is as follows:

SELECT t.pkTimeID, t.timeFROM tblTime tWHERE t.pkTimeIDNOT IN (SELECT c.timeID - 1FROM tblCalendar cUNIONSELECT c1.timeIDFROM tblCalendar c1UNIONSELECT c2.timeID + 1FROM tblCalendar c2WHERE (c2.[date] ='4/20/2007'))

Thank you again for your help, and if you have any suggestions on how I might improve what I did come up with I would appreciate it. This is my first "real" project in the work place and I would like to learn the best way to approach a problem where ever I can, so that I will not have to learn these things later.

Brian