No Primary Key!

[Bob Vance]

One of my tables is called tblInvoice_Holding , which holds my invoices till
I distribute. When I create a holding invoice the record is assigned a
Invoice Holding number, which is just a number field with No Primary key in
the table, I noticed I had a bug and say 6 different records where given the
same number, should I make this field the Primary key, and that should stop
different records getting the same number?

 

[Bob Vance]

Thanks Ken , I shall change it to primary key!, So really primary key just
means unique!
Regards Bob

 

[Banana]

In effect, yes. A primary key has to be unique, but a column which has to
have unique values does not necessarily have to be the primary key.

I just want to point out an important difference between a primary key
and a column with unique index (e.g. Index set to "Indexed (No
duplicates)") -

Primary key does not allow nulls while unique indexed column may be
null. This is significant when you consider that a uniquely indexed
column can have many nulls (null != null) and still be unique. So
primary key is slightly more rigid definition of uniqueness.

Of course, the uniquely indexed column could then be defined to not
permit nulls (e.g. Required set to "Yes") in which it would be
functionally equivalent to the primary key.

 

[Jerry Whittle]

Ken gave you very good advice.

However you can't just turn it into a primary key field now. If there are
duplicates, you will need to fix them first. The same goes for null or blank
fields. Access won't let you create the PK until all the existing data meets
the rules.

 

[Bob Vance]

Thanks guys, found that out and fix the dupes before setting primary key!