how do i add primary key in an existing database access 2003

[Phil Hogle]

I am new to access 2003, I inherited a database that shud have a primary key
but does not have one

 

[John W. Vinson]

On Thu, 15 Nov 2007 13:29:00 -0800, Phil Hogle <Phil

I am new to access 2003, I inherited a database that shud have a primary key
but does not have one

No, you didn't. Databases don't have primary keys; tables do. Your database
will typically contain several tables.

What specifically DO you have? A database (.mdb file container) containing
only one table? A database with multiple tables, one of which needs a primary
key? More details please!

John W. Vinson [MVP]

 

[Phil Hogle]

Not only am I new to A-2003 but also to database and terminlogy.
the database has several tables that do not have a primary key. I am adding
some new tables which will have a primary key. The inherited main table needs
a primary key added to it.

Phil Hogle

 

[dcc15 via AccessMonster.com]

Open table in design mode and right click on the field (added or existing)
you want to be 'Key" and select 'Primary key' from the menu. You can insert a
new field line the same way (inserts empty line above the highlighted field).
Make a test table (BACKUP before playing or disaster may result!) to try
these functions, it's hard to ruin a "test" table

Not only am I new to A-2003 but also to database and terminlogy.
the database has several tables that do not have a primary key. I am adding
some new tables which will have a primary key. The inherited main table needs
a primary key added to it.

Phil Hogle
[quoted text clipped - 7 lines]

John W. Vinson [MVP]

 

[John W. Vinson]

Not only am I new to A-2003 but also to database and terminlogy.
the database has several tables that do not have a primary key. I am adding
some new tables which will have a primary key. The inherited main table needs
a primary key added to it.

Phil Hogle

dcc15's advice is a good place to start, if the table has a field (or a
combination of fields) which are a good "candidate key". The criteria for a
candidate key is that the field must be unique - every record in the table has
a different value for that field; it should be stable - not something that
will be frequently edited; and (ideally, but not critically) it should be
short (a 255-byte text field can be a primary key but your table's indexes
will be larger and less efficient).

If you have no such candidate key, then you can create a new table by copying
this table, design mode only, to a new table; open the new table in design
view and add a new Autonumber field and make it the primary key. Then run an
Append query to copy the data from your existing table into the new one. Be
aware that this autonumber will make every record unique, even if there are
duplicate records in the other fields - you'll need some way to identify and
prevent such duplicates.

John W. Vinson [MVP]

 

[Ken Sheridan]

If the inherited tables are related then you'll also need to add foreign keys
to the tables on the 'many' side of each relationship. Say you have tables
Customers and Orders and you add autonumber primary key columns CustomerID
and OrderID, you'll also need to add a foreign key CustomerID column to
Orders, not an autonumber this time but a straightforward long integer number
data type.

You then have to fill the foreign key CustomerID in Orders with the values
which point to the correct rows in Customers. You do this by first
identifying columns in each table as you inherited them which enable you to
join them. This could be a combination of FirstName, LastName and Address
columns in each table for instance, in which case you'd fill the CustomerID
column in Orders with an update query like so:


UPDATE Orders INNER JOIN Customers
ON Orders.FirstName = Customers.FirstName
AND Orders.LastName = Customers.LastName
AND Orders.Address = Customers.Address
SET Orders.CustomerID = Customers.CustomerID;

In this example, once you've filled the foreign key CustomerID column with
the above update query you can delete the FirstName, LastName and Address
columns from the Orders table as they are no longer needed, and moreover
leave the table open to the risk of inconsistent data being entered. The
table is said to contain redundancy and not be fully normalized. Whenever
you need to show the customer details for an order you'd simply join the
tables on the CustomerID columns in a query. For data entry of orders you'd
have a combo box bound to the CustomerID column in Orders but which shows the
actual names by hiding the CustomerID column. For an example of this see the
CustomerID combo box on the Orders form in the sample Northwind database
which comes with Access.

Ken Sheridan
Stafford, England