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.