MS project database table's primary keys

[Faisal Shehab]

Hi everybody,

I have MS Project 2003 Pro, I already saved it to MS Access database, which
I can see. The only problem was, no relationships are drawn, and I cannot
find primary keys for any of the tables. In the “projdb.htm†file, there is
information about the foreign keys, but nothing about primary keys of the
tables.

Does anybody know where I could find the information about the primary keys
of the MS project database tables?

Thanks in advance.

FS

 

[Brian K - Project MVP]

Hi everybody,

I have MS Project 2003 Pro, I already saved it to MS Access database, which
I can see. The only problem was, no relationships are drawn, and I cannot
find primary keys for any of the tables. In the “projdb.htm†file,
there is
information about the foreign keys, but nothing about primary keys of the
tables.

Does anybody know where I could find the information about the primary keys
of the MS project database tables?

Thanks in advance.

FS

The combination of Proj_ID and either Task_ID or Resource_ID are the keys
for most of the tables. There are also some where there is no Proj_ID but
maybe something like Field_ID instead.

Basically, if there are more than one ID field in a table it is a good bet
that it is the combo of the two of them that is the primary key for that
table. The Task Information table for example: The primary key is the
combination of Proj_ID and Task_ID

 

[Faisal Shehab]

There are clear primary keys such as TASK_UID for the TASKS table. But there
are others that they don not have UID, but multiple ID’s. I know I could
guess the composite primary keys. But what I am looking for is document,
which tells me the definite primary keys. I am currently drawing the MS
project ER diagram in which I want to map the primary and foreign keys for
it. I am currently using the MS Access, and know how to manually create them.
All what I need to know is where I could find this information to start
mapping the keys in the tables.

Any ideas?

FS

 

[Brian K - Project MVP]

There are clear primary keys such as TASK_UID for the TASKS table. But
there
are others that they don not have UID, but multiple ID’s. I know I could
guess the composite primary keys. But what I am looking for is document,
which tells me the definite primary keys. I am currently drawing the MS
project ER diagram in which I want to map the primary and foreign keys for
it. I am currently using the MS Access, and know how to manually create
them.
All what I need to know is where I could find this information to start
mapping the keys in the tables.

Any ideas?

FS

The doc you mentioned is the only one around. Task_UID is not the primary
key for that table. Get a couple of projects in your database and you will
see that the Task_UID field is not unique to the entire table. It is
unique within a Proj_ID.

 

[Faisal Shehab]

I don't know man, I am reading the document "projdb.htm" and it says that the
TASK_UID is a unique ID for the task. If what you are saying is true then
almost every table have a composite key, which include the PROJ_ID. It does
not make sense in the Database Design perspective.

So what you are saying that the "projdb.htm" is the only distributed
document that contains this information?

FS

 

[Brian K - Project MVP]

I don't know man, I am reading the document "projdb.htm" and it says that
the
TASK_UID is a unique ID for the task. If what you are saying is true then
almost every table have a composite key, which include the PROJ_ID. It does
not make sense in the Database Design perspective.

So what you are saying that the "projdb.htm" is the only distributed
document that contains this information?

It is a unique ID for the task, within a specific project. It is not
referring to unique from a table perspective. Im not saying it is the best
design but trust me when I say that if you have 5 projects in the database
each with 5 tasks you are going to see Task_UID values repeated in the
table.

Oh and when I said Task_Information table I meant MSP_Tasks. Damn
upgrades!

 

[Brian K - Project MVP]

I don't know man, I am reading the document "projdb.htm" and it says that
the
TASK_UID is a unique ID for the task. If what you are saying is true then
almost every table have a composite key, which include the PROJ_ID. It does
not make sense in the Database Design perspective.

Yes almost every table uses the Proj_ID as part of a composite. Not all
but MANY.

So what you are saying that the "projdb.htm" is the only distributed
document that contains this information?

Yes.

 

[Ed Morrison]

Try the Project 2003 Database Schema for more information
(http://www.microsoft.com/downloads/...c2-3ac1-4fc8-8e67-d908cdb9faf2&DisplayLang=en).
Although this document is for Project Server 2003, I believe the saving to
Access is a subset of the server schema.

 

[Faisal Shehab]

Thanks for the info. I will go back and dig deeper to try to figure out the
primary keys for the tables. I will be back if I any further questions.

FS

 

[JackD]

If you are intending to create relationships and enforce referential
integrity within access based on these keys you are going to run into
trouble.
Referential integrity within project is handled by the business logic of the
program, not by relationships within the database. If you add constraints to
the database you will find that Project will eventually violate them.