how to create primary key of type "text"

[Moe]

Just like the "northwind Database", due to relation to other tables with
"AutoNumber" key I can not use the "autoNumber" type for primary key
(CustomerID) on my "Customers" table. How can I generate a text type key
automatically while ensuring its uniquness?

 

[Jeff Boyce]

The notion of a primary key is that it is a unique row identifier. Whether
you use a person's name (not a good idea), their SSN (not everyone has one),
an employee# (hopefully the business ensures there's only one per employee
with no "re-use"), or some arbitrary combination of characters (alphabetic
and/or digit), the idea is that the key is unique, and can be used as a kind
of short-hand to point at the full row.

I don't understand why you couldn't use any field you want as a primary key
(again, provided it uniquely identifies each row)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Vinson]

Just like the "northwind Database", due to relation to other tables with
"AutoNumber" key I can not use the "autoNumber" type for primary key
(CustomerID) on my "Customers" table. How can I generate a text type key
automatically while ensuring its uniquness?

What's wrong with an autonumber? What are you trying to use as the
"text type key"?

You can select a field of any datatype except Memo or OLE in table
design view and click the Key icon to make that field a primary key.
For that matter, you can select up to ten fields - ctrl-click each of
them so that they are all highlighted - and then click the key icon.

If you are trying to make the customer name the primary key, though,
DON'T; primary keys should be unique, stable, and short, and names
fail on all three counts!

John W. Vinson[MVP]

 

[Moe]

Thanks Jeff
As I said I need to populate the CustomerID field in the "northwind Tradres
DB" automatically. It is defined as text based on the customer name. I was
thinking of having the phone number field to be copied into it after a user
enters the customer phone number. I can not just set the "phoneNumber" field
as the primary key because I have imported data where some records have no
phone number. My intention is to make the customerID assignment for new
records automatically.

 

[Moe]

If I use AutoNumber then in a related table, like "Orders" where its primary
Key is also an AutoNumber field I can not use CustomersID to relate the
tables. Each table can only have one AutoNumber field.
Moe

 

[Rick Brandt]

If I use AutoNumber then in a related table, like "Orders" where its
primary Key is also an AutoNumber field I can not use CustomersID to
relate the tables. Each table can only have one AutoNumber field.
Moe

You don't relate tables Primary Key to Primary Key except for in one-to-one
relationships. Otherwise both tables have a PK and the child table (many side)
has an additional *foreign key* field that is used to relate to the PK in the
parent table.

Your Orders table should have a PK field AND another field that contains the
CustomerID to relate to the Customers table. This CustomerID field in the
Orders table would be a standard Long Integer.

 

[John Vinson]

If I use AutoNumber then in a related table, like "Orders" where its primary
Key is also an AutoNumber field I can not use CustomersID to relate the
tables. Each table can only have one AutoNumber field.

The foreign key datatype that you need is Long Integer, NOT
Autonumber.

John W. Vinson[MVP]