Re: how do i add primary key in an existing database access 2003
- From: Ken Sheridan <KenSheridan@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sun, 18 Nov 2007 09:21:00 -0800
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
"Phil Hogle" wrote:
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
"John W. Vinson" wrote:
On Thu, 15 Nov 2007 13:29:00 -0800, Phil Hogle <Phil
Hogle@xxxxxxxxxxxxxxx icrosoft.com> wrote:
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]
.
- References:
- Re: how do i add primary key in an existing database access 2003
- From: John W . Vinson
- Re: how do i add primary key in an existing database access 2003
- Prev by Date: Re: help writing turabian style
- Next by Date: Re: help writing turabian style
- Previous by thread: Re: how do i add primary key in an existing database access 2003
- Next by thread: user & group permissions
- Index(es):
Relevant Pages
|
