PK Null value

What data type does the primary key have to be? I have my PK set to text and
it returns a null value error. It also says "PK cannot be a null value" when
i have the data type as Number (long integer). What am i doing wrong?

A primary key field's value is not allowed to be NULL because a primary key
always must have a unique value for every record. I'm guessing that you're
trying to set an exising field to primary key, and you already have records
in the table, and in at least one of those records that field's value is
NULL. You'll need to go to datasheet view of the table and put a unique
value in every one of the records for the field that you want to have be the
primary key, then after that is done you'll be able to make that field the
primary key.
--

Ken Snell
MS ACCESS MVP
http://www.accessmvp.com/KDSnell/


"Colby" wrote in message
...
What data type does the primary key have to be? I have my PK set to text
and
it returns a null value error. It also says "PK cannot be a null value"
when
i have the data type as Number (long integer). What am i doing wrong?

Colby,

A primary key can be numeric OR text but it can't be empty (aka: Null). If
this primary key is of no use to you, meaning it doesn't have to be any
special set of letters or numbers, use Autonumber. Autonumber will fill in
a value for you.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Colby" wrote in message
...
What data type does the primary key have to be? I have my PK set to text
and
it returns a null value error. It also says "PK cannot be a null value"
when
i have the data type as Number (long integer). What am i doing wrong?

The fact that the column can contain Nulls suggests that it is probably not
suitable as the primary key by virtue of what 'attribute' it represents. The
data for a key must always be known and must be a value (Null is, despite the
message you got, not a value, but the absence of a value; though confusingly
the Value property of a column or control can be Null). Keys can be
'natural' or 'surrogate'. The data type is immaterial, so can be text,
number or date.

A 'natural' key is one made up of a column or columns which represent
meaningful data; e.g. a column of US states or UK counties could be the key
of a States or Counties table as in each case the names are distinct. A
column City in a table Cities is unsuitable as a primary key, however, as
city names are not infrequently duplicated.

A 'surrogate' key is one made up of a column of distinct values which are
arbitrary and serve only as a unique identifier. An 'autonumber' column of
the type Gina suggests is a surrogate key. An autonumber CityID column would
be a suitable key of a Cities table for instance.

Some people invariably use surrogate keys, usually by means of an autonumber
column, and often this has advantages. In some situations, though, a natural
key, where possible, can itself have advantages. One is where data is
entered or retrieved by the use of 'correlated combo boxes' where you select
a value from a list in one combo box which then limits the values in a list
in another combo box and so on. Where surrogate keys are used this does not
work well in a continuous form, as it will cause the values in the same
control in other rows to go blank when a different value is selected in the
current row; the values are still there in the column, but you don't see them.
There are ways around this, but they can be a bit kludgy, particularly if
more then two combo boxes are involved. Where a natural key is used, however,
the problem doesn't arise in the first place.

Another advantage of a natural key is that it can make it unnecessary to
include an additional table in a query. If you have a table of cities with a
State foreign key column referencing the natural key of a States table, say,
the States table does not have to be included in a query to see the state
name, as it would if a numeric surrogate StateID key were used. This does
not mean that the Sates table is redundant, however. It is necessary to
enforce referential integrity so that only valid state names can be entered
in Cities. Also, without it, if you don't have rows in cities for at least
one city in each state, then some states will be missing from the database
completely. You might say so what, we all know what they are, but a good
database should always cater for the Martian visitor with no prior knowledge.

Bear in mind that any table will always have a 'natural' candidate key, even
if it is a composite one made up of all columns in the table. If,
hypothetically, there were two cities of the same name in the same state you
might think that there can be no natural key as the row for each would have
the same values in each column. That is true, but the table would not be a
valid one as there is no way of knowing which is which. There would have to
be another column of real data which distinguishes the two, and it would be
the combination of all three columns which is the candidate key. Merely
introducing an autonumber column as the key is insufficient.

Finally, if a surrogate key is used, and there can be one or more other
candidate keys for the table, e.g. if a StateID was used as the key of States,
then its essential that the other candidate keys, whether made up of one or
more columns, are indexed uniquely to prevent duplicate values being
invalidly inserted.

So, in your case you need either to identify a column or set of columns whose
values uniquely identify each row in the table and use that column or columns
as the key, or introduce a surrogate key, e.g. by means of an autonumber
column.

Ken Sheridan
Stafford, England

Colby wrote:
What data type does the primary key have to be? I have my PK set to text and
it returns a null value error. It also says "PK cannot be a null value" when
i have the data type as Number (long integer). What am i doing wrong?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200909/1

"Gina Whipp" wrote in
:

A primary key can be numeric OR text but it can't be empty (aka:
Null).

Nitpick:

Empty is not exactly the same as Null.

Empty could be a zero-length string (if that's allowed to be stored,
and, annoyingly, since A2003, allowing ZLS is the default for text
fields).

In a field that allows ZLS, Null could mean "nobody has even
bothered to decide what should be in this field -- it's completely
unknown" while the ZLS (empty) could mean "somebody checked this
record and stored a ZLS to show that there is no value for this
field."

One option to avoid the Null problem in a compound unique index is
simply to allow ZLS and set the default value to "".

But I'd never do that.

Of course, I'd also never use compound keys!

The point is that any field in a unique index has to have a default
value. Otherwise it could be Null and thus can't function as a
unique index.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Nitpick away... you are CORRECT and I should be careful when typing those
kinds of replies when it is a known fact they are interchangeable. Thank
you, hopefully the OP saw this!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"David W. Fenton" wrote in message
36.99...
"Gina Whipp" wrote in
:

A primary key can be numeric OR text but it can't be empty (aka:
Null).

Nitpick:

Empty is not exactly the same as Null.

Empty could be a zero-length string (if that's allowed to be stored,
and, annoyingly, since A2003, allowing ZLS is the default for text
fields).

In a field that allows ZLS, Null could mean "nobody has even
bothered to decide what should be in this field -- it's completely
unknown" while the ZLS (empty) could mean "somebody checked this
record and stored a ZLS to show that there is no value for this
field."

One option to avoid the Null problem in a compound unique index is
simply to allow ZLS and set the default value to "".

But I'd never do that.

Of course, I'd also never use compound keys!

The point is that any field in a unique index has to have a default
value. Otherwise it could be Null and thus can't function as a
unique index.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/