table with no primary key

[lexcola]

Why is possible to create a table without a primary key? In other
words what advantages come from creating a table with no primary
key?

I am working with a text book, Access 2003 Guidebook 5th ed.; ISBN:
1-576-76141-x, and there is an example file which has no primary key.
When an existing record in the fore mentioned table is completely 100%
retyped, there are no primary key error window pop-ups. Actually,
there are no pop-ups at all.

When the same table is viewed in design view, none of the fields have
the primary key symbol. Prior to working with this file which came
with the book, I had always thought that tables must have a primary
key.

I thank all of you in advance for your insights.

 

[Ken Snell \(MVP\)]

It's usually a good idea to have a primary key in a table so that each
record is uniquely identified. However, there are times when a primary key
might be omitted because you don't need uniqueness (there is just one field
in the table; it's a temporary table that is a "midpoint" to storing the
data in a permanent table, etc.). But in general, one would normally expect
to find / have a primary key in a table.

 

[John W. Vinson]

Why is possible to create a table without a primary key?

It's also possible to create a car without brakes... <g>

Neither is something you would ordinarily want to do, but you might
occasionally have a "scratch" table, or a one-record table, for which it's not
essential to have a PK.

John W. Vinson [MVP]

 

[Jamie Collins]

Why is possible to create a table without a primary key?

If you mean duplicate rows... In a nutshell, because Jet is a SQL
engine and the SQL language allows it. It's one of the sticks
relational theorists use to hit SQL with e.g. see "The Askew Wall" by
Hugh Darwen. SQL DMBS is *not* a subset of RDBMS in the formal sense.

If you mean the SQL keyword PRIMARY KEY... It found its way into the
SQL language, despite theorists realizing that *logically* all keys
are equal. A SQL table should have a key, otherwise it's a
'heap' (which does have its uses, as John Vinson points out). A key
doesn't have to be implemented via a PRIMARY KEY constraint: a UNIQUE
constraint (index no duplicates) on all NOT NULL (Required = yes)
columns is logically equivalent. PRIMARY KEY is a *physical*
consideration; the choice is arbitrary and implementation dependent.
For Jet, PRIMARY KEY designation determines physical ordering on disk
(clustering) on compact (i.e. is a major consideration in query
optimization, according to Microsoft documentation); its columns'
names appear as bold text in the Relationships window in the Access
user interface; plus a few other subtleties. Do not confuse
'arbitrary' with 'optional': IMO all SQL tables should have a PRIMARY
KEY, even if merely to use non-key columns to influence physical
clustering. For more background, see:

Down with Primary Keys?
by David Portas'
http://blogs.conchango.com/davidportas/archive/2006/09/14/Down-with-Primary-Keys_3F00_.aspx

Jamie.

--

 

[Guest]

Despite the theory, if you don't need a primary key, its a waste if space to
have one.
If you are never going to access an individual record, why have a primary key?
I have one application that has a table whose sole purpose is to collect
records inserted from a web page. This table has no primary key. All of the
records are periodically transferred, as a group, to another table.

-Dorian

 

[Tony Toews [MVP]]

It's also possible to create a car without brakes... <g>

Neither is something you would ordinarily want to do, but you might
occasionally have a "scratch" table, or a one-record table, for which it's not
essential to have a PK.

I'm thinking of kids scooters and trikes.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Jamie Collins]

you might
occasionally have ... a one-record table, for which it's not
essential to have a PK.

I think it's essential for a one-record table to have a PK (or
equivalent), otherwise how do you ensure there is never more than one
row?! e.g.

CREATE TABLE Constants
(
lock CHAR(1) DEFAULT 'x' NOT NULL PRIMARY KEY
CONSTRAINT constants__max_one_row
CHECK (lock = 'x'),
pi DECIMAL(28, 27) NOT NULL ...
);

Jamie.

--

 

[RoyVidar]

I think it's essential for a one-record table to have a PK (or
equivalent), otherwise how do you ensure there is never more than one
row?!

Wouldn't this work?

CREATE TABLE Constants
(
somefield Varchar(10),
CONSTRAINT constants__max_one_row
CHECK (1 >=
(SELECT Count(*)
FROM Constants)))

 

[Jamie Collins]

Wouldn't this work?

CREATE TABLE Constants
(
somefield Varchar(10),
CONSTRAINT constants__max_one_row
CHECK (1 >=
(SELECT Count(*)
FROM Constants)))

Sure. Note I said, "PK (or equivalent)" and yours falls into the
latter category

But equally you could do similar on a regular table e.g.

CREATE TABLE Customers
(
customer_number char(10) NOT NULL,
CONSTRAINT customer_number__no_dups
CHECK (NOT EXISTS
(SELECT C1.customer_number
FROM Customers AS C1
GROUP BY C1.customer_number
HAVING COUNT(*) > 1)),
last_name VARCHAR(35) NOT NULL...
);

But we more often see something like:

CREATE TABLE Customers
(
customer_number CHAR(10) NOT NULL PRIMARY KEY,
last_name VARCHAR(35) NOT NULL...
);

....justifiable so, IMO. I think of table-level CHECK constraints as
being 'expensive'. For example, you get a more familiar error message
when a PK or UNIQUE (index no duplicates) constraint bites. And why
reinvent the wheel <g>?

Jet differentiates between CHECK constraints that are row-level and
(truly) table-level respectively e.g. you can't drop a table with a
table-level CHECK constraint (probably a bug but still it highlights
there the fact that Jet makes a distinction). From an implementation
point of view, row- and column-level CHECK constraints can be
implemented using Validation Rules, which have some advantages e.g.
can specify the failure message, better support in the Access user
interface (e.g. copying a table's structure may omit its CHECK
constraints), etc. That said, you only get one row-level Validation
Rule per table and IMO multiple CHECK constraints are better than a
single Validation Rule that tests multiple business rules i.e. you can
achieve better failure message granularity with multiple CHECK
constraints that have meaningful names to trap.

In newsgroups posts, I find it easier to post CHECK constraints in SQL
DLL than the equivalent Validation Rules in natural language or VBA.
My CHECK (lock = 'x') can be replaced by a column-level Validation
Rule.

I tend to reserve table-level CHECK constraints for those occasions
when no other combination of constraints will do the job.

Jamie.

--

 

[Guest]

I'm sure I can think of more examples, however it's late in the day and my
brain is moving in slow motion.

I would think look up tables don't need a PK. For example, if you have a
date field and do a lookup to a table listing all of the states, I don't see
any need for a PK. I do this all the time, i.e. LtblStates that has only one
field, all 50 states.

Robert

 

[John W. Vinson]

I do this all the time, i.e. LtblStates that has only one
field, all 50 states.

I'd make the state code the PK; otherwise some Texan might get into it and
edit the table to

TX
TX
TX
TX
TX
TX
....

<g>

John W. Vinson [MVP]

 

[Jamie Collins]

I'd make the state code the PK; otherwise some Texan might get into it and
edit the table to

TX
TX
TX
TX
TX
TX
...

<g>

I think I see Robert T's point: how much time and effort should you
devote to the design of constraints on a lookup table with very stable
values and on which most users should (IMO) have only 'select'
privileges? That said, defining a key over an 'all key' table comes
very cheap so I don't see a much of a case for omitting it.

Jamie.

--

 

[Tony Toews [MVP]]

I would think look up tables don't need a PK. For example, if you have a
date field and do a lookup to a table listing all of the states, I don't see
any need for a PK. I do this all the time, i.e. LtblStates that has only one
field, all 50 states.

Putting the state/province field itself as a PK might be a good idea
in this case. Although performance might not matter much as chances
are the entire table would reside in one 4 kb page and thus it would
be read all at once and sorted within memory.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/