Re: Primary Keys
- From: "Tom Ellison" <tellison@xxxxxxxxxxx>
- Date: Mon, 30 Jan 2006 17:22:44 -0600
Dear Potter:
You are correct that the table on the "many" side of a one-to-many
relationship does not require that all the columns in its primary key (or
other unique key) be used in the relationship. Indeed, it is not always the
case that the column(s) in the "many" table are indexed at all.
However, all the columns of the table on the "one" side of the relationship
must together be unique (when taken together), so a unique index (including
a primary key) is required. It is required not only for uniqueness, but
because it gives good performance to have this index. It is used whenever
you JOIN to it using this relationship.
"Why not leave all the data in one table?" Well, where shall I begin:
- The information in the set of columns forming what is properly the table
on the "one" side of the relationship will be repeated many times. If you
should ever need to change anything, such as just correcting a spelling, you
would have to enter that correction many times. Perhaps you expect to just
make changes in the spreadsheet and then import it, in which case this is
likely what you mean to do anyway. But it doesn't have to be that way.
- It is not uncommon to have a hierarchy of tables in one-to-many
relationship. Consider the organization of a country. It has many states.
Each state has many counties. Each county has many townships. Each
township consisits of many properties. Each property may have many
residents. Each resident has several bank accounts. Each bank account has
many deposits and withdrawals. Putting all that into a spreadsheet would
result in repeating state, county, township, property, resident, and bank
account just to show a list of deposits and withdrawals. It is proper to
have a key that does this, but do you need to repeat all the other
information about the state (state capitol city name, governor's name, etc.)
many millions of times in order to show every deposit or withdrawal made by
every resident? I don't think so.
Databases are organized, very highly organized, and according to a science
(or is it a mathematics?) This has been developed and tested over much of
my lifetime, and there are reasons for why it is done the way it is done.
It would take a very good chapter in a book to even get started to explain
this. You can buy the book and read it, or you can take my word for it, or
you can suffer the consequences of doing it poorly. If you stay with it,
you'll eventually see what I have been trying to explain. I can only
recommend strongly that you learn this by research before you learn it the
hard way.
Tom Ellison
"potter" <potter@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1F3A9224-91C9-4D9E-B702-243F527CC8D4@xxxxxxxxxxxxxxxx
> Tom - You are correct. in assuming I have many things to learn.
>
> So if I assign the 2 combined columns as my primary key in tbl_market
> "one"
> I must have both of the columns as my foreign key in my "many"
> tbl_industry?
> I thought I could use one column as a foreign key my "many" table. Seams
> like a bunch of lines between tables. why not just leave all the data in
> one
> table? I know it will be slower if data repeats but at least all the data
> I
> need is in one row or record. I can always find it! that is the
> goal...to
> find and use the data... Trying to figure out these keys are killing me.
>
> BTW - In my case I am not worried about typos because I always import this
> 127 column text file and append it to my large table. No manual date
> entry
> need.
>
> "Tom Ellison" wrote:
>
>> Dear Potter:
>>
>> The table on the "many" side of a one-to-many relationship (your
>> tbl_Sector)
>> must have a pair of columns that match the 2 column unique key
>> (Market_Sector and Industry_Sector) in the table on the "one" side of the
>> relationship (tbl_Market). Those columns must match the columns in
>> tbl_Sector that are the unique key you described (Market_Sector and
>> Industry_Sector) or you must have some other unique key to tbl_Sector on
>> which this relationship exists.
>>
>> The unique key on the foreign table is essential in order for it to be on
>> the "one" side of a one-to-many relationship. The uniqueness on one or
>> more
>> columns is what makes it a "one"-to-many relationship. That is, there is
>> only one row in this table that matches any number (many) of rows in the
>> other table.
>>
>> Does this describe what you have? If you do not have another unique key
>> to
>> tbl_Sector, then you must have some pair of columns in tbl_Market that
>> match
>> them. Otherwise, you have not designed so as to have such a
>> reletionship.
>>
>> Relationships are not an accidental occurrance. You design them in when
>> you
>> design the tables. You should create such relationships as you design
>> the
>> tables. So, your question should not be a mystery to you at this time.
>> Is
>> it? Do you have such a relationship designed and defined? Are you just
>> having trouble writing the query, or is there a failure in design behind
>> this?
>>
>> Tom Ellison
>>
>>
>> "potter" <potter@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:040E2EAA-E11B-45FA-B64B-1B4C2D3662B1@xxxxxxxxxxxxxxxx
>> > It is a one-to-many relationship. The "one" side is tbl_Market sector
>> > withe
>> > the 2 columns (Market_Sector and Industry_Sector) as the unique key.
>> > The
>> > "many" side is tbl_Sector. It also has a Primary Key (Industry_Group)
>> > JOINing
>> > to another table.
>> >
>> > That help explain what I am trying to do? By the way - I bought Access
>> > 2003
>> > Inside out. It's helpfuls but I still don't get how to join tables if
>> > a
>> > primary key uses multipal columns.
>> >
>> > "Tom Ellison" wrote:
>> >
>> >> Dear Potter:
>> >>
>> >> There are fundamental facts about relationships. Every relationship
>> >> is
>> >> one-to-one, one-to-many, or many-to-many. The most common
>> >> relationship,
>> >> by
>> >> far, is the one-to-many relationship. It has a table on the "one"
>> >> side
>> >> (the
>> >> foreign key table) and a table on the "many" side, the dependent
>> >> table.
>> >>
>> >> Without knowing which kind of relationship you want it is difficult to
>> >> help
>> >> you. If you are developing a one-to-many relationship, then it will
>> >> be
>> >> necessary to know which table is foreigh and which is dependent.
>> >>
>> >> If you don't know what these terms mean, I would suggest you make a
>> >> study
>> >> of
>> >> them before proceeding. Either the concepts will be familiar to you,
>> >> but
>> >> the terminology new, or perhaps you don't have the fundamentals.
>> >>
>> >> In the most common sense, JOINing two tables is a way of representing
>> >> an
>> >> already defined relationship within a query. There are other JOINs,
>> >> especially non-equi JOINs, but this is quite advanced. Somehow I
>> >> doubt
>> >> you're in that territory yet.
>> >>
>> >> For your two tables, Market_Sector and Industry_Sector, please state
>> >> what
>> >> columns are the Primary Key of each, and how they are related.
>> >> Perhaps
>> >> then
>> >> I can understand and help effectively.
>> >>
>> >> Tom Ellison
>> >>
>> >>
>> >> "potter" <potter@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> >> news:14BC75D7-C2A1-4ABB-9B6F-17E4FBEF6ADB@xxxxxxxxxxxxxxxx
>> >> > thx Tom... I still don't get how I can join the 2 tables. the 2
>> >> > columns
>> >> > (Market_Sector and Industry_Sector) combined are the primary key or
>> >> > unique
>> >> > key. My goal is to join my tbl_Market with tbl_industry.
>> >> > Industry_Sector
>> >> > columns exist in both tables. How do Join these tables again?
>> >> >
>> >> > "Tom Ellison" wrote:
>> >> >
>> >> >> Dear Potter:
>> >> >>
>> >> >> Is this table the foreign key table in the relationship you're
>> >> >> creating,
>> >> >> or
>> >> >> the dependent table?
>> >> >>
>> >> >> If it is dependent on another table, then the keys to this table
>> >> >> are
>> >> >> irrelevant to the relationship. A dependent table doesn't even
>> >> >> require
>> >> >> there to be a primary key.
>> >> >>
>> >> >> If it is the foreign key table, then you must create the
>> >> >> relationship
>> >> >> on
>> >> >> all
>> >> >> the columns that form this key (or any other unique key you wish to
>> >> >> define.)
>> >> >> Relationships do not have to be on the Primary Key, but should be
>> >> >> on
>> >> >> some
>> >> >> Unique Key.
>> >> >>
>> >> >> Tom Ellison
>> >> >>
>> >> >>
>> >> >> "potter" <potter@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> >> >> news:3863D23C-9FD6-4DF3-8083-6365EEB71992@xxxxxxxxxxxxxxxx
>> >> >> >I have assigned 2 fields as the primary key. I can link one of
>> >> >> >the
>> >> >> >fields
>> >> >> >to
>> >> >> > another table but not the other. I keep getting the following
>> >> >> > error
>> >> >> > message... No unique index found for the referenced fiels of the
>> >> >> > primary
>> >> >> > table.
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>
.
- References:
- Re: Primary Keys
- From: Tom Ellison
- Re: Primary Keys
- From: Tom Ellison
- Re: Primary Keys
- From: potter
- Re: Primary Keys
- From: Tom Ellison
- Re: Primary Keys
- From: potter
- Re: Primary Keys
- Prev by Date: Re: If combo box pick is not in list in access
- Next by Date: Re: change to standard format
- Previous by thread: Re: Primary Keys
- Next by thread: Re: Primary Keys
- Index(es):
Relevant Pages
|
