Modify a primary key

[Guest]

I'd like to add an additional field to a primary key which now consists of 3
fields. I need to do this programmatically either in an SQL statement or
using ADO or DAO.
I want add the "Date Entered" field to the primary key.

Do I need to remove the current primary key, or can I add a field to the
current primary key? I've been unable to locate how to accomplish this in my
reference books.

thanks
James

 

['69 Camaro]

Hi, James.

I'd like to add an additional field to a primary key which now consists of
3
fields. I need to do this programmatically

That's easy enough, but adding the column is way down on the list of things
that must be done.

Do I need to remove the current primary key

Yes. However, if there is a relationship with other tables, then the
foreign key constraints with all of the related tables must first be
dropped. The syntax for this is the following:

ALTER TABLE tblChild
DROP CONSTRAINT tblChild_tblMyTable_FK;

.. . . where tblChild is the name of the related table, and
tblChild_tblMyTable_FK is the name of the foreign key.

Then one must drop the primary key constraint. The syntax for this is the
following:

ALTER TABLE tblMyTable
DROP CONSTRAINT PrimaryKey;

.. . . where tblMyTable is the name of the table, and PrimaryKey is the name
of the primary key.

If the table is empty, then the following query can be used to add the new
column and the new primary key with all four columns:

ALTER TABLE tblMyTable
ADD COLUMN DateEntered Date NOT NULL,
CONSTRAINT PrimaryKey PRIMARY KEY (LastName, FirstName, MI, DateEntered);

.. . . where tblMyTable is the name of the table, LastName, FirstName, MI are
the three columns that made up the original primary key, DateEntered is the
new column, and PrimaryKey is the name of the primary key.

If the table is not empty, then three steps are needed to accomplish the
above query. First, add the new column:

ALTER TABLE tblMyTable
ADD COLUMN DateEntered Date;

Then enter dates for all rows, then add the primary key constraint:

ALTER TABLE tblMyTable
ADD CONSTRAINT PrimaryKey PRIMARY KEY (LastName, FirstName, MI,
DateEntered);

If the foreign key was dropped earlier, then it needs to be added again.
The syntax is the following:

ALTER TABLE tblChild
ADD CONSTRAINT tblChild_tblMyTable_FK
FOREIGN KEY (LastName, FirstName, MI, DateEntered)
REFERENCES tblMyTable (LastName, FirstName, MI, DateEntered);

This assumes that the foreign key also needs the fourth column. If not,
then ignore the last column (DateEntered) and the comma that precedes it in
both the FOREIGN KEY clause and the REFERENCES clause.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Guest]

69,
Wow, I had a bad feeling I'd have to go through several steps.
Anyway thanks so much for the info. I probably won't get around to
accomplishing this for 2-3 weeks (or so). There seems to be a dirth of
information available on how to accomplish this kind of task (at least in my
Access books). And even less DDL info. DDL seems much easier than using ADO
or DAO to accomplish this kind of task, or adding tables, adding fields, etc.
I just have a hard time finding the correct syntax to use. Do you know of any
good books (or websites)?

thanks so much for the help,
James Deckert

 

['69 Camaro]

Hi, James.

There seems to be a dirth of
information available on how to accomplish this kind of task (at least in
my
Access books).

The dearth of information on how to do the advanced tasks in Access is due
to the fact that the majority of Access users are the point 'n click
wizard-user variety, although Access is certainly capable of a great deal
more for database application developers. However, book publishers will
make far more profit selling books to the former rather than to the latter,
so there's little motivation to change the status quo.

DDL seems much easier than using ADO
or DAO to accomplish this kind of task, or adding tables, adding fields,
etc.

DDL is designed to be simple and fast for database engines to execute.
However, writing SQL isn't as fast and easy as "point 'n click and type a
name," which is what the majority of Access developers are doing, so they
have no desire to learn SQL.

I just have a hard time finding the correct syntax to use.

Access comes with the Jet SQL 4.0 online help reference. I put a shortcut
to it on my desktop for quick reference. The default path for Access 2003
is the following:

"C:\Program Files\Common Files\Microsoft Shared\OFFICE11\1033\JETSQL40.CHM"

Do you know of any
good books

The books I learned SQL from were fantastic, and fantasticly expensive as
well, because they were geared towards Oracle professionals. They were $150
each retail when bookstores carried ordinary SQL books in the $30 to $50
range, and I imagine they go for more than $200 each now. Therefore, I'll
let others recommend ordinary SQL books at ordinary prices that helped them
learn SQL.

(or websites)?

Microsoft has an online reference for Jet 4.0 SQL on the following Web
pages:

Fundamental Microsoft Jet SQL for Access 2000:

http://msdn2.microsoft.com/en-us/library/aa140011(office.10).aspx

Intermediate Microsoft Jet SQL for Access 2000:

http://msdn2.microsoft.com/en-us/library/aa140015(office.10).aspx

Advanced Microsoft Jet SQL for Access 2000:

http://msdn2.microsoft.com/en-us/library/aa139977(office.10).aspx

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Guest]

Gunny,
I got around to implementing this change. It worked great. Thanks.

The DDL help "Jet SQL 4.0 online help reference" was also appreciated. I
wasn't aware that the help was so close. I now have a shortcut on my desktop.

thanks again,
James

 

['69 Camaro]

Hi, James.

You're very welcome. Glad I could help you out.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.