2 primary keys?? 1

[dug2k]

Hi,
I have a table with the following fields:

ResourceID
ResourceName
DateTime
InstructorID
LocationID
Misc

I have 2 different combinations that need to be unique: DateTime & InstructorID, and DateTime & LocationID.

I was going to make a combo key of DateTime, InstructorID, and LocationID but this will not satisfy my 2 above conditions for every scenario. What is the easiest way to accomplish this? Thanks.

 

[BNPMike]

You can put an index on each of the two combinations separately. If you declare these as unique they will both separately maintain the two uniqueness constraints.

A relation only has to have some set of fields that act together as a key and that's only for purists. Access uses tables rather than relations so you don't actually need a key at all. Generally it's a good idea to have one, but don't fret over the concept of 'primary'.

 

[fneily]

so you don't actually need a key at all"

It's too bad that Ted Codd died in 2003. He would have been interested in that statement concerning a relational database management system. Sharon, his wife and mother of OLAF is still around. Maybe you could ask her.

As Ted once said to help remember the normal forms:
"A relation is in second normal form if the relation depends on the key, the whole key, and nothing but the key, so help me Codd".

 

[Darrylles]

Hmm,

And Codd's 'law' ALWAYS has to be obeyed regardless of business requirement?
No, I don't think that Codd expected that (and even if he did - tough - it ain't happening in the real business world).

That's the trouble, business has to balance 'future-proof' with 'get-the-contract-now'.
The real problem is business-managers with only 'get-it-now' 'business' skills - with no experience or knowledge of the future savings to be made from foresight, and following 'proper' methodology.
They after all don't give a damn - they expect to leave their mess behind them - no professionalism whatsoever.

Anyway, dug, you haven't given enough info regarding why these field combinations are required.

(It also feels very 'homeworky' to me).

Regards,

Darrylle











Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[dug2k]

Thanks all for your input. BNPMike's suggestion for declaring two unique indexes worked for my purposes. I do also have a key (ResourceID). I am not sure if this all follows Codd's law, but as Darrylles says, my manager's main concern is getting it done now. Luckily it's a small app which we only need for a few months. The field combinations are needed so that we can't book two instructors at the same time, or book a location for more than one class at a time. Thanks again for your help.

 

[Golom]

The "to primary key or not to primary key" issue isn't really about obeying Ted Codd's "law", nor is it about worshiping the higher god of "get-the-contract-now".

It is about the fact that relational databases are assumed to represent entities (i.e. things) in the real world. If you have no primary key (or at least candidate key) then, in essence, you are saying that you can't (or don't care to) distinguish one instance of that entity from another or that multiple copies of the same instance have some value that a single instance does not. Conversely, you may also be saying that it doesn't matter if some instances are missing since they are indistinguishable anyway.

The implications of such a statement are that, for processing purposes, any old instance of the entity will do. I can't think of any real-world situation in which "just get me any old record from the table" is a viable business practice.

 

[fneily]

Golom, you're discussion is true if the subject was just about records in a single table. RDBMS involves the interaction of multiple tables. Because of the rationale you stated for a single table, its' relationship with other tables can lead to absurd analysis.
People keep throwing this "business requirement" at me. I have dealt with non-technical, non-programming, and Access illiterate management at AT&T for 10 years, in the government for 4 years and as a contractor. The choice a person has is to program correctly, usually out of self-pride, or listen to people who know nothing. I always did it my way and gave them credit. It made for a less stressful working environment.

 

[BNPMike]

Hi fneily

Just to clarify - as you're an instructor - relations must have a key but only in the sense that one row cannot be a duplicate of another. What you need to appreciate is that Access/Jet like most (all?) ' relational' databases actually work with tables not relations. Similar but not quite the same. Codd of course wanted a single field to be the key. It was Chris Date who generalised the idea to multi-field keys.

My point is always think in terms of what SQL question you want to ask of the data to determine what kinds of keys you need.

Also remember that you don't need to tell Jet/Access what your key is. It's a personal thing between you and SQL. If you do tell Access it will start doing other things like putting indexes on things. Logical but not essential.