0

Provided the primary key (PK) value is unique within a table:

  • is it essential (or advisable) to prevent (or discourage) leading control characters – such as tab characters?

Background

Articles such as Selecting the Right Primary Key in Microsoft Access (Blue Moose Technologies, 2008) caution against primary key values that may be typed by users.

I read that, amongst other things, Access does not allow a table to have a primary key with a null value.

Null values aside: Access 2010 does appear to allow a leading tab character within the primary key … whilst I'm far from expert with database technologies, this strikes me as potentially troublesome.

Related

What's the best practice for primary keys in tables?

Points of reference for older versions of Access

For naming, but not for values:

Creating Tables Manually – Programming Microsoft Access 2000 (Microsoft Programming Series) states:

… you cannot start a field name with a space or a control character (ASCII values 0 through 31). …

Microsoft's glossary for Access 2003 states, in relation to standard naming conventions:

… You also cannot use leading spaces or control characters (ASCII values 0 through 31). …

Community
  • 1
  • 1
Graham Perrin
  • 524
  • 1
  • 10
  • 45

1 Answers1

2

Articles . . . caution against primary key values that may be typed by users.

That's an absolutely awful article.

As far as the relational model is concerned, all candidate keys are created equal. There's no formal, logical principle that you can apply to a table having two candidate keys that lets you say, "This one is the only one that can be the primary key." There are usually some practical reasons for picking one over another, but there are no formal, logical reasons.

Somebody has to type in all the useful data in a database. There are well known, well supported ways of dealing with the propagation of typographic errors, the most common of which is to use on update cascade in foreign key references.

SQL databases give you a lot of control over what a column allows. In Access, use a validation rule on a column.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185