Academic versus Real Word
December 16th, 2005In college I was told of various absolutes. First, a computer can only run a single OS at a time. And another absolute, a database table can only have one primary key per table. After being in the "Real World" for a while I know these rules are not necessarily absolutes.
We can now use products like VMWare and Virtual PC to run multiple OS installs on the same PC. I am sure my college professor meant there could only be one OS on the hardware, but the fact remains that I can run multiple instances at a time.
For duplicate primary keys, I was once told that Oracle added this "feature" due to customer requests. I fail to understand it. This is the sort of thing which will induce a seizure. I am not finding a reference to it via Google, so hopefully it is just a sick myth.
What I think is the only absolute in the world of IT is that everything changes. Just consider what has happened this past year. Apple is going to be running OS X on Intel! Before you know it Microsoft will be releasing the source for large portions of Windows Vista. The past few years have been very interesting. I expect the "stranger than fiction" trend to continue for quite a while.
December 29th, 2005 at 9:12 pm
I don't know about multiple primary keys, but you can have compound primary keys . . .
A table has a single primary key that is made up of one or more columns in that table. Think of it as a set. Many tables will have a synthetic primary key such as an id (int) or a GUID. Other tables can use intrinsic data such as a Social Security Number. These are all example of a single-column primary key.
Compound primary keys come into play when you have multiple columns that you want to use to uniquely identify a row. This is often done in mapping tables for example.
Contrived example:
Each employee has a single time-entry for a given calendar date.
The table might look like
create table employee_time {
employeeId int,
day date,
hours_worked smallint,
primary key (employeeId, day)
}
Then the primary key would consist of the employeId AND the day. This allows you to enforce those data rules in the database (although this specific instance might be better expressed as a business rule in code - but it's a made up example).
Is that what you mean?